Oracle DB Quick Reference

Setting Oracle Environment :


#export ORACLE_BASE=/usr/lib/oracle/xe/app/oracle  
#export ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/
#export ORACLE_SID=XE
#export PATH=$PATH:$ORACLE_HOME/bin


ORACLE_BASE:  You can use to install the various Oracle Software Products and the same Oracle base directory can be used for more than one installation.


Log in :
SQL> connect sys as sysdba;
Enter password:
Connected.


Start/Stop a DB;

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.


SQL> startup
ORACLE instance started.

Total System Global Area  599785472 bytes
Fixed Size            1260268 bytes
Variable Size          163579156 bytes
Database Buffers      432013312 bytes
Redo Buffers            2932736 bytes
Database mounted.
Database opened.
SQL>


How to check complete version of oracle :




SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0    Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL>

Create a schema:

A schema is the set of objects (tables, views, indexes, etc) that belongs to an user account.

Create a Table Space:

SQL> create tablespace sriram_ts datafile '/home/sriram/Oracle_db/sriram_tablespace.dbf' size 300M;

Tablespace created.

SQL>


Create a Temporary Table Space:

SQL> create temporary tablespace sriram_temp_ts TEMPFILE '/home/sriram/Oracle_db/sriram_temp_tablespace.dbf' size 100M;

Tablespace created.

SQL>

Create a DB user :

 SQL> CREATE USER sriram IDENTIFIED BY sriram321 DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;

User created.

Note: a temporary tablespace has tempfiles, not datafiles.


Current user logged in


SQL> show user;
USER is "SYS"
SQL>


To show all users on Oracle DB:


SQL> select * from all_users;

Better :
SQL>SELECT username FROM all_users ORDER BY username; 

To check which version of DB is this :



SQL> SELECT * FROM global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
XE

SQL>


To List all Tablespace that are created :



SQL> select name from v$tablespace;

NAME
--------------------------------------------------------------------------------
SYSTEM
UNDO
SYSAUX
USERS
TEMP
SRIRAM_TS
SRIRAM_TEMP_TS

7 rows selected.

SQL>

To List Free Space for all Table Space:


SQL> select * from dba_free_space;

To List Free Space for specific Table Space:

SQL> select * from dba_free_space where TABLESPACE_NAME ='USERS';

TABLESPACE_NAME

--------------------------------------------------------------------------------

   FILE_ID   BLOCK_ID   BYTES     BLOCKS RELATIVE_FNO

---------- ---------- ---------- ---------- ------------

USERS

4  209  103153664      12592       4

SQL> 

How To Display SGA (System Global Area) DATA ?



SQL> show SGA;

Total System Global Area  599785472 bytes
Fixed Size    1260268 bytes
Variable Size  163579156 bytes
Database Buffers  432013312 bytes
Redo Buffers    2932736 bytes
SQL> 

The System Global Area (SGA) is a group of shared memory areas that are dedicated to an Oracle “instance” (an instance is your database programs and RAM). In Oracle Database 10g you only need to define two parameters (sga_target andsga_max_size) to configure your SGA. If these parameters are configured, Oracle will calculate how much memory to allocate to the different areas of the SGA using a feature called Automatic Memory Management (AMM). As you gain experience you may want to manually allocate memory to each individual area of the SGA with the initialization parameters.

How to check what is your ORACLE_SID if you are logged in ?


SQL> show parameter db_name;


NAME     TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
db_name     string
XE
SQL> 

What is the Purpose of ORACLE_SID ?

The Oracle System ID (SID) is used to uniquely identify a particular database on a system. For this reason, one cannot have more than one database with the same SID on a computer system.

You can have multiple database instance on same system with different ORACLE_SID, so same database name may not clash.

SID is defined in file :

#cd /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin



#cat /etc/oratab 
XE:/usr/lib/oracle/xe/app/oracle/product/10.2.0/server:N
#


#cd /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/admin/ tnsnames.ora






Where is TNS Listener Port Defined ?


By Default Oracle DB listens on 1521 Port, but you can change it

#cat  /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/admin/listener.ora


To check what are the System Privileges  available in oracle db: ?
SQL>select * from system_privilege_map;

Granting admin Priviledge to User "sriram": ?

SQL>grant sysdba to sriram;



Oracle v$ View List : ?

Oracle creates the v$ views from the x$ structures.  The actual v$ view create view definitions are located in $ORACLE_HOME/rdbms/admin directory so you can see how the v$ views are built on the x$ structures.

#cd /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/rdbms/admin$