Oracle: My Simple Oracle Notes on Checking Oracle DB setup

Login to Oracle Linux
Please use "oracle" user

Check Linux free memory

free

Please see the figure on line 2 instead of line 1 because Linux will use almost all free memory for buffers/cache.  The second line deduce them from the figures.

How to connect to Oracle by sqlplus in text mode
1. set ORACLE_HOME parameter
3. set ORACLE_SID
4. run sqlplus "/ as sysdba"


we need to set ORACLE_HOME and ORACLE_SID environment variable before run sqlplus

ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export ORACLE_HOME

PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
export PATH

ORACLE_SID=MYDB
export ORACLE_SID

Then we can launch sqlplus

sqlplus "/ as sysdba"

If the SID is correct, we will connect to the database.  If the SID is incorrect, we will be prompt that we connected to an idle instance.


Check SGA parameters

show parameters sga

Check SGA memory size

show sga

Check Tablespace size and usage

SELECT /* + RULE */  df.tablespace_name "Tablespace",
       df.bytes / (1024 * 1024) "Size (MB)",
       SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
  FROM dba_free_space fs,
       (SELECT tablespace_name,SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
       fs.bytes / (1024 * 1024),
       SUM(df.bytes_free) / (1024 * 1024),
       Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
       Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
  FROM dba_temp_files fs,
       (SELECT tablespace_name,bytes_free,bytes_used
          FROM v$temp_space_header
         GROUP BY tablespace_name,bytes_free,bytes_used) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
 ORDER BY 4 DESC;

Check Oracle Database Version

select * from v$version

Favourite Oracle Sites
Rob's Oracle Blog
toddler DBA