Database Scripts
Script_1: pwfile_users
select 'userswith sysdba privilege' from
dual;
select * from v$pwfile_users;
Script_2: Logged in user to the database
set pagesize 150;
select 'LOGGED IN USERS' from dual;
select username from v$session where
username is not null;
/
/
/
Script_3: Component of the database
set linesize 150;
column comp_name format a30;
column COMP_ID format a12;
column COMP_NAME format a50;
select 'ComponentName with Version' from
dual;
select comp_id, comp_name, version from
dba_registry where status='VALID';
/
Script_4: Installed Component of the
database
set pages 999
col c1 heading 'feature' format a45
col c2 heading 'times|used' format 999,999
col c3 heading 'first|used'
col c4 heading 'used|now'
select 'Feature In Used By Database' from
dual;
select name c1, detected_usages c2,
first_usage_date c3, currently_used c4 from dba_feature_usage_statistics where
first_usage_date is not null;
/
Script_5: Locked Account
set linesize 150
set pagesize 300
column username format a30;
column EXPIRY_DATE format a24;
select 'LOCKED ACCOUNT' from dual;
SELECT username, account_status, created,
lock_date, expiry_date FROM dba_users WHERE account_status != 'OPEN';
/
select 'DATABASE SIZE IN GB' from dual;
SELECT a.data_size + b.temp_size +
c.redo_size + d.controlfile_size "total_size in GB" FROM (SELECT SUM
(bytes) / 1024 / 1024/1024 data_size FROM dba_data_files) a,
(SELECT NVL (SUM (bytes), 0) / 1024 /
1024/1024 temp_size FROM dba_temp_files) b,
(SELECT SUM (bytes) / 1024 / 1024/1024
redo_size FROM sys.v_$log) c,
(SELECT SUM (BLOCK_SIZE * FILE_SIZE_BLKS) /
1024 / 1024/1024 controlfile_size FROM v$controlfile) d;
/
select
'DBSIZE_USED_&_FREE_SPACE' from dual;
set
linesize 130
col
"Database Size" format a20
col
"Free space" format a20
col
"Used space" format a20
select
round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) - round(free.p / 1024 / 1024 /
1024) || ' GB' "Used space", round(free.p / 1024 / 1024 / 1024) || '
GB' "Free space"
from
(select bytes from v$datafile union all select bytes from v$tempfile union all
select bytes from v$log) used , (select sum(bytes) as p from dba_free_space)
free group by free.p ;
Script_7: Tablespace Size Monitoring
SELECT
a.tablespace_name,
ROUND (((c.BYTES - NVL (b.BYTES, 0)) / c.BYTES) * 100,2)
percentage_used,
c.BYTES / 1024 / 1024 space_allocated,
ROUND (c.BYTES / 1024 / 1024 - NVL (b.BYTES, 0) / 1024 / 1024,2)
space_used,
ROUND (NVL (b.BYTES, 0) / 1024 / 1024, 2) space_free,
c.DATAFILES
FROM dba_tablespaces a,
( SELECT tablespace_name, SUM (BYTES) BYTES FROM dba_free_space GROUP BY tablespace_name) b,
( SELECT COUNT
(1) DATAFILES, SUM (BYTES) BYTES, tablespace_name FROM dba_data_files GROUP BY tablespace_name) c
WHERE b.tablespace_name(+) = a.tablespace_name
AND c.tablespace_name(+) = a.tablespace_name
ORDER BY NVL (((c.BYTES - NVL (b.BYTES, 0))
/ c.BYTES), 0) DESC;
column
"Tablespace" format a13
column
"Used MB" format 99,999,999
column
"Free MB" format 99,999,999
column
"Total MB" format 99,999,999
select
fs.tablespace_name
"Tablespace",
(df.totalspace - fs.freespace) "Used MB",
fs.freespace "Free
MB",
df.totalspace "Total
MB",
round(100 * (fs.freespace / df.totalspace))
"Pct. Free"
from
(select tablespace_name, round(sum(bytes) /
1048576) TotalSpace from dba_data_files group by tablespace_name) df,
(select tablespace_name, round(sum(bytes) /
1048576) FreeSpace from dba_free_space group by tablespace_name) fs
where
df.tablespace_name = fs.tablespace_name;
Script_8: Free space in all tablespace of
database
select
a.file_id,
substr(a.tablespace_name,1,14) tablespace_name,
trunc(decode(a.autoextensible,'YES',a.maxsize-a.bytes+b.free,'NO',b.free)/1024/1024)
free_mb,
trunc(a.bytes/1024/1024) size_mb,
trunc(a.maxsize/1024/1024) maxsize_mb,
a.autoextensible ae,
trunc(decode
(a.autoextensible,'YES',
(a.maxsize-a.bytes+b.free)/a.maxsize*100,'NO',b.free/a.maxsize*100
)
) free_pct
from
(select file_id,
tablespace_name,
autoextensible,
bytes,
decode(autoextensible,'YES',maxbytes,bytes) maxsize
from dba_data_files
group by file_id,
tablespace_name,
autoextensible,
bytes,
decode(autoextensible,'YES',maxbytes,bytes)) a,
(select file_id,
tablespace_name,
sum(bytes) free
from dba_free_space
group by file_id,
tablespace_name) b
where a.file_id=b.file_id(+)
and
a.tablespace_name=b.tablespace_name(+)
order by a.tablespace_name asc;
Script_9: Analyze invalid Object
COLUMN owner FORMAT A30
COLUMN object_name FORMAT A30
set linesize 130
SELECT owner, object_type, object_name,
status FROM dba_objects WHERE status = 'INVALID' ORDER BY owner,
object_type, object_name;
Script_10: Last Analyzed Object (Dictinory
Statistics)
select 'Dictionary statistics' from dual;
Column table_name format a30
SELECT TABLE_NAME, LAST_ANALYZED, NUM_ROWS,
SAMPLE_SIZE FROM DBA_TABLES WHERE OWNER = 'SYS' ORDER BY LAST_ANALYZED ASC;
/
Script_11: Fixed Object Statistics (Dictinory Statistics)
select 'Fixed Object Statistics' from dual;
Column table_name format a30
SELECT TABLE_NAME, LAST_ANALYZED FROM
DBA_TAB_STATISTICS WHERE OWNER = 'SYS' AND TABLE_NAME LIKE 'X$%' ORDER BY LAST_ANALYZED
ASC;
/
Script_12: System Statistics (Dictinory Statistics)
select 'System Statistics' from dual;
column date format a20;
SELECT PNAME, SUBSTR (PVAL2, 1, 40)
"DATE" FROM SYS.AUX_STATS$ WHERE SNAME = 'SYSSTATS_INFO' AND PNAME IN
('DSTART', 'DSTOP');
Script_13: DBA Jobs
SET LINESIZE 80
column job format 9999
column log_user format A10
column next_date format A9
column next_sec format A8
column interval format A15
column what format A26
-- scheduled jobs
select job,broken, log_user, next_date,
next_sec, interval, what from dba_jobs;
--completed/failed jobs
select job,broken, log_user, last_date,
last_sec, broken,failures from dba_jobs;
Note: queries allowed on fixed tables or
views only
Script_14: Running Jobs
SET LINESIZE 80 PAGESIZE 14
SELECT a.job "Job",
a.sid,
a.failures "Failures",
Substr(To_Char(a.last_date,'DD-Mon-YYYY HH24:MI:SS'),1,20) "Last
Date",
Substr(To_Char(a.this_date,'DD-Mon-YYYY HH24:MI:SS'),1,20) "This
Date"
FROM
dba_jobs_running a;
Script_15: dba scheduler job
SELECT owner, program_name, enabled FROM
dba_scheduler_programs;
column JOB_NAME format a30;
column Job_class format a30;
set linesize 130;
SELECT owner, job_name, job_class, enabled
FROM dba_scheduler_jobs;
$ rman target /
RMAN>
run
{
backup database tag='today backup';
backup current controlfile tag='today backup';
}
RMAN>
BACKUP DATABASE PLUS ARCHIVE LOG;
RMAN>
run
{
CROSSCHECK BACKUPSET; # To check available and expired backup sets.
CROSSCHECK ARCHIVELOG ALL; # To check available and expired Archive Logs
DELETE NOPROMPT EXPIRED BACKUP; # It will delete expired(old/not exists physicaly) backups
DELETE NOPROMPT EXPIRED ARCHIVELOG ALL; # It will delete expired archive logs
SQL ‘ALTER SYSTEM ARCHIVE LOG CURRENT’; # Switch Log
BACKUP DATABASE PLUS ARCHIVELOG; # Backup the whole Database plus archive files
DELETE NOPROMPT OBSOLETE; # It will remove old backups according to the redundancy period.
}
Subscribe to:
Posts
(
Atom
)
Nice Blog!
ReplyDelete