Search This Blog

Total Pageviews

Tuesday 30 January 2018

Oracle profile metadata ....

Oracle profile metadata ....




set long 10000 longchunksize 10000 pagesize 0 linesize 1000 feedback off verify off trimspool on
column profile_ddl format a500
execute dbms_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
select dbms_metadata.get_ddl('PROFILE', profile) as profile_ddl from (select distinct profile   from   dba_profiles)
where  profile like upper('%&1%');
Enter value for 1: default


 ALTER PROFILE "DEFAULT"
    LIMIT
         COMPOSITE_LIMIT UNLIMITED
         SESSIONS_PER_USER UNLIMITED
         CPU_PER_SESSION UNLIMITED
         CPU_PER_CALL UNLIMITED
         LOGICAL_READS_PER_SESSION UNLIMITED
         LOGICAL_READS_PER_CALL UNLIMITED
         IDLE_TIME UNLIMITED
         CONNECT_TIME UNLIMITED
         PRIVATE_SGA UNLIMITED
         FAILED_LOGIN_ATTEMPTS 10
         PASSWORD_LIFE_TIME 15552000/86400
         PASSWORD_REUSE_TIME UNLIMITED
         PASSWORD_REUSE_MAX UNLIMITED
         PASSWORD_VERIFY_FUNCTION NULL
         PASSWORD_LOCK_TIME 86400/86400
         PASSWORD_GRACE_TIME 604800/86400
         INACTIVE_ACCOUNT_TIME UNLIMITED ;


col profile format a10
col resource_name format a30
col resource format a8
col limit          format a15
select * from dba_profiles where profile = 'DEFAULT';


SELECT resource_name,limit FROM dba_profiles WHERE profile='DEFAULT' AND resource_name in ('PASSWORD_LIFE_TIME','FAILED_LOGIN_ATTEMPTS');
SELECT resource_name,limit FROM dba_profiles WHERE profile='DEFAULT' AND resource_name like 'PASSWORD%' ;

create profile test limit            /* default value, always present */
  composite_limit               unlimited                   /* service units */
  sessions_per_user             unlimited              /* logins per user id */
  cpu_per_session               unlimited            /* cpu usage in minutes */
  cpu_per_call                  unlimited        /* max cpu minutes per call */
  logical_reads_per_session     unlimited
  logical_reads_per_call        unlimited
  idle_time                     unlimited
  connect_time                  unlimited
  private_sga                   unlimited      /* valid only with TP-monitor */
  failed_login_attempts         unlimited
  password_life_time            unlimited
  password_reuse_time           unlimited
  password_reuse_max            unlimited
  password_verify_function      null
  password_lock_time            unlimited
  password_grace_time           unlimited
/

====




set linesize 300 pagesize 300
col LIMIT for a20
select resource_name,limit from dba_profiles where profile='DEFAULT';


alter session set nls_date_format='dd-mm-yyyy hh24:mi'  ;

col name for a20
SELECT
   name,
   ctime,
   ptime
FROM
   sys.user$
WHERE
    name = 'SYS';




Oracle DBA

anuj blog Archive