ORACLE

DDL of USER: SELECT dbms_metadata.get_ddl('USER','NARI') FROM dual; DDL of USER related garnts: SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','NARI') from dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','NARI') from dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','NARI') from dual; or  ==== clear screen accept uname prompt 'Enter User Name : ' accept outfile prompt  ' Output filename : ' spool &&outfile..gen SET LONG 2000000 PAGESIZE 0 head off verify off feedback off linesize 132 SELECT dbms_metadata.get_ddl('USER','&&uname') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','&&uname') from dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','&&uname') from dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','&&uname') from dual; spool off === DDL of Role: S

alter profile user limit password_verify_function verify_fun; Here we altered profile password_verify_function to limit verify_fun, but it has not reflected in profile limit. We got below output. Select * from dba_profiles where resource_name=’PASSWORD_VERIFY_FUNCTION’; PROFILE              RESOURCE_NAME             RESOURCE        LIMIT           COM  --------             -------------------------  -------------- ---------------- ----  DEFAULT              PASSWORD_VERIFY_FUNCTION   PASSWORD        NULL            NO  USERS                PASSWORD_VERIFY_FUNCTION   PASSWORD         FROM ROOT       YES  select * from profname$;  PROFILE#   NAME                     FLAGS  ---------- ------------------------- ----------  0         DEFAULT                   0  1         USERS                      1  Update flag values to 0. update profname$ set flags=0;   commit; select * from profname$;  PROFILE#   NAME                      FLAGS  ---------- ------------------------ ----------  0      

If you are creating 2nd listener It will not allow you to use 1521 port, Then you can use any port in the range 1024 to 65536. Here I am using 1526 port for 2nd listener. If you are not using default LISTENER, You may face below issues. =========== C:\Nari>sqlplus sys/aaaa@NARI as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Mon Jul 10 23:38:33 2017 Copyright (c) 1982, 2013, Oracle.  All rights reserved. ERROR: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor ============ or ============ C:\Nari>sqlplus sys/aaaa@nari as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Mon Jul 10 23:38:33 2017 Copyright (c) 1982, 2013, Oracle.  All rights reserved. ERROR: ORA-28547: connection to server failed, probable Oracle Net admin error Enter user-name: ============= ->You can see no services in listener status (Or you can see Instance "NARI", status UNKNOWN, has 2 handler(s) for this service....) lsnrctl status LISTENER_NARI The l

Connect to OEM database with sysman user and run below query to find drive free space of all servers: set lines 375 pages 375 col target_name for a27 col column_label for a45 col target_type for a5 col value for a35 col key_value for a35 select TARGET_GUID,TARGET_NAME,TARGET_TYPE,'Space Free(MB)' COLUMN_LABEL,COLLECTION_TIMESTAMP,VALUE,KEY_VALUE  from MGMT$METRIC_CURRENT where  metric_name in ('Filesystems') and METRIC_COLUMN in ('available') and key_value like 'C:%' order by target_name;

============ set colsep , set ECHO OFF set TERMOUT ON set TAB OFF set TRIMOUT ON set TRIMSPOOL ON set PAGESIZE 50000 set LINESIZE 500 set FEEDBACK OFF set VERIFY OFF CLEAR COLUMNS == ==== set markup html on spool on  SPOOL UNDO_INFO.HTML  set pagesize 200  set echo on;  - spool off  set markup html off spool off  === spool undo_details.html  set markup html on  - set markup html off  spool off ===== ============== set lines 375 pages 999 select name,open_mode,database_role,to_char(startup_time, 'HH24:MI DD-MON-YY') "Startup time"  from v$database,v$instance; select count(*) from dba_objects where status='INVALID'; select sum(bytes/1024/1024/1024) from dba_data_files; col owner for a25 col object_name for a30 col object_type for a23 select owner,object_name,object_type from dba_objects where status='INVALID'; col action_time for a45 col action for a23 col comments for a40 col namespace for a10 col version for a12 select * from dba_registry_history; sele

Recipe #1 - find blocking sessions with v$session select s.username, s.blocking_session, s.sid, s.serial#, s.seconds_in_wait from  v$session s where  blocking_session IS NOT NULL; ============================== Recipe #3 - blocking sessions with all available information SELECT s1.username || '@' || s1.machine     || ' ( SID=' || s1.sid || ' )  is blocking '     || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status     FROM v$lock l1, v$session s1, v$lock l2, v$session s2     WHERE s1.sid=l1.sid AND s2.sid=l2.sid     AND l1.BLOCK=1 AND l2.request > 0     AND l1.id1 = l2.id1     AND l1.id2 = l2.id2; ============================== Recipe #2 - find blocking sessions using v$lock SELECT     l1.sid || ' is blocking ' || l2.sid blocking_sessions FROM     v$lock l1, v$lock l2 WHERE    l1.block = 1 AND    l2.request > 0 AND    l1.id1 = l2.id1 AND    l1.id2 = l2.id2 =============================== Rec