Maintenance Scripts - Oracle Database

### 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)'