How to view storage configuration of Oracle database?

OracleSoftware & CodingProgramming

Problem:

You want to find out some indroductory information about the database.

Solution:

Every Oracle programmer/ DBA has at one point or another during their career inherited a database someone else have already set up.You needs to find out some introductory information about the database to learn more about it.

Idenitfy the host details and databsae version

Example

SELECT instance_name,host_name,version,startup_time FROM v$instance

Output

INSTANCE_NAME    HOST_NAME                      VERSION           STARTUP_TIME TESTDB          ip-000-00-0-000                19.0.0.0.0        12/AUG/20

Next, We will determine the tablespaces that make up the database

Example

SELECT tablespace_name FROM dba_tablespaces ORDER BY tablespace_name;

Output

TABLESPACE_NAME ------------------------------ RDSADMIN SYSAUX SYSTEM TEAM_DATA TEAM_TEMP TEAM_UNDO TEMP UNDOT1 USERS

Next up, we will find about where the tablespaces data files reside on disk storage. In addition to the data files, we will also know where the temp files, control files, and online redo logs are located. All of this information is stored in different data dictionary views, but we can perform a UNION operation to put it all together.

Example

SELECT 'DATA' as type,file_name,bytes FROM dba_data_files UNION ALL SELECT 'TEMP',file_name,bytes FROM dba_temp_files UNION ALL SELECT 'REDO',lf.member,l.bytes FROM v$logfile lf join v$log l on lf.group#=l.group# UNION ALL SELECT 'CTL',value,NULL FROM v$parameter2 where name='control_files';

TYPE
FILE_NAME
BYTES
DATA
/dbdata/db1/TES/datafile/o1_mf_system_hbl2yz2b_.dbf
629145600
DATA
/dbdata/db1/TES/datafile/o1_mf_sysaux_hbl2z3b9_.dbf
2918580224
DATA
/dbdata/db1/TES/datafile/o1_mf_undo_t1_hbl2z6f0_.dbf
1038090240
DATA
/dbdata/db1/TES/datafile/o1_mf_users_hbl2z70m_.dbf
15714156544
DATA
/dbdata/db1/TES/datafile/o1_mf_admin_hbl4792q_.dbf
7340032
DATA
/dbdata/db1/TES/datafile/o1_mf_team_htpjyqk7_.dbf
1073741824
DATA
/dbdata/db1/TES/datafile/o1_mf_team_htpk0o95_.dbf
1073741824
TEMP
/dbdata/db1/TES/datafile/o1_mf_team_htpjyqbz_.tmp
1073741824
TEMP
/dbdata/db1/TES/datafile/o1_mf_temp_hh2nl8c4_.tmp
4823449600
REDO
/dbdata/db1/TES/onlinelog/o1_mf_4_hh2ng9p5_.log
134217728
REDO
/dbdata/db1/TES/onlinelog/o1_mf_3_hh2ng8rl_.log
134217728
REDO
/dbdata/db1/TES/onlinelog/o1_mf_2_hh2ng875_.log
134217728
REDO
/dbdata/db1/TES/onlinelog/o1_mf_1_hh2ng7o5_.log
134217728
CTL
/dbdata/db1/TES/controlfile/control-01.ctl
 

We will look at the tablespace storage with below SQL.

Example

SELECT f.tablespace_name,   TO_CHAR(f.bytes,'99,999,999,999,999')                        AS allocated_bytes,   NVL(TO_CHAR(se.bytes,'99,999,999,999,999'),LPAD('Empty',19)) AS used_bytes,   TO_CHAR(NVL(TRUNC((se.bytes/f.bytes)*100,2),0),'990.00')     AS percent_used FROM   (SELECT df.tablespace_name,     SUM(bytes) AS bytes   FROM dba_data_files df   GROUP BY df.tablespace_name   ) f,   (SELECT s.tablespace_name,     SUM(bytes) AS bytes   FROM dba_segments s   GROUP BY s.tablespace_name   ) se WHERE f.tablespace_name=se.tablespace_name (+) ORDER BY f.tablespace_name;

Output

ADMIN                 7,340,032             131,072     1.78
SYSAUX            2,918,580,224       2,640,117,760    90.45
SYSTEM              629,145,600         565,772,288    89.92
TEAM_DATA             1,073,741,824               Empty     0.00
TEAM_UNDO             1,073,741,824           1,310,720     0.12
UNDOT1            1,038,090,240          74,186,752     7.14
USERS            15,714,156,544      10,962,141,184    69.75

Finally, we will idenitfy the users - oracle created users and non oracle created users.

SQL to list non-oracle users

SELECT username,   account_status,   profile AS security_profile FROM dba_users WHERE oracle_maintained='N' ORDER BY username;

SQL to learn about the profiles

SELECT resource_name,limit FROM dba_profiles WHERE profile='DEFAULT';

Output

CPU_PER_SESSION           UNLIMITED CPU_PER_CALL                   UNLIMITED LOGICAL_READS_PER_SESSION   UNLIMITED LOGICAL_READS_PER_CALL       UNLIMITED IDLE_TIME                       UNLIMITED CONNECT_TIME                   UNLIMITED PRIVATE_SGA                UNLIMITED FAILED_LOGIN_ATTEMPTS        UNLIMITED PASSWORD_LIFE_TIME            UNLIMITED PASSWORD_REUSE_TIME           UNLIMITED PASSWORD_REUSE_MAX            UNLIMITED PASSWORD_VERIFY_FUNCTION         NULL PASSWORD_LOCK_TIME            1 PASSWORD_GRACE_TIME           7 INACTIVE_ACCOUNT_TIME        UNLIMITED

raja
Published on 04-Dec-2020 10:59:35
Advertisements