Oracle Instance info ...

Instance info ... 

Oracle Instance info ... 

Database size !!!!

http://anuj-singh.blogspot.com/2023/09/size-of-oracle-database.html?zx=72890a8f565d482d

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 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 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 100
SELECT con_id, key_id FROM v$encryption_keys;


SELECT KEY_ID 
FROM V$ENCRYPTION_KEYS 
WHERE ACTIVATION_TIME = (SELECT MAX(ACTIVATION_TIME) 
                         FROM V$ENCRYPTION_KEYS
                         WHERE ACTIVATING_DBID = (SELECT DBID FROM V$DATABASE));

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 500

COL startup_time FOR A26;
COL short_host_name FOR A30;
COL platform_name FOR A40;

PRO Database/Instance
PRO ~~~~~~~~~~~~~~~~~
SELECT st.dbid,				
       st.instance_number,	
       st.startup_time,		
       i.version,			
       d.name db_name,
       i.instance_name,
       TRANSLATE(LOWER(SUBSTR(SUBSTR(host_name, 1, decode(INSTR(i.host_name, '.'),0,30,INSTR(i.host_name, '.')) - 1), 1, 30)),
        'abcdefghijklmnopqrstuvwxyz0123456789-_ ''`~!@#$%&*()=+[]{}\|;:",.<>/?'||CHR(0)||CHR(9)||CHR(10)||CHR(13)||CHR(38),
        'abcdefghijklmnopqrstuvwxyz0123456789-_')
        short_host_name,			
        pl.platform_name	
  FROM (SELECT DISTINCT dbid, instance_number, startup_time
        FROM dba_hist_database_instance) st,
       gv$instance i,
       gv$database d,
       (select os||' '||bit platform_name
          from (
            SELECT 1 id, substr(banner,9,instr(banner,':')-9)  os FROM v$version  where banner like 'TNS for%') a Left outer join
            (select 1 id, substr(banner,instr(banner,'bit')-2,5) bit   FROM v$version  where banner like '%bit Pro%') b 
            on a.id=b.id
            ) pl
  WHERE st.instance_number=i.inst_id
  AND   i.inst_id=d.inst_id
  ORDER BY
       dbid,				
       instance_number,	
       startup_time
/

===

fro Web 

https://github.com/fatdba/Oracle-Database-Scripts/blob/main/Admin_General/dbserverdetails.sql

set linesize 300 pagesize 300
 col SYSTEM_ITEM for a30 
 col SYSTEM_VALUE for a60
 
 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),
 pdbs AS (SELECT * FROM v$pdbs), 
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 '    pdb:'||name, 'Open Mode:'||open_mode FROM pdbs -- need 12c flag
  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 'Intel(R) Xeon(R) CPU E5-2640 v3 @ 2.60GHz' LIKE '%5675%' THEN 'X2-2 ' END||
 CASE WHEN 'Intel(R) Xeon(R) CPU E5-2640 v3 @ 2.60GHz' LIKE '%2690%' THEN 'X3-2 ' END||
 CASE WHEN 'Intel(R) Xeon(R) CPU E5-2640 v3 @ 2.60GHz' LIKE '%2697%' THEN 'X4-2 ' END||
 CASE WHEN 'Intel(R) Xeon(R) CPU E5-2640 v3 @ 2.60GHz' LIKE '%2699%' THEN 'X5-2 or X-6 ' END||
 CASE WHEN 'Intel(R) Xeon(R) CPU E5-2640 v3 @ 2.60GHz' LIKE '%8160%' THEN 'X7-2 ' END||
 CASE WHEN 'Intel(R) Xeon(R) CPU E5-2640 v3 @ 2.60GHz' LIKE '%8870%' THEN 'X3-8 ' END||
 CASE WHEN 'Intel(R) Xeon(R) CPU E5-2640 v3 @ 2.60GHz' LIKE '%8895%' THEN 'X4-8 or X5-8 ' END||
 'with '||cell.cnt||' storage servers'
 ELSE 'Unknown' END FROM cell
  UNION ALL
SELECT 'Processor:', 'Intel(R) Xeon(R) CPU E5-2640 v3 @ 2.60GHz' 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
;

===================

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 a37
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 /*+ parallel(8) */  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",LOG_MODE,DATABASE_ROLE
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,(select LOG_MODE,DATABASE_ROLE from v$database)
group by free.SS,LOG_MODE,DATABASE_ROLE
/

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,
dbms_xplan.FORMAT_SIZE(sum(used.bytes)) 			"Database Size" , 
dbms_xplan.FORMAT_SIZE(sum(used.bytes) - free.SS ) 		"Used space"
, dbms_xplan.FORMAT_SIZE(free.SS )  				"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