DBA SCRIPTS

1) DROP USER OBJECTS:

conn username/password —>>> MANDATORY to connect as a user

declare
cursor fkc is select table_name,
constraint_name
from user_constraints
where constraint_type =’R’;
tname user_constraints.table_name%TYPE;
cname user_constraints.constraint_name%TYPE;
begin
open fkc;
loop
fetch fkc into tname, cname;
exit when fkc%NOTFOUND;
dbms_output.put_line(‘alter table ‘||tname||’ drop constraint ‘||cname);
execute immediate ‘alter table ‘||tname||’ drop constraint ‘||cname;
end loop;
close fkc;

end;
/

declare
cursor fkc is select object_name,
object_type
from user_objects
where object_name not in
(‘INDEX’,’PACKAGE BODY’);
obj_name user_objects.object_name%TYPE;
obj_type user_objects.object_type%TYPE;
begin
open fkc;
loop
fetch fkc into obj_name, obj_type;
exit when fkc%NOTFOUND;
dbms_output.put_line(‘Drop ‘||obj_type||’ ‘||obj_name);
begin
execute immediate ‘Drop ‘||obj_type||’ ‘||obj_name;
exception
when others then null;
end;
end loop;
close fkc;
end;
/

PURGE RECYCLEBIN;

DECLARE

the_job user_jobs.job%TYPE;
cursor c1 is select job from user_jobs;
BEGIN
open c1;
loop
fetch c1 into the_job;
exit when c1%NOTFOUND;
dbms_job.remove(the_job);
end loop;
close c1;
END;
/

VERIFY :

set heading off
SQL >   select ‘Objects left in schema : ‘ from dual;
SQL>    select object_name,object_type from user_objects;

SQL>   select ‘Jobs left in schema: ‘ from dual;
SQL>   select job,what from user_jobs;

2) User privileges & Grantee :

SELECT grantee, privilege, admin_option
FROM sys.dba_sys_privs
WHERE (privilege LIKE ‘% ANY %’
OR privilege IN (‘BECOME USER’, ‘UNLIMITED TABLESPACE’)
OR admin_option = ‘YES’)
AND grantee NOT IN (‘SYS’, ‘SYSTEM’, ‘OUTLN’, ‘AQ_ADMINISTRATOR_ROLE’,
‘DBA’, ‘EXP_FULL_DATABASE’, ‘IMP_FULL_DATABASE’,
‘OEM_MONITOR’, ‘CTXSYS’, ‘DBSNMP’, ‘IFSSYS’,
‘IFSSYS$CM’, ‘MDSYS’, ‘ORDPLUGINS’, ‘ORDSYS’,
‘TIMESERIES_DBA’)

3) Free Space :
//** This script lists all Tablespaces and its datafiles with their free and used space **//
SET SERVEROUTPUT ON

SET PAGESIZE 1000

SET LINESIZE 255

SET FEEDBACK OFF

SELECT Substr(df.tablespace_name,1,20) “Tablespace Name”,

Substr(df.file_name,1,40) “File Name”,

Round(df.bytes/1024/1024,2) “Size (M)”,

Round(e.used_bytes/1024/1024,2) “Used (M)”,

Round(f.free_bytes/1024/1024,2) “Free (M)”,

Rpad(‘ ‘|| Rpad (‘X’,Round(e.used_bytes*10/df.bytes,0), ‘X’),11,’-‘) “% Used”

FROM   DBA_DATA_FILES  df,

(SELECT file_id, Sum(Decode(bytes,NULL,0,bytes)) used_bytes

FROM dba_extents  GROUP by file_id)  e,

(SELECT Max(bytes) free_bytes, file_id

FROM dba_free_space   GROUP BY file_id) f

WHERE  e.file_id (+) = df.file_id

AND    df.file_id  = f.file_id (+)

ORDER BY df.tablespace_name, df.file_name;

4) Tablespaces :

//** This script lists all Tablespaces with their Sizes **//
SELECT /* + RULE */  df.tablespace_name “Tablespace”,
df.bytes / (1024 * 1024) “Size (MB)”,
SUM(fs.bytes) / (1024 * 1024) “Free (MB)”,
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) “% Free”,
Round((df.bytes – SUM(fs.bytes)) * 100 / df.bytes) “% Used”
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+)  = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / (1024 * 1024),
SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) – df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) – df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+)  = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4 DESC;
5) Locked_objects :

SET LINESIZE 500

SET PAGESIZE 1000

SET VERIFY OFF

COLUMN owner FORMAT A20

COLUMN username FORMAT A20

COLUMN object_owner FORMAT A20

COLUMN object_name FORMAT A30

COLUMN locked_mode FORMAT A15

SELECT b.session_id AS sid,

NVL(b.oracle_username, ‘(oracle)’) AS username,

a.owner AS object_owner,

a.object_name,

Decode(b.locked_mode, 0, ‘None’,

1, ‘Null (NULL)’,

2, ‘Row-S (SS)’,

3, ‘Row-X (SX)’,

4, ‘Share (S)’,

5, ‘S/Row-X (SSX)’,

6, ‘Exclusive (X)’,

b.locked_mode) locked_mode,

b.os_user_name

FROM   dba_objects a,

v$locked_object b

WHERE  a.object_id = b.object_id

ORDER BY 1, 2, 3, 4;

SET PAGESIZE 14

SET VERIFY ON



6) Data Pump Monitoring Script :

select sid, serial#, sofar, totalwork, dp.owner_name, dp.state, dp.job_mode

from gv$session_longops sl, gv$datapump_job dp

where sl.opname = dp.job_name and sofar != totalwork;

7) RMAN Job Monitoring Script :

SQL >  SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) “%_COMPLETE”

FROM V$SESSION_LONGOPS

WHERE OPNAME LIKE ‘RMAN%’  AND OPNAME NOT LIKE ‘%aggregate%’

AND TOTALWORK != 0 AND SOFAR <> TOTALWORK ;

To see the Rman status and SID:

SQL >  SELECT s.SID, p.SPID, s.CLIENT_INFO FROM V$PROCESS p, V$SESSION s

WHERE p.ADDR = s.PADDR AND CLIENT_INFO LIKE ‘rman%’;

8) To see what USERS are Running :
SELECT a.sid, a.serial#, a.username, b.sql_text FROM v$session a, v$sqlarea b
WHERE a.sql_address=b.address;

**** To see for a particular USER, what he is running *******

SELECT a.sid, a.serial#, a.username, b.sql_text FROM v$session a, v$sqlarea b
WHERE a.sql_address=b.address and a.username = ‘<username>’;

9) Find FREE/USED/TOTAL size of oracle database :

(used space):

—————-

SQL> select sum(bytes)/1024/1024/1024 GB from dba_segments;

(free space):

—————

SQL> select sum(bytes)/1024/1024/1024 GB from dba_free_space;

(Total database size):

—————————

SQL> select sum(bytes)/1024/1024/1024 GB from dba_data_files;

  +

(Temp size):

—————

SQL> select SUM(bytes_used)/1024/1024/1024 GBused, SUM(bytes_free)/1024/1024/1024 GBfree from  v$temp_space_header;

(or)

SELECT SUM (a.log_space + b.data_space + c.tempspace) “Total_DB_Size (G)”
FROM (SELECT ROUND (SUM (BYTES/1024/1024/1024), 2) data_space  FROM dba_data_files) b,(SELECT ROUND (SUM (BYTES*members/1024/1024/1024), 2) log_space  FROM v$log) a, (SELECT NVL(ROUND(SUM(BYTES/1024/1024/1024),2), 0) tempspace
FROM dba_temp_files) c;

10) RMAN Cold Backup :

Rman target / nocatalog

Rman > configure controlfile autobackup on;

Rman >  run {

2>  allocate channel d1 type disk;

3> Backup full tag full_offline_bkup

4> Format ‘/var/backup/corppsdb/rman/db_t%t_s%s_p%p’

5> Database plus archivelog;

6> Release channel d1;

}

Advertisements
Previous Post
Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: