%20(1).png)
Database Information Script
-- -----------------------------------------------------------
-- Find Database Info
--find_db_info.sql
--to run : goto sqlplus and run
--@/path/to/your/find_db_info.sql
-- -----------------------------------------------------------
set echo off
set lines 140
set pages 2000
COLUMN spool_file NEW_VALUE spool_file NOPRINT
SELECT HOST_NAME || '_' || name || '_db_status_'
|| TO_CHAR (SYSDATE,
'YYYY_MM_DD_hh24mi',
'NLS_DATE_LANGUAGE=''AMERICAN''')
|| '.log'
AS spool_file
FROM v$database, v$instance;
SPOOL &spool_file
col YYYYMMDD for a15 heading "YYYY-MM-DD"
col Date_Time for a25 heading "Date and Time"
select to_char(sysdate,'YYYY-MM-DD') YYYYMMDD, to_char(sysdate,'DD-Mon-YY HH24:MI:SS') Date_Time from dual;
prompt =====================================
prompt Database Info
prompt =====================================
col host_name for a20 heading "Host Name"
col DB_Name for a13 heading "DB Name"
col version for a10 heading "DB Version"
col LOG_MODE for a15 heading "Log Mode"
col Started_Time for a20 heading "Started Time"
col FLASHBACK_ON for a15 heading "Flashback"
col db_created_date for a18 heading "DB Created Date"
col platform_name for a25
SELECT host_name,
NAME DB_Name,
version,
TO_CHAR(CREATED,'DD/Mon/YYYY HH24:MI') db_created_date,
platform_name
FROM V$database,
V$instance;
SELECT NAME DB_Name,
LOG_MODE "Log Mode",
OPEN_MODE,
FLASHBACK_ON,
PROTECTION_MODE,
TO_CHAR(STARTUP_TIME,'DD/MM/YYYY HH24:MI:SS') Started_Time,
ROUND(SYSDATE - STARTUP_TIME,2) "Days Up"
FROM V$database,
V$instance;
prompt =====================================
prompt Importent Parameter Values
prompt =====================================
col name for a35 heading "Parameter Name";
col value for a90;
SELECT name, VALUE
FROM v$parameter
WHERE name IN
('db_name',
'db_unique_name',
'global_names',
'db_block_size',
'db_file_multiblock_read_count',
'control_file_record_keep_time',
'cursor_sharing',
'open_cursors',
'processes',
'statistics_level',
'os_authent_prefix',
'deferred_segment_creation',
'memory_max_target',
'memory_target',
'sga_max_size',
'sga_target',
'pga_aggregate_target',
'processes',
'compatible',
'remote_login_passwordfile',
'sec_case_sensitive_logon',
'sort_area_retained_size',
'sort_area_size',
'workarea_size_policy',
'log_buffer',
'job_queue_processes',
'audit_trail',
'local_listener',
'remote_listener',
'timed_statistics',
'undo_management',
'undo_retention',
'undo_tablespace',
'recyclebin')
AND value is not null
ORDER BY 1;
SELECT name, VALUE
FROM v$parameter
WHERE name IN
('spfile',
'ifile',
'diagnostic_dest',
'background_dump_dest',
'user_dump_dest',
'audit_file_dest',
'utl_file_dir',
'archive_lag_target',
'fal_client',
'fal_server',
'dg_broker_config_file1',
'dg_broker_config_file2',
'dg_broker_start',
'log_archive_format',
'log_archive_dest_1',
'log_archive_dest_2',
'log_archive_dest_3',
'log_archive_dest_4',
'log_archive_dest_5',
'log_archive_dest_state_1',
'log_archive_dest_state_2',
'log_archive_dest_state_3',
'log_archive_dest_state_4',
'log_archive_dest_state_5',
'db_recovery_file_dest',
'db_recovery_file_dest_size',
'db_flashback_retention_target',
'db_file_name_convert',
'db_create_file_dest',
'db_create_online_log_dest_1',
'db_create_online_log_dest_2',
'db_create_online_log_dest_3',
'standby_file_management')
AND value is not null
ORDER BY 1;
prompt =====================================
prompt Database Property Info
prompt =====================================
col PROPERTY_NAME for a30
col PROPERTY_VALUE for a40
SELECT PROPERTY_NAME, PROPERTY_VALUE
FROM database_properties
ORDER BY 1;
prompt =====================================
prompt Users Info
prompt =====================================
col account_status for a25
col username for a25
col profile for a20
BREAK ON account_status on profile SKIP 0;
SELECT d.account_status,
d.profile,
d.username,
u.password,
d.created
FROM dba_users d, SYS.USER$ U
WHERE U.NAME = D.USERNAME
ORDER BY 1,2,3;
prompt =====================================
prompt Find DB and Tablespace Space Details
prompt =====================================
set lines 140
set pages 2000
COL NAME FOR A12 HEADING "DB Name";
COL OPEN_MODE FOR A12 HEADING "Open Mode";
COL HOST_NAME FOR A15 HEADING "Server";
COL VERSION FOR A12 HEADING "Version";
COL Processes FOR A10 HEADING 'Processes'
COL TABLESPACE FOR A25 HEADING 'Tablespace Name'
COL FILE_NAME FOR A55 HEADING 'File Name'
COL AUTOEXTENSIBLE FOR A6 HEADING 'Auto|Extend'
COL MAX_BYTES FOR 999999,990 HEADING 'Max.File|Ext.Size'
COL SIZEMB FOR 999999,990 HEADING 'TBS.Size| (MBs)'
COL USEDMB FOR 9999,990 HEADING 'Used.Size| (MBs)'
COL FREEMB FOR 999,990 HEADING 'Free.Size| (MBs)'
COL FILE_SIZE FOR 999999,990 HEADING 'Data.File|Size(MBs)'
COL PCT_USED FOR 990.00 HEADING 'TBS.%|Used'
COL PCT_FREE FOR 990.00 HEADING 'TBS.%|Free'
COL MAX_USED FOR 990.00 HEADING 'Max.File|Ext.%Used'
select
(select round(sum(bytes/1073741824),2) from dba_data_files) "DataFile(GBs)",
(select NVL(sum(bytes/1048576),0) from dba_temp_files) "Temp Mb",
(select sum(bytes/1048576)*max(members) from v$log) "Redo Mb",
(select count(*) from v$log) "Redo Group",
round(((select sum(bytes/1048576) from dba_data_files) +
(select NVL(sum(bytes/1048576),0) from dba_temp_files) +
(select sum(bytes/1048576)*max(members) from v$log)) / 1024,2) "Tot.DB Size(GBs)",
(select round(to_number(value) / 1024/1024,2) from v$parameter where name = 'sga_max_size') "SGA Size",
(select count(*) from dba_users) "Users",
(select value from v$parameter where name like 'processes') ||
(select TO_CHAR(count(*),99999) from v$session where username is not null) as Processes
from dual;
SELECT * FROM (
SELECT d.tablespace_name tablespace,
ROUND(A.MAX_BYTES,2) MAX_BYTES,
NVL (a.BYTES, 0) / 1024 / 1024 SizeMB,
ROUND (NVL (a.BYTES - NVL (f.BYTES, 0), 0) / 1024 / 1024, 2) UsedMB,
ROUND ((NVL (a.BYTES, 0) - NVL (a.BYTES - NVL (f.BYTES, 0), 0)) / 1024/1024,2) FreeMB,
ROUND (NVL ((a.BYTES - NVL (f.BYTES, 0)) / a.BYTES * 100, 0), 2) pct_used,
ROUND (100 - NVL ((a.BYTES - NVL (f.BYTES, 0)) / a.BYTES * 100, 0), 2) pct_free,
ROUND (100 - ((A.max_bytes - ROUND (NVL (a.BYTES - NVL (f.BYTES, 0), 0) / 1024 / 1024, 2)) / A.max_bytes * 100), 2) MAX_USED
FROM SYS.dba_tablespaces d,
(SELECT tablespace_name, SUM (BYTES) BYTES,
SUM(DECODE(NVL(MAXBYTES,0),0,BYTES,MAXBYTES)) / 1024/1024 MAX_BYTES
FROM dba_data_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM (BYTES) BYTES
FROM dba_free_space
GROUP BY tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
AND d.CONTENTS NOT IN ('TEMPORARY')
-- AND (NVL ((a.BYTES - NVL (f.BYTES, 0)) / a.BYTES * 100, 0) >= 95) -- TBS PCD_USED
ORDER BY 8 desc
);
COL TABLESPACE FOR a25 HEADING 'Tablespace Name'
COL FILE_NAME FOR A65 HEADING 'File Name'
COL AUTOEXTENSIBLE FOR A6 HEADING 'Auto|Extend'
--COL MAX_SIZE FOR 999,990 HEADING 'Maximun |Ext.size'
COL FILE_SIZE FOR 999,990 HEADING 'Data.File|Size(MBs)'
--COL FREE_MBS FOR 999,990 HEADING 'Free.MBS'
BREAK ON TABLESPACE SKIP 0;
SELECT * FROM
(
SELECT A1.TABLESPACE_NAME tablespace,
A1.FILE_NAME,
A1.AUTOEXTENSIBLE,
A1.MAXBYTES / 1024/1024 MAX_BYTES,
A1.BYTES/1024/1024 AS FILE_SIZE,
nvl(B.FREEMB,0) as FREEMB
FROM DBA_DATA_FILES A1,
(SELECT FILE_ID,
SUM(BYTES)/1024/1024 AS FREEMB
FROM DBA_FREE_SPACE
GROUP BY FILE_ID) B
WHERE A1.FILE_ID = B.FILE_ID(+) AND
A1.TABLESPACE_NAME IN
(
SELECT tablespace FROM (
SELECT d.tablespace_name tablespace,
ROUND(A.MAX_BYTES,2) MAX_BYTES,
NVL (a.BYTES, 0) / 1024 / 1024 SizeMB,
ROUND (NVL (a.BYTES - NVL (f.BYTES, 0), 0) / 1024 / 1024, 2) UsedMB,
ROUND ((NVL (a.BYTES, 0) - NVL (a.BYTES - NVL (f.BYTES, 0), 0)) / 1024/1024,2) FreeMB,
ROUND (NVL ((a.BYTES - NVL (f.BYTES, 0)) / a.BYTES * 100, 0), 2) pct_used,
ROUND (100 - NVL ((a.BYTES - NVL (f.BYTES, 0)) / a.BYTES * 100, 0), 2) pct_free,
ROUND (100 - ((A.max_bytes - ROUND (NVL (a.BYTES - NVL (f.BYTES, 0), 0) / 1024 / 1024, 2)) / A.max_bytes * 100), 2) MAX_USED
FROM SYS.dba_tablespaces d,
(SELECT tablespace_name, SUM (BYTES) BYTES, SUM(MAXBYTES) / 1024/1024 MAX_BYTES
FROM dba_data_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM (BYTES) BYTES
FROM dba_free_space
GROUP BY tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
AND d.CONTENTS NOT IN ('TEMPORARY')
-- AND ROUND (100 - ((A.max_bytes - ROUND (NVL (a.BYTES - NVL (f.BYTES, 0), 0) / 1024 / 1024, 2)) / A.max_bytes * 100), 2) > 50
)
))
ORDER BY TABLESPACE,FILE_NAME;
COL TABLESPACE FOR A15 HEADING 'Tablespace Name'
COL FILE_NAME FOR A60 HEADING 'File Name'
col status for a12
select tablespace_name tablespace,file_name,AUTOEXTENSIBLE,MAXBYTES/1024/1024 MAX_BYTES,
bytes/1024/1024 FILE_SIZE,user_bytes/1024/1024 USEDMB,status
from dba_temp_files
order by 1,2;
prompt =====================================
prompt Control Files Info
prompt =====================================
col name for a60
col status for a10
select name,status from v$controlfile;
prompt =====================================
prompt Log Files Info
prompt =====================================
col member for a60
col status for a12
col type for a12
col thread# for 9999
col group# for 9999
SELECT l.thread#,
l.group#,
lf.MEMBER,
l.bytes / 1024 / 1024 Size_MB,
l.status,
lf.TYPE
FROM v$log l, v$logfile lf
WHERE l.group# = lf.group#
order by 1,2,3;
prompt =====================================
prompt DBA Directories
prompt =====================================
col owner for a20
col directory_name for a25
col directory_path for a55
select * from dba_directories order by owner,directory_name;
prompt =====================================
prompt DB Components and Objects status Info
prompt =====================================
col comp_name for a50;
col version for a15;
col status for a15;
col owner for a25
col object_name for a30;
col object_type for a25;
col action_time for a30
col action for a10
col namespace for a10
col version for a12
col bundle_series for a15
col comments for a25
select comp_name,version,status from DBA_REGISTRY order by status,comp_name;
select action_time,action,namespace,version,bundle_series,comments from dba_registry_history order by 1 desc;
-- select action_time,action,namespace,version,comments from dba_registry_history order by 1 desc;
break on owner skip 0;
select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by 1,2;
select owner,object_type,object_name from dba_objects where status='INVALID' order by 1,2,3;
clear break;
prompt =====================================
prompt Datafiles with unrecoverable changes
prompt =====================================
set pages 2000
set lines 150
break on tablespace_name skip 0
col tablespace_name for a20
col file_name for a60
col UNRECOVERABLE_CHANGE# for 99999999999999999
col unrecover_time for a20
select df.tablespace_name,
df.file_name,
to_char(v.unrecoverable_time,'DD-Mon-YY HH24:MI') unrecover_time,
v.unrecoverable_change#,
b.time
from dba_data_files df,
v$datafile v,
v$backup b
where v.file# = df.file_id
and v.file# = b.file#
and v.unrecoverable_change# > 0
and (b.time is null or b.change# < v.unrecoverable_change#)
order by tablespace_name, file_name
/
clear break;
prompt =====================================
prompt Recyclebin Objects count
prompt =====================================
SELECT OWNER,COUNT(*) FROM DBA_RECYCLEBIN GROUP BY owner ORDER BY owner;
select to_char(sysdate,'YYYY-MM-DD') YYYYMMDD, to_char(sysdate,'DD-Mon-YY HH24:MI:SS') Date_Time from dual;
spool off;