### Check the current Edition
select ad_zd.get_edition_type from dual;
### Changing the current Edition
exec ad_zd.set_edition('PATCH');
### Edition Details
select
aed.edition_name "Edition Name"
, ad_zd.get_edition_type(aed.edition_name) "Type"
, decode(use.privilege, 'USE', 'ACTIVE', 'RETIRED') "Status"
, decode(aed.edition_name, sys_context('userenv', 'current_edition_name'),
'CURRENT', '') "Current?"
from
all_editions aed
, database_properties prop
, dba_tab_privs use
where prop.property_name = 'DEFAULT_EDITION'
and use.privilege(+) = 'USE'
and use.owner(+) = 'SYS'
and use.grantee(+) = 'PUBLIC'
and use.table_name(+) = aed.edition_name
order by 1
/
### Display Edition level used objects & sessions
select
obj.object_name EDITION_NAME
, ses.STATUS STATUS
, ses.USERNAME USERNAME
, substr(ses.CLIENT_IDENTIFIER, 1, 10) CLIENT_ID
, ses.OSUSER OSUSER
, ses.SID||','||ses.serial# "SID,SERIAL#"
, ses.MACHINE
, substr(ses.PROGRAM, 1, 10) PROGRAM
, ses.TYPE
, substr(ses.MODULE, 1, 30) MODULE
, ses.ACTION
, ses.CLIENT_INFO
, ses.LOGON_TIME
, ses.SERVICE_NAME
from
v$session ses
, dba_objects obj
where obj.object_id = ses.SESSION_EDITION_ID
order by EDITION_NAME, STATUS, SQL_EXEC_START
/
### Dependencies of the Object
select
dep.OWNER OWNER
, dep.NAME OBJECT_NAME
, dep.TYPE OBJECT_TYPE
, dep.REFERENCED_OWNER
, dep.REFERENCED_NAME
, dep.REFERENCED_TYPE
from DBA_DEPENDENCIES dep
where dep.NAME = upper('&OBJECT_NAME')
and dep.referenced_type <> 'NON-EXISTENT'
and (dep.referenced_owner, dep.referenced_name) not in
(('SYS', 'STANDARD'))
order by 1, 2, 3, 4, 5
/
### Check the Edition view of the Object
select
evc.view_column_name view_column
, decode(evc.view_column_name, evc.table_column_name, '=', '===>') "->"
, evc.table_column_name table_column
from
user_synonyms syn
, dba_editioning_views ev
, dba_editioning_view_cols evc
where syn.synonym_name = upper('&OBJECT_NAME')
and ev.owner = syn.table_owner
and ev.view_name = syn.table_name
and evc.owner = ev.owner
and evc.view_name = ev.view_name
order by evc.owner, evc.view_name, evc.view_column_id
/
### To List all editioning views of custom table
select * from dba_editioning_views where table_name like 'XX%';
### To Create editioning views
exec ad_zd_table.upgrade('OWNER', 'TABLE_NAME')
Note: Above command creates an APPS synonym as well
### RMAN
select ctime "Date", decode(backup_type, 'L', 'Archive Log', 'D', 'Full', 'Incremental') backup_type, bsize "Size MB"
from (select trunc(bp.completion_time) ctime, backup_type, round(sum(bp.bytes/1024/1024),2) bsize
from v$backup_set bs, v$backup_piece bp
where bs.set_stamp = bp.set_stamp and bs.set_count = bp.set_count and bp.status = 'A'
group by trunc(bp.completion_time), backup_type) order by 1, 2;
select TAG, STATUS, START_TIME, round(ELAPSED_SECONDS,2)in_Min, DELETED, BYTES/1024/1024 Size_MB from v$backup_piece;
### SQL Tuning Advisor using SQL ID
DECLARE
l_sql_tune_task_id VARCHAR2(1000);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => '9zy3b5xvj1y2t',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 120,
task_name => '9zy3b5xvj1y2t',
description => '9zy3b5xvj1y2t');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
begin
DBMS_SQLTUNE.execute_tuning_task(task_name => '9zy3b5xvj1y2t');
end;
/
SELECT DBMS_SQLTUNE.report_tuning_task('9zy3b5xvj1y2t') AS recommendations FROM dual;
### Generate trace for running session
SQL> exec dbms_monitor.session_trace_enable(session_id=>684, serial_num=>36892, waits=>TRUE, binds=>TRUE);
SQL> exec dbms_monitor.session_trace_disable(session_id=>684, serial_num=>36892);
SQL> oradebug setorapid 121 (v$process.PID)[OR]
SQL> oradebug setospid 17179 (v$process.SPID)
Oracle pid: 62, Unix process pid: 17179, image: oracle@test.example.com
SQL> oradebug unlimit
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 12;
SQL> oradebug Event 10046 trace name context forever,level 8; -- Recommended for Performance
SQL> oradebug TRACEFILE_NAME; -- to show trace file name
SQL> oradebug event 10046 trace name context off
Statement processed.
tkprof DEV_ora_113386.trc DEV_ora_113386_tkp.txt explain=apps/apps sort=prsela,exeela,fchela
tkprof <file.trc> <file.prf> explain=apps/<password> sys=no sort='(prsela,exeela,fchela)'
