Anuroop Challa

  How to check compression enabled on partition table ======================================= select a.partition_name, a.tablespace_name, a.compression, a.compress_for , round(sum(a.num_rows / a.blocks),0) rows_per_block, sum(a.num_rows) num_rows, sum(a.blocks) blocks , sum(b.bytes) /(1024*1024) mb from dba_tab_partitions a, dba_segments b where a.table_name = 'TESTCOMP' and a.partition_name = b.partition_name and a.table_name = b.segment_name group by a.partition_name, a.tablespace_name, a.compression, a.compress_for order by 1;

Gather stats in Database level:- EXEC DBMS_STATS.GATHER_DATABASE_STATS( ESTIMATE_PERCENT=>100,METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY',       CASCADE => TRUE,       degree => 4,       OPTIONS => 'GATHER STALE',       GATHER_SYS => TRUE,       STATTAB => PROD_STATS); ======================================================================= CASCADE => TRUE :Gather statistics on the indexes as well. If not used Oracle will decide whether to collect index statistics or not. DEGREE => 4 :Degree of parallelism. OPTIONS :        =>’GATHER’ :Gathers statistics on all objects in the schema.        =>’GATHER AUTO’ :Oracle determines which objects need new statistics, and determines how to gather those statistics.        =>’GATHER STALE’:Gathers statistics on stale objects. will return a list of stale objects...

EXPLAIN PLAN OF SQL ==================== First connect sqlplus and generate plan like below, with out executing sql. ---i will take below sql for example---  select * from emp where empno = 202 ---how to generate plan without execution. EXPLAIN PLAN FOR  select * from emp where empno = 202; ---- how to display generated plan-- SET LINESIZE 300 SET PAGESIZE 300 SELECT * FROM table(DBMS_XPLAN.DISPLAY); --this sql will displays the plan.

Scripts to check DBMS jobs running ====================================== select job_name, session_id, running_instance, elapsed_time, cpu_used from dba_scheduler_running_jobs; if you want to stop job, you can stop by using  below command (or) kill by using SID =============================================================== exec DBMS_SCHEDULER.stop_JOB (job_name => '<JOB_NAME>');

archive log from db if you want to delete older item with out any backup you can fallow below one of the procedure. ============SYSDATE===in below is 7day u can change per yourt need================= rman connect target / crosscheck archivelog all; delete noprompt archivelog until time 'sysdate-7'; crosscheck archivelog all; delete noprompt expired archivelog all; LIST ARCHIVELOG ALL; crosscheck archivelog all; exit ======Using until time and date===in below you can change per your need================= rman connect target / crosscheck archivelog all; run{ DELETE archivelog until time "to_date('2017-04-11:08:00:00','YYYY-MM-DD:hh24:mi:ss')"; } crosscheck archivelog all; delete noprompt expired archivelog all; LIST ARCHIVELOG ALL; crosscheck archivelog all; exit

How to check actual undo size and needed undo size in oracle =============================================== select d.undo_size/(1024*1024) "ACTUAL UNDO SIZE (MEGS)",   (to_number(e.value) * to_number(f.value) * g.undo_block_per_sec) /   (1024*1024) "NEEDED UNDO SIZE (MEGS)"   from (select sum(a.bytes) undo_size   from v$datafile a,v$tablespace b,dba_tablespaces c   where c.contents = 'UNDO'   and c.status = 'ONLINE'   and b.name = c.tablespace_name   and a.ts# = b.ts#) d,   v$parameter e,   v$parameter f,   (select max(undoblks/((end_time-begin_time)*3600*24))   undo_block_per_sec   from v$undostat) g   where e.name = 'undo_retention'   and f.name = 'db_block_size';

---------to check inventory ok or not------------- select dbms_sqlpatch.verify_queryable_inventory from dual; -------------------------------------------------------------------------------------------------------------------------- ORACLE12c check script -------------------------------------------------------------------------------------------------------------------------- set pages 300; set lines 300; col VERSION format a10; col STATUS format a10; col ACTION_TIME format a20; col DESCRIPTION format a80; select PATCH_ID,PATCH_UID, VERSION,STATUS, ACTION_TIME,DESCRIPTION from dba_registry_sqlpatch; -------------------------------------------------------------------------------------------------------------------------- ORACLE11G check script -------------------------------------------------------------------------------------------------------------------------- set lines 300 set pages 300 col ACTION format a15 col NAMESPACE format a15 col version format a1...

Script To check what UNDO space is available =================================== select /*+ rule */ segs.tablespace_name, segs.segment_type, df.bytes/1024/1024 ts_mb , nvl(usegs.bytes,0)/1024/1024 active_unexp_mb,  nvl(fs.bytes,0)/1024/1024 free_mb,  round(decode(segment_type,'ROLLBACK',nvl(fs.bytes,0)/df.bytes*100,  'TYPE2 UNDO',round((df.bytes-nvl(usegs.bytes,0))/df.bytes*100))) usable_pct,  round(nvl(fs.bytes,0)/df.bytes*100) ts_free_pct  from (select /*+ rule */ distinct tablespace_name, segment_type from dba_segments  where segment_type in ('ROLLBACK','TYPE2 UNDO')) segs,  (select /*+ rule */ tablespace_name, sum(bytes) bytes from dba_data_files  where tablespace_name in (select /*+ rule */ distinct tablespace_name from dba_segments  where segment_type in ('ROLLBACK','TYPE2 UNDO')) group by tablespace_name) df,  (select /*+ rule */ tablespace_name, sum(bytes) bytes from dba_undo_extents  where status in ('ACTIVE','UNE...

scripts to check RMAN back up information - script2 =============================================== in this scripts -2 you get OUPUT rate write to tape ================================================ SELECT (SELECT instance_name FROM v$instance) || ' ' || (SELECT instance_number FROM v$instance) instance,rs.sid, rj.COMMAND_ID,rj.STATUS,max(rj.START_TIME) STARTED_TIME, rj.END_TIME,trunc(rj.ELAPSED_SECONDS/60) as "Elapsed_Minutes",rj.INPUT_TYPE,rj.OUTPUT_DEVICE_TYPE, rj.INPUT_BYTES/1024/1024/1024 "DB_SIZE(GB)", rj.OUTPUT_BYTES/1024/1024/1024 "Backup_Size", rj.OUTPUT_BYTES_PER_SEC_DISPLAY "Output_rate(p/s)" from v$rman_backup_job_details rj, v$rman_status rs where rj.COMMAND_ID=rs.COMMAND_ID group by rs.sid,rj.COMMAND_ID,rj.STATUS,rj.START_TIME,rj.END_TIME,rj.ELAPSED_SECONDS,rj.INPUT_TYPE,rj.OUTPUT_DEVICE_TYPE,rj.INPUT_BYTES,rj.OUTPUT_BYTES,rj.OUTPUT_BYTES_PER_SEC_DISPLAY having max(rj.START_TIME) > sysdate-10 order by rj.STAR...

scripts to check RMAN back up  information. =================================================================== Below script will provide you clear details enter number days to get output as per your need. =================================================================== set lines 220 set pages 1000 col cf for 9,999 col df for 9,999 col elapsed_seconds heading "ELAPSED|SECONDS" col i0 for 9,999 col i1 for 9,999 col l for 9,999 col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES" col session_recid for 999999 heading "SESSION|RECID" col session_stamp for 99999999999 heading "SESSION|STAMP" col status for a10 trunc col time_taken_display for a10 heading "TIME|TAKEN" col output_instance for 9999 heading "OUT|INST" select   j.session_recid, j.session_stamp,   to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,   to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,   (j.output_by...

Script to check sessions/process infromation from statspack. ----- please check once before use script and modify sysdate as per your need---------- select B.SNAP_TIME,A.CURRENT_UTILIZATION from STATS$RESOURCE_LIMIT A,PERFSTAT.STATS$SNAPSHOT B where  a.snap_ID=b.snap_ID AND A.RESOURCE_NAME  ='processes' and TRUNC(B.SNAP_TIME) >= TRUNC(sysdate-2) order by A.snap_ID; ------------------------------------- Thanks for your vist!!! Any suggestions or you have any better scipt please share in comment session it helps me in future posts ------------------------------------

script to check session information from awr snap. ===========T his script will helps you to check session histaory from awr snap use with change sysdate as per neeed= =========== ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS'; SELECT to_char(TRUNC(s.begin_interval_time,'HH24'),'DD-MON-YYYY HH24:MI:SS') snap_begin, sum(r.current_utilization) sessions FROM dba_hist_resource_limit r, dba_hist_snapshot s WHERE ( TRUNC(s.begin_interval_time,'HH24'),s.snap_id ) IN ( --Select the Maximum of the Snapshot IDs within an hour if more than one snapshot IDs --have the same number of sessions within that hour , so then picking one of the snapIds SELECT TRUNC(sn.begin_interval_time,'HH24'),MAX(rl.snap_id) FROM dba_hist_resource_limit rl,dba_hist_snapshot sn WHERE TRUNC(sn.begin_interval_time) >= TRUNC(sysdate-2) AND rl.snap_id = sn.snap_id AND rl.resource_name = 'sessions' AND rl.instance_number = sn.instance_numb...

srdc_expdp_performance.sql   ==> create sql from below script and run --------------- REM srdc_exp_performance.sql - Gather Information for EXPDP Performance Issues define SRDCNAME='EXPDP_PERFORMANCE' SET MARKUP HTML ON PREFORMAT ON set TERMOUT off FEEDBACK off verify off TRIMSPOOL on HEADING off set lines 132 pages 10000 COLUMN SRDCSPOOLNAME NOPRINT NEW_VALUE SRDCSPOOLNAME select 'SRDC_'||upper('&&SRDCNAME')||'_'||upper(instance_name)||'_'||to_char(sysdate,'YYYYMMDD_HH24MISS') SRDCSPOOLNAME from v$instance; set TERMOUT on MARKUP html preformat on REM spool &&SRDCSPOOLNAME..htm select '+----------------------------------------------------+' from dual union all select '| Diagnostic-Name: '||'&&SRDCNAME' from dual union all select '| Timestamp:       '||to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS TZH:TZM') from dual union all select '| Machine:     ...

Data pump export size estimate script following script will provide you  data in each schema on your database(MB).  you can change if want GB as per need.  Hope with the help of this script you will get aprox size of export backup. ----------------------------------------------------------------------------------------------------------------------- SELECT owner, ROUND(SUM(size_mb)) MBytes FROM ( SELECT owner, segment_name, segment_type, partition_name, ROUND(bytes/(1024*1024),2) SIZE_MB, tablespace_name FROM DBA_SEGMENTS WHERE SEGMENT_TYPE IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION') --AND TABLESPACE_NAME LIKE 'COSTE%' --AND SEGMENT_NAME LIKE 'OE_ORDER_LINES_ALL%' --AND partition_name LIKE 'USAGE_FCT_NEW%' --AND OWNER = 'TARGET_DW' --AND ROUND(bytes/(1024*1024),2) > 1000) ) GROUP BY owner ORDER BY MBytes DESC; --------------------------------------- any suggestion are well come, you can comment...

======================================================================== Check and move indexes to Index tablespace.(please check change tablespace name in script data and index) ======================================================================== SELECT 'ALTER INDEX Schema_Name.' ||index_name|| ' REBUILD TABLESPACE Tablescpae_index;' FROM dba_indexes WHERE owner = 'Schema_Name' AND TEMPORARY='N' AND tablespace_name = 'Tablescpae_DATA';

====================================================================== Check tables as in NOLOGGING ====================================================================== SELECT LOGGING,COUNT(*) FROM dba_tables WHERE owner = 'Schema_Name' AND TEMPORARY = 'N' GROUP BY LOGGING ORDER BY 2 DESC; ====================================================================== If found tables in NOLOGGING the move change to LOGGING from the script. ====================================================================== SELECT 'ALTER TABLE Schema_Name.'||table_name|| ' LOGGING;' FROM dba_tables WHERE LOGGING='NO' AND owner = 'Schema_Name' AND TEMPORARY = 'N'; ====================================================================== Check indexes as in NOLOGGING --------------------------------------- SELECT LOGGING,COUNT(*) FROM dba_indexes WHERE owner = 'Schema_Name' AND TEMPORARY = 'N' GROUP BY LOGGING ORDER BY 2 DE...

==================================== Invalid check in database ==================================== COLUMN object_name FORMAT A30 SELECT owner,        object_type,        object_name,        status FROM   dba_objects WHERE  status = 'INVALID' ORDER BY owner, object_type, object_name; =============Invalid  under schema level-============= SELECT COUNT(*) FROM dba_objects WHERE owner = 'Schema_name' AND status <>'VALID'; ==================================== To compile all run utlrp in db level. ==================================== @?/rdbms/admin/utlrp.sql ====================================================== for only invalid compile spool in script and run for invalids ====================================================== set heading off; set feedback off; set echo off; Set lines 999; Spool run_invalid.sql select 'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '.' || OB...

================================================ Check oracle DBA_FEATURE_USAGE_STATISTICS script ================================================ SELECT NAME                  , VERSION               , DETECTED_USAGES       , CURRENTLY_USED        , FIRST_USAGE_DATE      , LAST_USAGE_DATE       , LAST_SAMPLE_DATE      , DESCRIPTION          FROM DBA_FEATURE_USAGE_STATISTICS WHERE  detected_usages > 0 AND ( name LIKE '%ADDM%' OR name LIKE '%Automatic Database Diagnostic Monitor%' OR name LIKE '%Automatic Workload Repository%' OR name LIKE '%AWR%' OR name LIKE '%Baseline%' OR (name LIKE '%Compression%' AND name NOT LIKE '%HeapCompression%')  -- (#46352) - Ignore HeapCompression in dba fus) OR name LIKE '%Data Guard%' OR name LIKE '%Data Mining%' OR name L...

=============================================================== check sizes =============================================================== ============check datafile size=================== SELECT SUM(a.bytes/1048576) Megs_Alloc,        SUM(b.bytes/1048576) Megs_Free,        SUM((a.bytes-b.bytes)/1048576) Megs_Used FROM (SELECT SUM(a.bytes) bytes FROM sys.dba_data_files a) a, (SELECT NVL(SUM(b.bytes),0) bytes FROM sys.dba_free_space b) b; =============Check tablespace ============== SELECT tablespace_name, SUM(bytes/1024/1024) FROM dba_data_files GROUP BY tablespace_name ORDER BY 2 DESC; ========check schema size ================= ----- All schema ------ select OWNER,sum(BYTES/1024/1024) from dba_segments group by owner order by 2; -----particular schema---- select    owner,    sum(bytes)/1024/1024/1024 schema_size_gig from    dba_segments where owner='schema_name' group by   ...

 set pages 500  set lines 125  SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type",  d.extent_management "Management",  TO_NUMBER(TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99999990D900')) "Size (M)",  TO_NUMBER(TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024 ,'99999990D900')) "Used (M)",  TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990D00') "Used %"  FROM  sys.dba_tablespaces d,  (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a,  (select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f  WHERE  d.tablespace_name = a.tablespace_name(+)  AND d.tablespace_name = f.tablespace_name(+)  AND NOT (d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')  UNION ALL  SELECT d.status "Status",  d.tablespace_name "Name",  d.contents ...