Instance info ...
Oracle Instance info ...
set head off verify off echo off pages 1500 linesize 120 feedback off
alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';
select
'INSTANCE_NUMBER.............................................: '|| INSTANCE_NUMBER ,
'INSTANCE_NAME...............................................: '|| INSTANCE_NAME ,
'HOST_NAME...................................................: '|| HOST_NAME ,
'VERSION.....................................................: '|| VERSION ,
'STARTUP_TIME................................................: '|| STARTUP_TIME ,
'STATUS......................................................: '|| STATUS ,
'PARALLEL....................................................: '|| PARALLEL ,
'THREAD#.....................................................: '|| THREAD# ,
'ARCHIVER....................................................: '|| ARCHIVER ,
'LOG_SWITCH_WAIT.............................................: '|| LOG_SWITCH_WAIT ,
'LOGINS......................................................: '|| LOGINS ,
'SHUTDOWN_PENDING............................................: '|| SHUTDOWN_PENDING ,
'DATABASE_STATUS.............................................: '|| DATABASE_STATUS ,
'INSTANCE_ROLE...............................................: '|| INSTANCE_ROLE ,
'ACTIVE_STATE................................................: '|| ACTIVE_STATE ,
'BLOCKED.....................................................: '|| BLOCKED
from Gv$instance;
set head on verify on echo on feedback on
set head off verify off echo off pages 1500 linesize 120 feedback off
alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';
select
'INSTANCE_NUMBER.............................................: '|| INSTANCE_NUMBER ,
'INSTANCE_NAME...............................................: '|| INSTANCE_NAME ,
'HOST_NAME...................................................: '|| HOST_NAME ,
'VERSION.....................................................: '|| VERSION ,
'VERSION_LEGACY..............................................: '|| VERSION_LEGACY ,
'VERSION_FULL................................................: '|| VERSION_FULL ,
'STARTUP_TIME................................................: '|| STARTUP_TIME ,
'STATUS......................................................: '|| STATUS ,
'PARALLEL....................................................: '|| PARALLEL ,
'THREAD#.....................................................: '|| THREAD# ,
'ARCHIVER....................................................: '|| ARCHIVER ,
'LOG_SWITCH_WAIT.............................................: '|| LOG_SWITCH_WAIT ,
'LOGINS......................................................: '|| LOGINS ,
'SHUTDOWN_PENDING............................................: '|| SHUTDOWN_PENDING ,
'DATABASE_STATUS.............................................: '|| DATABASE_STATUS ,
'INSTANCE_ROLE...............................................: '|| INSTANCE_ROLE ,
'ACTIVE_STATE................................................: '|| ACTIVE_STATE ,
'BLOCKED.....................................................: '|| BLOCKED
from Gv$instance;
set head on verify on echo on feedback on
set head off verify off echo off pages 0 linesize 120 feedback off alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS'; select 'Sysdate.....................................................: '|| sysdate , 'INSTANCE_NUMBER.............................................: '|| INSTANCE_NUMBER , 'INSTANCE_NAME...............................................: '|| INSTANCE_NAME , 'HOST_NAME...................................................: '|| HOST_NAME , 'VERSION.....................................................: '|| VERSION , 'STARTUP_TIME................................................: '|| STARTUP_TIME , 'STATUS......................................................: '|| STATUS , 'PARALLEL....................................................: '|| PARALLEL , 'THREAD#.....................................................: '|| THREAD# , 'ARCHIVER....................................................: '|| ARCHIVER , 'LOG_SWITCH_WAIT.............................................: '|| LOG_SWITCH_WAIT , 'LOGINS......................................................: '|| LOGINS , 'SHUTDOWN_PENDING............................................: '|| SHUTDOWN_PENDING , 'DATABASE_STATUS.............................................: '|| DATABASE_STATUS , 'INSTANCE_ROLE...............................................: '|| INSTANCE_ROLE , 'ACTIVE_STATE................................................: '|| ACTIVE_STATE , 'BLOCKED.....................................................: '|| BLOCKED from gv$instance; set head on verify on echo on feedback on
set linesize 500
col PDB_NAME for a15
select name CDB_NAME,(select name from v$pdbs where rownum <2) PDB_NAME,open_mode,database_role,
(SELECT to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') FROM dual) "Current_time_db" ,
(select INSTANCE_NAME from v$instance) INSTANCE_NAME,
(select HOST_NAME from v$instance ) HOST_NAME
from v$database;
Oracle Instance info ... With database Name .
set head off verify off echo off pages 150 linesize 120 feedback off
alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';
select distinct
'DATABASE_NAME..............................................: '|| NAME ,
'INSTANCE NAME..............................................: '|| INSTANCE_NAME ,
'LOG_MODE...................................................: '|| LOG_MODE ,
'OPEN MODE..................................................: '|| OPEN_MODE ,
'INSTANCE_NAME..............................................: '|| INSTANCE_NAME ,
'HOSTNAME...................................................: '|| HOST_NAME ,
'STATUS.....................................................: '|| STATUS ,
'LOGINS.....................................................: '|| LOGINS ,
'STARTUP-TIME...............................................: '|| STARTUP_TIME ,
'DATAGUARD BROKER...........................................: '|| DATAGUARD_BROKER ,
'GUARD STATUS...............................................: '|| GUARD_STATUS ,
'FLASHBACK ON...............................................: '|| FLASHBACK_ON ,
'PROTECTION MODE............................................: '|| PROTECTION_MODE ,
'CONTROLFILE TYPE...........................................: '|| CONTROLFILE_TYPE
from gv$instance i, v$database d
-- where 1=1
-- and i.inst_id=d.inst_id
order by 2;
set head on verify on echo on feedback on
set head off verify off echo off pages 150 linesize 120 feedback off
alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';
select distinct
'DATABASE_NAME..............................................: '|| NAME ,
'INSTANCE NAME..............................................: '|| INSTANCE_NAME ,
'LOG_MODE...................................................: '|| LOG_MODE ,
'OPEN MODE..................................................: '|| OPEN_MODE ,
'INSTANCE_NAME..............................................: '|| INSTANCE_NAME ,
'HOSTNAME...................................................: '|| HOST_NAME ,
'STATUS.....................................................: '|| STATUS ,
'LOGINS.....................................................: '|| LOGINS ,
'Uptime.....................................................: '|| floor(sysdate - startup_time) || ' days(s) ' || trunc( 24*((sysdate-startup_time) - trunc(sysdate-startup_time))) || ' hour(s) ' || mod(trunc(1440*((sysdate - startup_time) - trunc(sysdate-startup_time))), 60) ||' minute(s) ' || mod(trunc(86400*((sysdate-startup_time) - trunc(sysdate-startup_time))), 60) ||' seconds' ,
'STARTUP-TIME...............................................: '|| STARTUP_TIME ,
'DATAGUARD BROKER...........................................: '|| DATAGUARD_BROKER ,
'GUARD STATUS...............................................: '|| GUARD_STATUS ,
'FLASHBACK ON...............................................: '|| FLASHBACK_ON ,
'PROTECTION MODE............................................: '|| PROTECTION_MODE ,
'CONTROLFILE TYPE...........................................: '|| CONTROLFILE_TYPE
from gv$instance i, v$database d
-- where 1=1
-- and i.inst_id=d.inst_id
order by 2;
set head on verify on echo on feedback on
Instance uptime ...
set linesize 200 pagesize 200
column hostname format a30
column "Instance Name" format a16
column "Started At" format a26
column "Database_Uptime" format a50
SELECT
instance_number ,instance_name as "Instance Name",host_name as Hostname,to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') as "Started_At",floor(sysdate - startup_time) || ' days(s) ' ||trunc( 24*((sysdate - startup_time) - trunc(sysdate - startup_time))) || ' hour(s) ' ||
mod(trunc(1440*((sysdate - startup_time) - trunc(sysdate - startup_time))), 60) ||' minute(s) ' ||mod(trunc(86400*((sysdate - startup_time) - trunc(sysdate - startup_time))), 60) ||' seconds' as "Database_Uptime",STATUS
FROM gv$instance;
set pages 0 lines 300 trims on head off feed off ver off
select '# ---------------------------'||chr(10)||
'# -- Instance Information --'||chr(10)||
'# ---------------------------'||chr(10)
from dual;
select 'Host Name : '||host_name||chr(10)||
'Instance ID : '||inst_id||chr(10)||
'Instance Name : '||instance_name||chr(10)||
'Version : '||version||chr(10)||
'Startup Time : '||to_char(startup_time, 'DD-MON-RR HH24:MI:SS')||chr(10)||
'Instance Role : '||instance_role||chr(10)||
'Blocked :' ||blocked
from gv$instance order by inst_id asc
/
======
alter session set nls_date_format='dd-mm-yyyy hh24:mi';
set linesize 300 pagesize 300
col HOST_NAME for a45
select
sysdate
, i.inst_id
, case when i.inst_id=sys_context('userenv', 'instance') then '*' end connected
, i.instance_name
, i.instance_role
, i.host_name
, i.startup_time
, i.status
, i.version
, (select count(*) from gv$session s where s.type='USER' and s.status='ACTIVE' and s.inst_id=i.inst_id) user_sessions
--, round((select m.value from v$sysmetric m where m.metric_id=2147 and m.group_id=2), 2) aas
--, 0 tps
, i.inst_id id_
from gv$instance i
order by i.inst_id
set linesize 300 col BANNER_FULL for a50 wrap col BANNER_LEGACY for a50 wrap select * from gv$version;
alter session set nls_date_format='dd-mm-yyyy hh24:mi';
set line 300 pagesize 100
col inst for 9999
col instname for a12
col host_name for a25
col version for a10
col status for a15
col dbstatus for a10
col db_unique_name for a12
col flashback_on for a20
col instance_role for a18
col wrl_type for a15
col wrl_parameter for a70
select inst_id, instance_name instname,startup_time, host_name, version, status, parallel, thread#, archiver, logins, shutdown_pending, database_status dbstatus, instance_role from gv$instance order by inst_id;
select inst_id, dbid, name, db_unique_name, open_mode, log_mode, flashback_on, switchover_status, database_role,CONTROLFILE_TYPE from gv$database order by inst_id;
select * from gv$encryption_wallet order by inst_id;
set linesize 300 col VALUE for a60 col NAME for a25 select name , value from v$parameter where name in ('encrypt_new_tablespaces','tde_configuration','wallet_root') ;
set linesize 300
col fs_failover_observer_host for a40
select fs_failover_status,fs_failover_observer_present,fs_failover_observer_host from v$database;
set linesize 300
col fs_failover_observer_host for a40
col host format a20
col role for a15
col host for a15
col role for a20
col db_name for a15
col host for a20
select sys_context('userenv' ,'server_host') host,sys_context('userenv', 'db_name') db_name,sys_context('userenv' ,'database_role') role,fs_failover_status,fs_failover_observer_present,fs_failover_observer_host
from v$database;
set linesize 300 pagesize 300
select ts#, encryptionalg, encryptedts, key_version, status from v$encrypted_tablespaces;
col wrl_parameter for a70
select * from v$encryption_wallet;
select * from v$encryption_keys;
select wrl_parameter,status,wallet_type from v$encryption_wallet;
select key_id,keystore_type from v$encryption_keys;
select key_id from v$encryption_keys;
select keystore_type from v$encryption_keys;
select wrl_parameter from v$encryption_wallet;
select status from v$encryption_wallet;
select * from v$encrypted_tablespaces;
select tablespace_name, encrypted from dba_tablespaces;
select * from dba_encrypted_columns;
database info !!!
http://anuj-singh.blogspot.com/2017/11/oracle-database-info-from-vdatabase.html
set pages 50 linesize 300
col open_mode for a23
col maxseq for 9999999
Col role for a20
col database_role for a10
col instance_name for a10
col user for a10
col host_name for a20
col HOST for a20
col version for a10
col started for a15
alter session set NLS_DATE_FORMAT ='MM/DD/YYYY HH24:MI:SS';
select tab1.instance,substr(tab1.host_name,1,15) HOST,tab1.version,
to_char(tab1.startup_time,'Mon-dd-yy:hh24:mm') STARTED,tab1.open_mode,tab1.database_role ROLE,tab1.dbid,tab2.seqn MAXSEQ from
(select a.inst_id,a.host_name, b.open_mode,b.database_role,a.version,a.startup_time,b.dbid,
a.thread# thrd,a.instance_name instance from gv$instance a,gv$database b
where a.inst_id=b.inst_id)
tab1,
(select thread#,max(sequence#) seqn from gv$log_history a
where a.resetlogs_time >= (select max(b.RESETLOGS_TIME) from gv$log_history b) group by thread#)
tab2
where tab1.thrd=tab2.thread#
order by inst_id
/
ps -ef|egrep -i 'smon|mrp0|lsp|FSFP|NSV|NSS|NSA|CTWR'|egrep -v egrep
NSV Performs broker network communications between databases in a Data Guard environment
NSS SYNC transport is configured for a remote standby
NSA standby destinations configured for ASYNC transport
CTWR tracks changed blocks as redo is generated at a primary===================
set linesize 300
col Uptime for a60
col hostname for a40
select SYS_CONTEXT('USERENV','HOST') hostname , inst_id, instance_name, status, startup_time || ' - ' ||
trunc(SYSDATE-(STARTUP_TIME) ) || ' day(s), ' || trunc(24*((SYSDATE-STARTUP_TIME) -
trunc(SYSDATE-STARTUP_TIME)))||' hour(s), ' || mod(trunc(1440*((SYSDATE-STARTUP_TIME) - trunc(SYSDATE-STARTUP_TIME))), 60) ||' minute(s), ' || mod(trunc(86400*((SYSDATE-STARTUP_TIME) - trunc(SYSDATE-STARTUP_TIME))), 60) ||' seconds' "Uptime"
from gv$instance
order by inst_id
/
=====
-- from history
set linesize 300
col instance_name for a15
col startup_time for a27
select * from ( select instance_name,startup_time from dba_hist_database_instance where 1=1 and DBID =(select DBID from v$database) order by startup_time desc) where rownum < 20;
alter session set nls_date_format='dd-mm-yyyy hh24:mi';
set line 300 pagesize 100
col inst for 9999
col instname for a12
col host_name for a25
col version for a10
col status for a15
col dbstatus for a10
col db_unique_name for a12
col flashback_on for a20
col instance_role for a18
col wrl_type for a15
col wrl_parameter for a70
select inst_id, instance_name instname,startup_time, host_name, version, status, parallel, thread#, archiver, logins, shutdown_pending, database_status dbstatus, instance_role from gv$instance order by inst_id;
select inst_id, dbid, name, db_unique_name, open_mode, log_mode, flashback_on, switchover_status, database_role,CONTROLFILE_TYPE from gv$database order by inst_id;
select * from gv$encryption_wallet order by inst_id;
set linesize 300 pagesize 300
col PLATFORM_NAME for a20
col HOST_NAME for a20
col CPU_COUNT for a20
select d.dbid
,d.name dbname
,d.db_unique_name
,d.platform_name
,i.version
,i.inst_id
,i.instance_number
,i.instance_name
,lower(substr(i.host_name||'.', 1, instr(i.host_name||'.', '.') - 1)) host_name
,lpad(ora_hash(lower(substr(i.host_name||'.', 1, instr(i.host_name||'.', '.') - 1)),999999),6,'6') host_hv
,p.value cpu_count
from v$database d
,gv$instance i
,gv$system_parameter2 p
where p.inst_id = i.inst_id
and p.name = 'cpu_count';
with
rac as (select count(*) instances, case count(*) when 1 then 'Single-instance' else count(*)||'-node RAC cluster' end db_type from gv$instance)
,mem as (select sum(value) target from gv$system_parameter2 where name = 'memory_target')
,sga as (select sum(value) target from gv$system_parameter2 where name = 'sga_target')
,pga as (select sum(value) target from gv$system_parameter2 where name = 'pga_aggregate_target')
,db_block as (select value bytes from v$system_parameter2 where name = 'db_block_size')
,db as (select name, platform_name from v$database)
,inst as (select host_name, version db_version from v$instance)
,data as (select sum(bytes) bytes, count(*) files, count(distinct ts#) tablespaces from v$datafile)
,temp as (select sum(bytes) bytes from v$tempfile)
,log as (select sum(bytes) * max(members) bytes from v$log)
,control as (select sum(block_size * file_size_blks) bytes from v$controlfile)
,cell as (select count(distinct cell_name) cnt from v$cell_state)
,core as (select sum(value) cnt from gv$osstat where stat_name = 'NUM_CPU_CORES')
,cpu as (select sum(value) cnt from gv$osstat where stat_name = 'NUM_CPUS')
,pmem as (select sum(value) bytes from gv$osstat where stat_name = 'PHYSICAL_MEMORY_BYTES')
select 'Database name:' system_item
,db.name system_value from db
union all
select 'Oracle Database version:'
,inst.db_version
from inst
union all
select 'Database block size:'
,trim(to_char(db_block.bytes / power(2,10), '90'))||' KB'
from db_block
union all
select 'Database size:'
,trim(to_char(round((data.bytes + temp.bytes + log.bytes + control.bytes) / power(10,12), 3), '999,999,990.000'))||' TB'
from db
,data
,temp
,log
,control
union all
select 'Datafiles:'
,data.files||' (on '||data.tablespaces||' tablespaces)'
from data
union all
select 'Database configuration:'
,rac.db_type from rac
union all
select 'Database memory:'
,case when mem.target > 0 then 'MEMORY '||trim(to_char(round(mem.target / power(2,30), 1), '999,990.0'))||' GB, ' end||
case when sga.target > 0 then 'SGA ' ||trim(to_char(round(sga.target / power(2,30), 1), '999,990.0'))||' GB, ' end||
case when pga.target > 0 then 'PGA ' ||trim(to_char(round(pga.target / power(2,30), 1), '999,990.0'))||' GB, ' end||
case when mem.target > 0 then 'AMM' else case when sga.target > 0 then 'ASMM' else 'MANUAL' end end
from mem, sga, pga
union all
select 'Hardware:'
, case when cell.cnt > 0 then 'Engineered System '||
case when 'AMD FX(tm)-6300 Six-Core Processor' like '%5675%' then 'X2-2 ' end||
case when 'AMD FX(tm)-6300 Six-Core Processor' like '%2690%' then 'X3-2 ' end||
case when 'AMD FX(tm)-6300 Six-Core Processor' like '%2697%' then 'X4-2 ' end||
case when 'AMD FX(tm)-6300 Six-Core Processor' like '%2699%' then 'X5-2 ' end||
case when 'AMD FX(tm)-6300 Six-Core Processor' like '%8870%' then 'X3-8 ' end||
case when 'AMD FX(tm)-6300 Six-Core Processor' like '%8895%' then 'X4-8 or X5-8 ' end||
'with '||cell.cnt||' storage servers'
else 'Unknown' end from cell
union all
select 'Processor:', 'AMD FX(tm)-6300 Six-Core Processor'
from dual
union all
select 'Physical CPUs:'
, core.cnt||' cores'||
case when rac.instances > 0 then ', on '||rac.db_type end
from rac
,core
union all
select 'Oracle CPUs:'
,cpu.cnt||' CPUs (threads)'||case when rac.instances > 0 then ', on '||rac.db_type end
from rac
,cpu
union all
select 'Physical RAM:'
, trim(to_char(round(pmem.bytes / power(2,30), 1), '999,990.0'))||' GB'||case when rac.instances > 0 then ', on '||rac.db_type end
from rac
, pmem
union all
select 'Operating system:'
, db.platform_name
from db;
alter session set nls_date_format='dd-mm-yyyy hh24:mi'; prompt ####################### Execution Time ################################ set linesize 140 set pagesize 9999 select sysdate as current_date from dual; prompt ####################### Database Version ################################ select * from v$version; prompt ####################### Instance Information ################################ set linesize 300 col instance_number for 99999999 col INSTANCE_NAME for a14 col host_name for a12 col status for a8 col STARTUP_TIME for a16 col THREAD# for 999999999 col paraller for a12 select INSTANCE_NUMBER,INSTANCE_NAME,host_name,STATUS,STARTUP_TIME,THREAD#,parallel from Gv$instance; prompt ####################### Database Information ################################ set linesize 300 col "Database Name" for a8 col open_mode for a12 col created for a18 col log_mode for a12 col controlfile_type for a12 col controlfile_created for a18 col controlfile_time for a18 col resetlogs_change# for 999999999999999 col checkpoint_change# for 999999999999999 col dbid for 999999999999999 col controlfile_change# for 999999999999999 col resetlogs_time for a18 select dbid, name "Database Name",open_mode,flashback_on,open_mode, log_mode from v$database; select created ,checkpoint_change# ,controlfile_type,controlfile_created,controlfile_change#,controlfile_time,resetlogs_change#,resetlogs_time from v$database; prompt ####################### SCN Information ################################ col time for a20 col scn for 99999999999999999999999 col Headroom for 999999999999999 SELECT to_char(tim,'yyyy-mm-dd hh24:mi:ss') time,scn,round((chk16kscn-scn)/24/3600/16/1024,1) Headroom FROM ( select tim, scn, (( ((to_number(to_char(tim,'YYYY'))-1988)*12*31*24*60*60) + ((to_number(to_char(tim,'MM'))-1)*31*24*60*60) + (((to_number(to_char(tim,'DD'))-1))*24*60*60) + (to_number(to_char(tim,'HH24'))*60*60) + (to_number(to_char(tim,'MI'))*60) + (to_number(to_char(tim,'SS'))) ) * (16*1024)) chk16kscn from (select sysdate tim,checkpoint_change# scn from v$database)) ORDER BY tim;
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
set linesize 300
col HOST_ADDRESS for a15
col "DB RAC?" for a25
col HOST_NAME for a26
select name INSTANCE,HOST_NAME, UTL_INADDR.GET_HOST_ADDRESS "HOST_ADDRESS",
LOGINS, archiver,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB_UP_TIME", RESETLOGS_TIME "RESET_TIME",
FLOOR(sysdate-startup_time) days,
(select DECODE(vp1.value,'TRUE','Yes ('|| decode(vp1.value,'TRUE',' instances: '||vp2.value)||')','No')
from v$instance,(select value from v$parameter where name like 'cluster_database') vp1,
(select value from v$parameter where name like 'cluster_database_instances') vp2) "DB RAC?" from v$database,gv$instance;
=============
set termout off
column db_inst_num heading "Inst Num" new_value db_inst_num format 99999 noprint;
column db_inst_name heading "Instance" new_value db_inst_name format a12 noprint;
column db_db_name heading "DB Name" new_value db_db_name format a12 noprint;
column db_dbid heading "DB Id" new_value db_dbid noprint;
select d.dbid db_dbid
, d.name db_db_name
, i.instance_number db_inst_num
, i.instance_name db_inst_name
from v$database d,
v$instance i;
set termout on
-- list databases and instances
set underline on;
column DBID heading "DB Id" format a12;
column a_db_name heading "DB Name" format a12;
column a_cont_name heading "AWR Data Source" format a24;
column type heading "Type" format a24;
prompt
prompt
prompt Available Databases and Instances.
prompt The database with * is current database
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select distinct
(case
when a.a_dbid = sys_context('userenv','con_dbid') THEN '* '
else ' '
end) || a.a_dbid DBID,
a.a_db_name, a.a_cont_name,
(case data_source_type
when 'ROOT' THEN
case rac
when 'YES' THEN
case cdb
when 'YES' THEN 'CDB, RAC'
else 'NON_CDB, RAC'
end
else
case cdb
when 'YES' THEN 'CDB'
else 'NON_CDB'
end
end
when 'PDB' THEN
case rac
when 'YES' THEN 'PDB, RAC'
else 'PDB'
end
when 'IMPORTED' THEN
case rac
when 'YES' THEN
case cdb
when 'YES' THEN
case
when (sys.dbms_sqltune_util2.is_imported_pdb(a.a_dbid) = 'yes')
then 'IMPORTED, RAC, PDB'
else
'IMPORTED, RAC, CDB'
end
else 'IMPORTED, RAC'
end
when 'NO' THEN
case cdb
when 'YES' THEN
case
when (sys.dbms_sqltune_util2.is_imported_pdb(a.a_dbid) = 'yes')
then 'IMPORTED, PDB'
else 'IMPORTED, CDB'
end
else 'IMPORTED'
end
else 'IMPORTED'
end
end ) type
from
(
select wr.dbid a_dbid,
wr.db_name a_db_name,
(case wr.cdb
when 'YES' THEN 'CDB$ROOT'
else wr.db_name -- in non_cdb, display db_name
end) a_cont_name,
sys.dbms_sqltune_util2.resolve_database_type(wr.dbid) data_source_type,
wr.parallel rac, wr.cdb cdb, wr.con_id con_id
from awr_root_database_instance wr
UNION ALL
select wr.dbid a_dbid,
wr.db_name a_db_name,
nvl(pi.pdb_name, wr.db_name) data_source,
sys.dbms_sqltune_util2.resolve_database_type(wr.dbid) data_source_type,
wr.parallel rac, wr.cdb cdb, wr.con_id con_id
from awr_pdb_database_instance wr,
awr_pdb_pdb_instance pi
where wr.dbid = pi.con_dbid (+)
and sys_context('userenv', 'con_id') > 2
) a ;
prompt prompt DATABASE Size prompt prompt set linesize 300 col "Database Size" for a20 col "Free space" for a20 col "Used space" for a20 col hostname for a35 col Instance for a15 col DB_unique_name for a15 col Tdate heading 'Date' alter session set nls_date_format='dd-mm-yyyy hh24:mi' ; select sysdate Tdate,SYS_CONTEXT('USERENV','HOST') hostname,sys_context('USERENV', 'INSTANCE_NAME') Instance ,SYS_CONTEXT('USERENV','DB_UNIQUE_NAME') DB_unique_name, round(sum(used.bytes)/1024/1024/1024 ) || ' GB' "Database Size" , round(sum(used.bytes)/1024/1024/1024 ) - round(free.SS/1024/1024/1024) || ' GB' "Used space" , round(free.SS /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) SS from dba_free_space) free group by free.SS /
====
Database size !!!!
http://anuj-singh.blogspot.com/2023/09/size-of-oracle-database.html?zx=72890a8f565d482d
