The first script (NewProfile.SQL) will call ProfRsrc.SQL, if resource_limit is 'True', otherwise, it will just prompt the user for settings for passwords.
rem +------------------------------------------------------------------+
rem | Script ID: NewProfile.sql
rem |
rem | Purpose: Prompt a newbie creating a profile with what the
rem | parameters mean and what their choices are.
rem |
rem | Developer: Lynn Tobias
rem | Script Date: August 01, 2008
rem | Oracle Ver: 10g
rem |
rem | Input File(s): none
rem |
rem | Table(s) Used: DBA_Profiles, v$Parameters
rem |
rem | Called by: n/a
rem | Calls: Resources.SQL - created and called. It runs
rem | ProfRsrc.SQL if value = True and
rem | posts a message if false.
rem | Create_Profile.Sql - actual profile code generated
rem |
rem | Variables: xValue (from v$parameters) if 'False' no resources
rem | Profile_Name - Name typed in
rem | Password Parameters: Password_Verify_Function
rem | Failed_Login_Attempts, Password_Grace_Time,
rem | Password_Life_Time, Password_Lock_Time,
rem | Password_Reuse_Max, Password_Reuse_Time
rem |
rem | Revisions: Prog Date Version Comment/Change
rem | ---- -------- ------- ----------------------------+
rem |
rem +------------------------------------------------------------------+
Clear Screen
Prompt +-------------------------------------------------------------------------+
Prompt | C r e a t i n g a P r o f i l e |
Prompt +-------------------------------------------------------------------------+
Prompt | Notes: Anyone not assigned a profile is subject to the DEFAULT profile. |
Prompt | If a profile omits some resources, the DEFAULT profile is used. |
Prompt +-------------------------------------------------------------------------+
Prompt
rem *1**************************************************************************
rem Show the settings on the default profile
rem ****************************************************************************
Prompt The current settings on the Default profile are:
Break on Resource_Type Skip 1
Set NewPage 1 Heading On Feedback Off Pagesize 60
Column Limit Format A15 Heading Limit
Column Profile Format A7 Heading Profile
Column Resource_Name Format A30 Heading Resource|Name
Column Resource_Type Format A10 Heading Resource|Type
Select Profile, Resource_Type, Resource_Name, Limit
From Dba_Profiles
Where Profile = 'DEFAULT'
Order By Resource_Type, Resource_Name;
rem *2**************************************************************************
rem List profile names already taken and ask for a new one.
rem ****************************************************************************
Prompt
Column Profile Format A30 Heading 'Current Profiles:'
Select Distinct Profile
From Dba_Profiles
Order by Profile;
Prompt
Accept Profile_Name Prompt 'Please choose a new name: '
rem *2.1******************************************************************
rem Make sure the typed name is not on the list.
rem **********************************************************************
Set Heading Off Feedback Off Verify Off
Select Case When Ctr > 0
Then '*->> *** THIS PROFILE HAS ALREADY BEEN USED AND WILL BE DROPPED.****'
Else ' '
End
From (
Select Count(*) Ctr
From DBA_Profiles
Where Profile = Upper('&Profile_Name')
);
rem *3**************************************************************************
rem Write the Drop and the first line of the Create Profile.
rem ****************************************************************************
Set Term Off
Spool Create_Profile.Sql Replace
Select 'DROP PROFILE ' || '&Profile_Name' || ';'
From Dual;
Select 'CREATE PROFILE ' || '&Profile_Name' || ' LIMIT'
From Dual;
Spool Off
Set Term On
rem *4**************************************************************************
rem Check the resource limit parameter to see if they can be set
rem ( ALTER SYSTEM SET resource_limit=TRUE SCOPE=BOTH; )
rem ****************************************************************************
Column Value New_Value Xvalue
Set Term off
Spool Resources.SQL Replace
Select Case When Value = 'TRUE'
Then '@ProfRsrc'
Else 'Prompt *->> RESOURCE_LIMIT=FALSE. ALTER SYSTEM to use.'
End
From V$Parameter
Where Name = 'resource_limit';
Spool Off
Set Term On
Prompt
@Resources
Prompt
rem *5**************************************************************************
rem Get the password parameters
rem ****************************************************************************
Prompt
Prompt | P A S S W O R D P A R A M E T E R S
Prompt +-------------------------------------------------------------------------+
Prompt | Please either supply an answer to the following parameters |
Prompt | or press Enter to choose Default: |
Prompt +-------------------------------------------------------------------------+
Prompt |Note: These can take any one of these as an answer: |
Prompt | - integer |
Prompt | - expression (any form except scalar subquery expression) |
Prompt | - UNLIMITED |
Prompt | - DEFAULT (press Enter) |
Prompt | |
Prompt | Time: Day=1 Hours=n/24 Minute=n/1440 Seconds=n/86400 |
Prompt +-------------------------------------------------------------------------+
Prompt
Accept FAILED_LOGIN_ATTEMPTS Default Default Prompt -
'FAILED_LOGIN_ATTEMPTS (# of failed logins to the acct before the acct is locked): '
Prompt
Accept PASSWORD_GRACE_TIME Default Default Prompt -
'PASSWORD_GRACE_TIME (# of days between warning and login expiration) : '
Prompt
Accept PASSWORD_LIFE_TIME Default Default Prompt -
'PASSWORD_LIFE_TIME (# of days a password can be used) : '
Prompt
Accept PASSWORD_LOCK_TIME Default Default Prompt-
'PASSWORD_LOCK_TIME (# of days acct locked after the # of repeated failed logins): '
Prompt
Prompt +-------------------------------------------------------------------------+
Prompt | For PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX to work, |
Prompt | they must both be specified. |
Prompt +-------------------------------------------------------------------------+
Prompt | If PASSWORD_REUSE_TIME = 10 and PASSWORD_REUSE_MAX = 2, |
Prompt | then Password can be reused after 10 days if changed 2 times. |
Prompt | |
Prompt | If either is a number and the other UNLIMITED, |
Prompt | then the password can never be reused. |
Prompt +-------------------------------------------------------------------------+
Prompt
Accept PASSWORD_REUSE_MAX Default Default Prompt -
'PASSWORD_REUSE_MAX (# of changes required before the current one can be reused): '
Prompt
Accept PASSWORD_REUSE_TIME Default Default Prompt -
'PASSWORD_REUSE_TIME (# of days before a password can be reused) : '
Prompt
Prompt +-------------------------------------------------------------------------+
Prompt | PASSWORD_VERIFY_FUNCTION |
Prompt +-------------------------------------------------------------------------+
Prompt | This allows a PL/SQL password verification script to be used. |
Prompt | Oracle provides a default script. Use this or 3rd-party code, or make |
Prompt | your own. 'NULL' is no verification done. |
Prompt | |
Prompt | Answer: Function | NULL | DEFAULT |
Prompt +-------------------------------------------------------------------------+
Prompt
Accept PASSWORD_VERIFY_FUNCTION Default Default Prompt 'PASSWORD_VERIFY_FUNCTION: '
Prompt
rem *6**************************************************************************
rem Create the profile statement
rem ****************************************************************************
Set Heading Off NewPage None Linesize 100 Term Off
Spool Create_Profile.Sql Append
Select 'FAILED_LOGIN_ATTEMPTS ' || '&FAILED_LOGIN_ATTEMPTS' From Dual;
Select 'PASSWORD_GRACE_TIME ' || '&PASSWORD_GRACE_TIME' From Dual;
Select 'PASSWORD_LIFE_TIME ' || '&PASSWORD_LIFE_TIME' From Dual;
Select 'PASSWORD_LOCK_TIME ' || '&PASSWORD_LOCK_TIME' From Dual;
Select 'PASSWORD_REUSE_MAX ' || '&PASSWORD_REUSE_MAX' From Dual;
Select 'PASSWORD_REUSE_TIME ' || '&PASSWORD_REUSE_TIME' From Dual;
Select 'PASSWORD_VERIFY_FUNCTION ' || '&PASSWORD_VERIFY_FUNCTION' From Dual;
Select ';' From Dual;
Spool Off
rem *7**************************************************************************
rem Execute the statement created
rem ****************************************************************************
Set Echo On Feedback On Term On
@Create_Profile
Set Echo Off Heading On Feedback Off NewPage 1
rem *8**************************************************************************
rem Show them the new profile from DBA_Profiles
rem ****************************************************************************
Column Profile Format A7 Heading Profile
Select Profile, Resource_Type, Resource_Name, Limit
From Dba_Profiles
Where Profile = Upper('&Profile_Name')
Order By Resource_Type, Resource_Name;
rem +------------------------------------------------------------------+
rem | Script ID: ProfRsrc.sql
rem |
rem | Purpose: Prompt a newbie creating a profile with what the
rem | parameters mean for resources and what their choices are.
rem |
rem | Developer: Lynn Tobias
rem | Script Date: August 01, 2008
rem | Oracle Ver: 10g
rem |
rem | Input File(s): none
rem |
rem | Table(s) Used: Dual
rem |
rem | Called by: Resources.SQL (generated by NewProfile.Sql)
rem | Calls: n/a
rem |
rem | Variables: Resource Parameters: SESSIONS_PER_USER, CPU_PER_SESSION,
rem | CPU_PER_CALL, CONNECT_TIME, IDLE_TIME,
rem | LOGICAL_READS_PER_SESSION, LOGICAL_READS_PER_CALL,
rem | COMPOSITE_LIMIT, PRIVATE_SGA
rem |
rem | Output: Create_Profile.Sql
rem |
rem | Revisions: Prog Date Version Comment/Change
rem | ---- -------- ------- ----------------------------+
rem |
rem +------------------------------------------------------------------+
Prompt
Prompt | R E S O U R C E P A R A M E T E R S
Prompt +-------------------------------------------------------------------------+
Prompt | Please either supply an answer to the following parameters |
Prompt | or press Enter to choose Default: |
Prompt +-------------------------------------------------------------------------+
Prompt | Note: These can take any one of these as an answer: |
Prompt | - integer |
Prompt | - UNLIMITED |
Prompt | - DEFAULT (press Enter) |
Prompt | |
Prompt | * Used in COMPOSITE_LIMIT |
Prompt | |
Prompt | If CONNECT_TIME or IDLE_TIME exceeded, the current transaction rolls |
Prompt | back and the session is ended. The next call returns an error. |
Prompt | |
Prompt | If other resources exceeded, the operation is aborted, the current |
Prompt | statement is rolled back, and an error posted. Commit or rollback the |
Prompt | current transaction, and then the session ends. |
Prompt | |
Prompt | If single call limit exceeded, the operation is aborted, current state- |
Prompt | ment rolled back, an error returned. Current transaction is left intact |
Prompt +-------------------------------------------------------------------------+
Prompt
Accept SESSIONS_PER_USER Default Default Prompt -
'SESSIONS_PER_USER (# of concurrent sessions) : '
Prompt
Accept CPU_PER_SESSION Default Default Prompt -
'CPU_PER_SESSION* (CPU time limit in 100th of seconds) : '
Prompt
Accept CPU_PER_CALL Default Default Prompt -
'CPU_PER_CALL (CPU time limit for a parse, execute, or fetch in 100th of seconds) : '
Prompt
Accept CONNECT_TIME Default Default Prompt -
'CONNECT_TIME* (total session elapsed time limit in minutes) : '
Prompt
Accept IDLE_TIME Default Default Prompt -
'IDLE_TIME (the permitted continuous inactive time during a session in minutes) : '
/* Does include long-running queries and other operations */
Prompt
Accept LOGICAL_READS_PER_SESSION Default Default Prompt -
'LOGICAL_READS_PER_SESSION* (# of data blocks read from memory and disk/session) : '
Prompt
Accept LOGICAL_READS_PER_CALL Default Default Prompt -
'LOGICAL_READS_PER_CALL (# of data blocks read to process a parse/execute/fetch) : '
Prompt
Accept COMPOSITE_LIMIT Default Default Prompt -
'COMPOSITE_LIMIT (Cpu_Per_Session+Connect_Time+Logical_Reads_Per_Session+Private_SGA: '
Prompt
Prompt +-------------------------------------------------------------------------+
Prompt | Note: PRIVATE_SGA can take any one of these as an answer: |
Prompt | - integer K|M|G|T|P|E |
Prompt | - UNLIMITED |
Prompt | - DEFAULT (press Enter) |
Prompt | (This only applies for shared server architecture. |
Prompt +-------------------------------------------------------------------------+
Accept PRIVATE_SGA Default Default Prompt -
'PRIVATE_SGA* (space a session can allocate in the shared pool of the SGA : '
Set Heading Off NewPage None Linesize 100 Term Off
Spool Create_Profile.Sql Append
Select 'SESSIONS_PER_USER ' || '&SESSIONS_PER_USER' From Dual;
Select 'CPU_PER_SESSION ' || '&CPU_PER_SESSION' From Dual;
Select 'CPU_PER_CALL ' || '&CPU_PER_CALL' From Dual;
Select 'CONNECT_TIME ' || '&CONNECT_TIME' From Dual;
Select 'IDLE_TIME ' || '&IDLE_TIME' From Dual;
Select 'LOGICAL_READS_PER_SESSION ' || '&LOGICAL_READS_PER_SESSION' From Dual;
Select 'LOGICAL_READS_PER_CALL ' || '&LOGICAL_READS_PER_CALL' From Dual;
Select 'COMPOSITE_LIMIT ' || '&COMPOSITE_LIMIT' From Dual;
Select 'PRIVATE_SGA ' || '&PRIVATE_SGA' From Dual;
Spool Off
Set Term On
Posts
No comments:
Post a Comment