Name	Password
`sys`	`change_on_install`
`system`	`manager`
`orcladmin`	`welcome`

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$

Oracle How Tos

Tuesday, April 26, 2011

How to find your Oracle SID

Some default passwords Oracle DB !!

Friday, February 25, 2011

Oracle DB Quick Reference

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.