Daily Used Queries

To delete old / unused files:

find /u01/inst/apps/QAT/logs/appl/conc/out -type f -mtime +30 -exec rm -f {} \;    --To delete files older than 30 days

find /path/to/ -type f -mtime +7 -name '*.gz' -execdir rm -- '{}' \;                        --To delete gzip files older than 7days

find . -mtime +10 -print -exec gzip {} \;   --for gzip files                                       --To gzip files older than 10days

find . -mtime +10 | xargs  tar -czvPf  trace_log_$(date +%F).tar.gz                       --To tar files older than 10days

find . -type f -mtime +30 -exec rm -f {} \;                                                           --To remove files older than 30days

delete noprompt archivelog until time 'SYSDATE-15';                                         -- To remove archives from rman older than 15days

To check Inactive / Blocking sessions

select sid,serial#,username,status,schemaname,logon_time from v$session where status='INACTIVE' and username is not null;

select distinct vlock.id1,decode(vlock.request,0,'Holder','Waiter') Type,vses.client_identifier,vses.sid,vses.serial#,vses.inst_id,vses.status,round(vses.last_call_et/60) mins,vses.event,vses.module,vses.sql_id,vses.machine,vses.row_wait_obj#

from gv$session vses, gv$lock vlock

where vlock.sid=vses.sid and vlock.inst_id=vses.inst_id

and (vlock.id1,vlock.id2,vlock.type) in (select id1, id2, type from gv$lock where request>0)

and vlock.ctime/60 > 5

order by id1 ;

To Drop a User who is connected:

DECLARE

  v_user_exists NUMBER;

  user_name CONSTANT varchar2(20) := 'XXEIS';

BEGIN

  LOOP

    -- Kill any currently active sessions

    FOR c IN (SELECT s.sid, s.serial# FROM v$session s WHERE upper(s.username) = user_name)

    LOOP

      EXECUTE IMMEDIATE

        'alter system kill session ''' || c.sid || ',' || c.serial# || ''' IMMEDIATE';

    END LOOP;

    -- Try to drop the user.

    BEGIN

      EXECUTE IMMEDIATE 'drop user ' || user_name || ' cascade';

      EXCEPTION WHEN OTHERS THEN

      IF (SQLCODE = -1940) THEN

        -- Ignore ORA-01940 'Cannot drop user that is currently connected' exception

        NULL;

      ELSE

        RAISE;

      END IF;

    END;

    -- Continue looping, killing sessions and retrying to drop until successful

    BEGIN

      SELECT COUNT(*) INTO v_user_exists FROM dba_users WHERE username = user_name;

      EXIT WHEN v_user_exists = 0;

    END;

  END LOOP;

END;

/

To Get error from the files:

egrep -i "PLS-|ORA-|unab-|Unab-|Error-" *.log

grep -iE "ORA-|Error-|Unab-"  filename

Data Pump:

CREATE DIRECTORY XXSBIWFA AS '/u02/XXSBIWFA';

select *from dba_directories;

GRANT  read,  write  ON  DIRECTORY  XXSBIWFA  TO  XXSBIWFA;

GRANT  DATAPUMP_EXP_FULL_DATABASE  TO  hr;

GRANT  read,  write  ON  DIRECTORY  SBI_DIR  TO  XXEBS;

expdp \"/ as sysdba\" schemas=XXOCA_M directory=XXOCA_M_EXP dumpfile=XXOCA_M.dmp logfile=XXOCA_M.log

expdp XXSBIWFA/XXSBIWFA schemas=XXSBIWFA directory=XXSBIWFA dumpfile=XXSBIWFA3rdSep.dmp logfile=expdp_XXSBIWFA.log

impdp \'/ AS SYSDBA\' remap_schema=XXSBIWFA:XXSBIWFA2 dumpfile=XXSBIWFA3rdSep.dmp directory=XXSBIWFA  logfile=XXSBIWFAimp.log

select *from tab;

To Compile Invalid Objects:

select owner,object_name,object_type,status from dba_objects where STATUS='INVALID' and OWNER='XXSBIWFA';

EXEC DBMS_UTILITY.compile_schema(schema => 'XXSBIWFA2');

 @/u01/DEMO2_DB/db/tech_st/11.2.0/rdbms/admin/utlrp.sql

SELECT COUNT (*) FROM ALL_OBJECT WHERE STATUS='INVALID' ;

exec DBMS_UTILITY.compile_schema(schema => 'XXEIS');

select object_name, object_type, owner from dba_objects where status='INVALID' and owner='XXSPLASH_STOWERSLST' ;

EXEC DBMS_UTILITY.compile_schema(schema => 'XXSPLASH_STOWERSLST') ;

To Check the Quota of Tablespace Size:

set linesize 300

set pagesize 300

select a.tablespace_name, b.total,

       c.available, d.large_extent,

       (b.total - c.available) used,

       round((((b.total - c.available)*100)/b.total),2) pctused

  from (select tablespace_name, (sum(bytes)/1024)/1024 total

          from dba_data_files group by tablespace_name)  b,

       (select tablespace_name,  nvl(round(((sum(bytes)/1024)/1024),2),0) available

          from dba_free_space group by tablespace_name)  c,

       (select tablespace_name, (max(bytes)/1024)/1024 large_extent

          from dba_free_space group by tablespace_name)  d,

       dba_tablespaces  a

where a.tablespace_name = b.tablespace_name(+)

   and a.tablespace_name = c.tablespace_name(+)

   and a.tablespace_name = d.tablespace_name(+)

/

 select default_tablespace from dba_users where username='XXWFAOCA';

select file_name,file_id,bytes/1024/1024/1024 from dba_data_files where tablespace_name='TEMP';

To get version of File name at OS Level

strings -a | grep '$Header' file_name

adident Header file_name

Convert Trace files with TKPROF

tkprof <trace_File>.trc <tkprof>.txt sys=no explain=apps/<apps pwd>