Anuroop Challa

======================================================================== 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 || '.' || OBJECT_NAME || ' COMPILE;'

================================================ 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 LIKE '%Database Replay%' OR name LIKE '%EM%' OR name LIKE '%Encrypt%' OR name LIKE '%Exadata%' OR name

=============================================================== 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    owner; ========check table size with table name ================= s

simple and frequent use commands -- can not update all same time will update more day by day cd   ==> change directory. ls   ==> the list command. mkdir ==> allows the user to make a new directory rmdir  ==> remove directory ( Be very careful using the rm command ). rm ==>   the rmdir command will remove directories and files held within, the rm command. will delete created files( Be very careful using the rm command ). CP  ==> allows a user to copy a file to another folder or directory. mv  ==>  allows a user to move a file to another folder or directory. touch ==>  allows users to make files using the(exaple  touch test.txt ). more ==>  Look at file, one page at a time. cat  ==>   concatenate and display files. bdf ==> See how much free disk space (in linux use df ).  du ==>  Estimate disk usage of directory in Bytes. top ==>  Print system usage and top resource . gzip ==>  compress file (ex:  gzip filename). gunzip

little detailed in commands.. for shel script use -----There are many commands will add few and update it day by day---- --date command in different way use in shel $ date Fri Oct 27 09:27:04 EAT 2017 $ date '+%a' Fri $ date '+%d-%b-%y' 27-Oct-17 $ date '+%d-%b-%y %a' 27-Oct-17 Fri ----------same in capital letter  $ date '+%d-%b-%y %a' | tr '[a-z]' '[A-Z]' 27-OCT-17 FRI ================= ======================== ======================== chmod ======================== ======================== ================= Change file permissions there are 3type of permisons  r ead,  w rite, ande  x ecute EXAMPLE:  chmod u=rwx,g=rx,o=r filename The letters  u ,  g , and  o  stand for " user ", " group ", and " other ". (" = ") means "set the permissions exactly like this letters " r ", " w ", and " x " stand for "read", &quo