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';
