Find which table references a sequence qith the following statement: SELECT s.object_id AS sequence_object_id,<br>s.name AS sequence_name,<br>OBJECT_SCHEMA_NAME(o.parent_object_id) + '.'<br>+ OBJECT_NAME(o.parent_object_id) AS table_name,<br>r.*<br>FROM sys.sequences s<br>CROSS APPLY sys.dm_sql_referencing_entities(<br>OBJECT_SCHEMA_NAME(s.object_id) + '.' + s.name,<br>'OBJECT'<br>) r<br>JOIN sys.objects o<br>ON o.object_id = r.referencing_id
DECLARE v_sqlid varchar2(20) :='<SQL ID>’;l_sql_tune_task_id VARCHAR2(100);BEGINl_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (sql_id => v_sqlid ,scope => DBMS_SQLTUNE.scope_comprehensive,time_limit => 120,task_name => v_sqlid,description => ‘Tuning task for statement ‘||v_sqlid);DBMS_OUTPUT.put_line(‘l_sql_tune_task_id: ‘ || l_sql_tune_task_id);END;/ EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => ‘<SQL ID>’); select dbms_sqltune.report_tuning_task(‘v_sqlid ‘<SQL ID>’) from dual;
select * from dba_tab_statistics where STALE_STATS=’YES’; SELECT instance_id,JOB_NAME,ACTUAL_START_DATE,RUN_DURATION,STATUSFROM DBA_SCHEDULER_JOB_RUN_DETAILSWHERE JOB_NAME like ‘%OPT%’order by ACTUAL_START_DATE desc;
A blocking lock is when a resource is locked from one session, and another session needs to acquire a new non compatible lock on the same resource. Find the locking resources with the following statement: SELECT a.sid||decode(request,0,'(holder)','(waiter)'||':blocked by:'||blocking_session) sess_id ,<br>lmode,<br>request,<br>a.type,<br>c.object_name,<br>decode(row_wait_obj#,-1,'Holder…
Find the log switches with following query: SELECT TRUNC (first_time) "Date", TO_CHAR (first_time, 'Dy') "Day",<br>COUNT (1) "Total",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '00', 1, 0)) "h0",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '01', 1, 0)) "h1",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '02', 1, 0))…
SELECT o.name as table_name, rv.value as partition_range,<br>fg.name as file_groupName, p.partition_number,max(p.rows) as number_of_rows,<br>sum(au.total_pages) *<em>8 [Total MB],sum(au.used_pages)</em>*8 [Used MB]<br>FROM sys.partitions p<br>INNER JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id<br>INNER JOIN sys.objects o ON p.object_id = o.object_id<br>INNER JOIN sys.system_internals_allocation_units au…
To create AWR reports based on the automated metrics gathered:Connect to the correct Pluggable: select * from v$pdbs; Find the dbid:select * from v$database;<br> If you want to create manually a AWR snapshot use the following: select sys.dbms_workload_repository.create_snapshot('ALL') from dual;…
with cols as (select s.name,st.name table_name,ac.name column_name,t.name column_type,ac.max_length,ac.precision,ac.scale,casewhen t.name in (‘datetime’,’datetime2′,’date’) then ‘DATE’end oracle_typefrom sys.all_columns ac join sys.types t on t.system_type_id=ac.system_type_idjoin sys.tables st on st.object_id=ac.object_idjoin sys.schemas s on s.schema_id=st.schema_idwhere st.name!=’sysdiagrams’),grp as(select upper(table_name) table_name, col= upper(STUFF ((SELECT ‘,’ + column_name+’ ‘+isnull(oracle_type,”)…
select s.name,st.name table_name,ac.name column_name,t.name column_type,ac.max_length,ac.precision,ac.scale,casewhen t.name=’sysname’ then ‘nvarchar2(128)’when t.name=’nvarchar’ then ‘nvarchar2(‘+cast(ac.max_length as varchar)+’)’when t.name=’varchar’ then ‘varchar2(‘+cast(ac.max_length as varchar)+’)’when t.name=’char’ then ‘char(‘+cast(ac.max_length as varchar)+’)’when t.name=’int’ then ‘number(10)’when t.name=’bigint’ then ‘number(19)’when t.name in (‘float’,’real’) then ‘number’when t.name=’smallint’ then ‘number(5)’when t.name=’tinyint’ then ‘number(3)’when…
A database in ARCHIVELOG mode copies redo log entries in the achive log files. Archive log number and size is a metric of a database transactions load in time. Use the following query to monitor the Archive log production per…