The Oracle Database is not thin air. There is a physical layer. The database is somewhere, let' s take a look where files are located on disk.
With this one I list where the data files are located, how big the actual file is, whether the file is auto extensible and what the maximum file size is about.
SQL> set pagesize 9999
SQL> set linesize 300
SQL> column file_name format a50
SQL> column tablespace_name format a25
SQL> select file_id,file_name,tablespace_name,round(bytes/(1024*1024),2) "ACTUAL SIZE",autoextensible "AUTO",round(maxbytes/(1024*1024),2) "MAX SIZE" from dba_data_files order by tablespace_name;
FILE_ID FILE_NAME TABLESPACE_NAME ACTUAL SIZE AUT MAX SIZE
---------------- -------------------------------------------------- ------------------------- ----------------
5 /opt/oradata/SILVER/user_data_01.dbf USER_DATA 8192 YES 7168
2 /opt/oradata/SILVER/sysaux01.dbf SYSAUX 700 YES 32767.98
1 /opt/oradata/SILVER/system01.dbf SYSTEM 710 YES 32767.98
6 /opt/oradata/SILVER/tuning_01.dbf TS_TUNING 100 NO 0
7 /opt/oradata/SILVER/ts_user_1.dbf TS_USER_1 100 NO 0
8 /opt/oradata/SILVER/ts_user_2.dbf TS_USER_2 100 NO 0
3 /opt/oradata/SILVER/undotbs01.dbf UNDOTBS1 1704 YES 2048
4 /opt/oradata/SILVER/users01.dbf USERS 4086 YES 8192
8 rows selected.
Note we can also query v$datafile in order to retrieve similar information.
With this one I list where the temp files are located, how big the actual file is, whether the file is auto extensible and what the maximum file size is about.
SQL> select file_id,file_name,tablespace_name,round(bytes/(1024*1024),2) "ACTUAL SIZE",autoextensible "AUTO",round(maxbytes/(1024*1024),2) "MAX SIZE" from dba_temp_files order by tablespace_name;
FILE_ID FILE_NAME TABLESPACE_NAME ACTUAL SIZE AUT MAX SIZE
---------------- -------------------------------------------------- ------------------------- ----------------
1 /opt/oradata/SILVER/temp01.dbf TEMP 1024 NO 0
Note we can also query v$tempfile in order to retrieve similar information.
Every Oracle database has at least 2 redo log groups. Every group has at least 1 member. With this one I list where the redo log files are located.
Note redo log files can be online or standby. Standby redo logs are only to be used for Data Guard purposes.
SQL> column member format a30
SQL> select group#,member,type from v$logfile;
GROUP# MEMBER TYPE
---------------- ------------------------------ -------
1 /opt/oradata/SILVER/redo01.log ONLINE
2 /opt/oradata/SILVER/redo02.log ONLINE
3 /opt/oradata/SILVER/redo03.log ONLINE
Every Oracle Database has at least 1 control file. Preferrable more than 1.
With this one I list where the controlfile are located.
SQL> column name format a50
SQL> select * from v$controlfile;
STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS
------- -------------------------------------------------- --- ---------------- ----------------
/opt/oradata/SILVER/control01.ctl NO 16384 608
/opt/flash_recovery_area/SILVER/control02.ctl NO 16384 608
Where the controlfiles of a database are can also be found in the server parameter file also the spfile called.
With this one I list whether a server parameter file is used for the startup of the current database instance and if so where the server parameter file is located.
By default the server parameter file is located in the $ORACLE_HOME/dbs directory on Unix systems or in the %ORACLE_HOME%\database directory on Windows systems.
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
spfile string /opt/oracle/product/11.2.0/dbhome_1/dbs/spfileSILVER.ora
If we want that the database can be accessed with the SYSDBA or the SYSOPER privilege from a remote destinatiion. Not on the database server itself we cannot use OS authentication but we use password file authentication.
By default the password file is located in the $ORACLE_HOME/dbs directory on Unix systems or in the %ORACLE_HOME%\database directory on Windows systems.
The name of the password file is something like PWD$INSTANCE_NAME
With this one we can query who is able to connect as sysdba or as sysoper
SQL> select * from v$pwfile_users;
USERNAME SYSDBA SYSOPER SYSASM
------------------------------ ------ ------- ------
SYS TRUE TRUE FALSE
In order to be able to access the database with password file authentication the instance initialization parameter remote_login_passwordfile must have been set to exclusive. ( which is the default )
SQL> show parameter remote_login_passwordfile
NAME TYPE VALUE
remote_login_passwordfile string EXCLUSIVE
If we think about a database we think about tables, indexes, stored procedures, packages, functions, triggers, sequences.
Tables, indexes and lob segments are at the physical layer segments stored in tablespaces.
A tablespace of which the contents are UNDO or PERMANENT has at least 1 but can span multiple data files.
A tablespace of which the contents are TEMPORARY has at least 1 but can span multiple temp files.
With this one we list the tablespaces as well their content
SQL> select tablespace_name,contents from dba_tablespaces;
TABLESPACE_NAME CONTENTS
------------------------- ---------
SYSTEM PERMANENT
SYSAUX PERMANENT
UNDOTBS1 UNDO
TEMP TEMPORARY
USERS PERMANENT
USER_DATA PERMANENT
TS_TUNING PERMANENT
TS_USER_1 PERMANENT
TS_USER_2 PERMANENT
9 rows selected.
Note we can also query v$tablespace in order to retrieve similar information.
Sometimes we wonder whether a tablespace is almost full, whether we have to add a datafile or not. You may want to use my used space script for this purpose.
Information related to tables can be retrieved from the views DBA_TABLES, ALL_TABLES or USER_TABLES.
Information related to indexes can be retrieved from the views DBA_INDEXES, ALL_INDEXES or USER_INDEXES.
Information related to triggers can be retrieved from the views DBA_TRIGGERS, ALL_TRIGGERS or USER_TRIGGERS.
Information related to sequences can be retrieved from the views DBA_SEQUENCES, ALL_SEQUENCES or USER_SEQUENCES.
Information related to synonyms can be retrieved from the views DBA_SYNONYMS, ALL_SYNONYMS or USER_SYNONYMS.
Information related to pl/sql code can be retrieved from the views DBA_SOURCE, ALL_SOURCE or USER_SOURCE.
Information related to views can be retrieved from the views DBA_VIEWS, ALL_VIEWS or USER_VIEWS
Off course there are other objects like database links, user types, ... The views DBA_OBJECTS, ALL_OBJECTS, USERS_OBJECTS can be queried.