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"

how to get the index size without creating the index on table?

There are multiple ways we can calculate the Index size without creating it.
1.just check the table size and based on the number of columns used on the index we may put the approx index size.
2.just take the explain plan for the index create statement ,it will show you the index size.

script to grant read/write access to user from all Db users in oracle database?

Here is the script which I used to pull all the grants and push to ROLE.
then grant to role to user.

select 'grant select ,update,insert,delete on '||owner||'.'||view_name||' to RW_ROLE;' from dba_views where owner in (select username from dba_users where username not in ('SYSTEM','DBSNMP','SYS','OUTLN','OLAPSYS','CTXSYS','EM_MONITOR','SYSMAN') );

select 'grant select ,update,insert,delete on '||owner||'.'||table_name||' to RW_ROLE;' from dba_tables where owner in (select username from dba_users where username not in ('SYSTEM','DBSNMP','SYS','OUTLN','OLAPSYS','CTXSYS','EM_MONITOR','SYSMAN') );

steps to generate fake error in oracle database alert log

11g on-wards

alter session set events '942 incident(FAKE_ERROR_GENERATED)';
drop table KILL_NOTEXIST;
alter session set events '942 trace name context off';.

Steps to import same table as different table name in oracle database?

here is the steps:
1.take the expdp of the table  scott.emp

2. impdp scott/scott tables=EMP remap_table=emp:emp_temp3 directory=DATA_PUMP_DIR dumpfile=EMP_table.dmp logfile=impdpEMP3.log

it will create new table as emp_temp3