scrip to check temporary tablespace usage by each SQL_IDS in oracle database

select to_char(BEGIN_INTERVAL_TIME,'YYYY-MM-DD HH24:MI'),sql_id,sorts_delta
from dba_hist_sqlstat a, dba_hist_snapshot b
where a.snap_id=b.snap_id and to_char(BEGIN_INTERVAL_TIME,'YYYY-MM-DD HH24:MI')
between '2015-10-05 22:00' and '2015-10-05 23:30' order by sorts_delta desc;

scrip to find sql server last startup time

SELECT    [sqlserver_start_time] AS [LastStartupDate] FROM    [sys].[dm_os_sys_info]

oracle database live monitoring 11g

we may use the below view for Db live monitoring 11g onwards.


 select sql_text ,status,username from V$SQL_MONITOR;

script to check database resource utilization history in oracle database

Please use the below view to find the resource history usage details with specific time frame.

select CURRENT_UTILIZATION,MAX_UTILIZATION,LIMIT_VALUE from DBA_HIST_RESOURCE_LIMIT where RESOURCE_NAME like '%processes%' and SNAP_ID='13011';

scrip to check catalog db synced with rman repository?

select DB_NAME,DB_STATUS,RESYNC_TIME,RESYNC_TYPE from  rman.RC_RESYNC where DB_NAME='DB_NAME';

DB_NAME  DB_STAT RESYNC_TIME RESYNC_
-------- ------- --------- -------
DB_NAME      OPEN    30-OCT-14 FULL

steps to perform table-reorganization using DBMS_REDEFINITION on oracle table

1.DETERMINE IF THE TABLE CAN BE REDEFINED ONLINE

BEGIN
   DBMS_REDEFINITION.CAN_REDEF_TABLE('schema_name','original_TABLE', DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/

2.CREATE THE INTERIM TABLE

CREATE TABLE "schema_name"."original_TABLE_INTERIM" AS SELECT * FROM "schema_name"."original_TABLE" WHERE 1=2;

3.PARALLELISM FOR LARGE TABLES

ALTER SESSION FORCE PARALLEL DML PARALLEL 8;
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 8;

4.START THE REDEFINITION

BEGIN
   DBMS_REDEFINITION.START_REDEF_TABLE(
                 uname => 'schema_name',
                 orig_table => 'original_TABLE',
                 int_table => 'original_TABLE_INTERIM',
                 options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/

5.COPY THE TABLE DEPENDENTS FROM THE ORIGINAL TABLE TO THE INTERIM TABLE

DECLARE
   error_count pls_integer := 0;
BEGIN
   DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('schema_name', 'original_TABLE', 'original_TABLE_INTERIM', dbms_redefinition.cons_orig_params, TRUE,TRUE,TRUE,FALSE, error_count);
   DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
END;
/


6.check for invalid objects

select count(*),status from dba_objects where owner='schema_name' group by status;

select object_name from DBA_OBJECTS where owner='schema_name' and status='INVALID';

7.DO ONE FINAL SYNCHRONIZE BEFORE FINISHING THE REDEFINITION

BEGIN
   DBMS_REDEFINITION.SYNC_INTERIM_TABLE('schema_name', 'original_TABLE', 'original_TABLE_INTERIM');
END;
/

8.FINISH THE REDEFINITION

exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('schema_name', 'original_TABLE', 'original_TABLE_INTERIM');

9.Drop the interim table
drop TABLE "schema_name"."original_TABLE_INTERIM"