Oracle Database Architecture Diagram

• USER PROCESSES
• ORACLE NET SERVICES
• LISTENER.ORA
• TNSNAMES.ORA
• SQLNET.ORA

SYSTEM GLOBAL AREA
a) LIBRARY CACHE
b) SHARED POOL
c) DICTIONARY CACHE
d) SHARED SQL AREA
e) ENQUEUES
f) LATCHES
g) RESULT CACHE
h) OTHER
i) FIXED AREA

DATABASE BUFFER CACHE

• db_recycle_cache_size
• db_keep_cache_size

REDO LOG BUFFER
LARGE POOL
JAVA POOL
a) SORT EXTENT POOL
b) STREAMS POOL
c) FLASHBACK MEMORY

ORACLE BACKGROUND PROCESSES
ARCH(Archiver)
CJQ(Job Queue Process)
CKPT(Checkpoint)
DBWR(Database Writer or Dirty Buffer Writer)
LGWR(Log Writer)
LMON(Lock Monitor)
MMON(Memory Monitor)
MMAN(Memory Manager)
PMON(Process Monitor)
SMON(System Monitor)
DMON(Dataguard Monitor)
SNP(Snapshot Process)
MRP(Managed recovery process)
RFS(Remote File Server process)
QMN(Queue Monitor Process)
DIAG(Diagnosability Daemon)
LCKx(Global Cache Service Processes)
LMON(Global Enqueue Service Monitor)
LMDx(Global Enqueue Service Daemon )
LMSx(Global Cache Service Processes)

USER PROCESSES
When a user runs an application program (such as a Pro*C program) or an Oracle tool (such as
Oracle Enterprise Manager or SQL*Plus), Oracle Database creates a user process to run the user's
application. Could be client connection or a DB HTTP server request.

ORACLE NET SERVICES
Supports network communication between a client application and a remote or local database
running on a variety of operating systems.
Oracle Net Services allows the database servers and the client applications (or servers acting as
clients) that access it to run on separate machines, and provides a means for moving data between
the nodes on a network.
Oracle Net Services is also used for Inter Process Communication if clients and servers are running
on the same machine.

LISTENER.ORA
is a SQL*Net configuration file used to configure Oracle Database Listeners (required to accept
remote connection requests).
This file normally resides in the ORACLE HOME\NETWORK\ADMIN directory or in the directory
define by the $TNS_ADMIN variable.

TNSNAMES.ORA
is a SQL*Net configuration file that defines databases addresses for establishing connections to
them. This file normally resides in the ORACLE HOME\NETWORK\ADMIN directory.

SQLNET.ORA
is a text file that provides SQL*Net with basic configuration details like tracing options, default
domain, encryption, etc. This file can be found in the ORACLE HOME\NETWORK\ADMIN directory.

SYSTEM GLOBAL AREA - System Global Area (SGA) is a group of shared memory areas that are dedicated to an Oracle “instance”. It consists of

1. Shared Pool
2. Database Buffer Cache
3. Redo Log buffer
4. Large Pool
5. Java Pool

SHARED POOL

is a RAM area within the RAM heap that is created at startup time. This is
Since it is not possible to dedicate separate regions of memory for the shared pool components, the
shared pool is usually the second-largest SGA memory area (depending on the size of the
db_cache_size parameter).
All of the sub-areas in shared pool are controlled by the single shared_pool_size parameter.
The shared pool is like a buffer for SQL statements. Oracle's parsing algorithm ensures that identical
SQL statements do not have to be parsed each time they're executed. The shared pool is used to
store SQL statements.

The shared pool contains RAM memory regions that serve the following purposes:
LIBRARY CACHE
Responsible for collecting, parsing, interpreting, and executing all of the SQL statements that go
against the Oracle database. Contains the current SQL execution plan information. It also holds
stored procedures and trigger code.
SHARED SQL AREA -
The shared SQL area stores each SQL statement executed in the database. This area allows SQL
execution plans to be reused by many users.
DICTIONARY CACHE
The dictionary cache stores environmental information, which includes referential integrity, table
definitions, indexing information, and other metadata stored within Oracle's internal tables.
RESULT CACHE
is an area in the shared pool and contains the end results of a query execution.
LATCHES
Background process needs one of the data structure to satisfy its purpose, it acquires a latch while it
manipulates or looks for a shared resource.
Latches are simple types of a lock that can be very quickly acquired and freed, low-level serialization
mechanisms to protect shared data structures in the system global area (SGA). For example, latches
protect the list of users currently accessing the database and protect the data structures describing
the blocks in the buffer cache.
A server or background process acquires a latch for a very short time while manipulating or looking
at one of these structures. The implementation of latches is operating system dependent,
particularly in regard to whether and how long a process will wait for a latch.


Advantages of Latches

• It can be very quickly acquired and freed.
• There is a cleanup procedure that will be called if process dies while holding a latch.
• Synchronization of levels of latching - Process acquires a latch at a certain level and it cannot acquire another latch subsequently that is equal to or less than that level.
•  Prevents more than one process from executing the same piece of code at a given time.
• Latches have an associated level that is used to prevent deadlocks.

Limitations

• Low-level of serialization (one at a time)

• The implementation of latches is operating system dependent, particularly in regard to

whether and how long a process will wait for a latch.

• A server or background process acquires a latch for a very short time while manipulating or

looking at one of these structures. If it acquires a latch for more time, there is a possibility
process may die. There is no ordered queue of waiters like in enqueues. Latch waiters may
either use timers to wakeup and retry or spin (only in multiprocessors) . Since all waiters are
concurrently retrying (depending on the scheduler), anyone might get the latch and
conceivably the first one to try might be the last one to get.


ENQUEUES
Enqueues are another type of locking mechanism used in Oracle. Any object (Not only data
structures) which can be concurrently(non serially) used, can be protected with enqueues.
A good example is of locks on tables. We allow varying levels of sharing on tables e.g. two processes
can lock a table in share mode(Read) or in share update mode(Read and Write) etc.
Enqueue is obtained using an OS specific(not dependent) locking mechanism. An enqueue allows the
user to store a value in the lock, i.e the mode in which we are requesting it.
The OS lock manager keeps track of the resources locked. If a process cannot be granted the lock
because it is incompatible with the mode requested and the lock is requested with wait, the OS puts
the requesting process on a wait queue which is serviced in FIFO.

Advantages

• Allows several concurrent processes to have varying degree of known resources.

• Enqueue is obtained using an OS specific(not dependent) locking mechanism. If a process

cannot be granted lock then it puts the process in wait queue.

•  There is ordered queue of waiters.

Differences between Latches and Enqueues
Latches - Low level of serialization. Enqueue - Concurrency
Latches - Process acquires latch for a short time and there is no ordered queue of waiters. Enqueue -
Obtained using OS Specific locking mechanism, there are waiters.

FIXED AREA
contains several thousand atomic variables. These are small data structures, such as latches and pointers,
which refer to other areas of the SGA. The size of the fixed area is static. It also contains general information
about the state of the database and the instance which the background processes need to access.
OTHER
The following table lists other different areas stored in the shared pool and their purpose:
* PRIVATE SQL AREA - Private SQL areas are non-shared memory areas assigned to unique user
sessions.
* PL/SQL AREA - Used to hold parsed and compiled PL/SQL program units, allowing the execution
plans to be shared by many users.
* CONTROL STRUCTURES - Common control structure information, for example, lock information
DATABASE BUFFER CACHE
The SGA is used to store incoming data (the data buffers as defined by the db_cache_size
parameter), and internal control information that is needed by the database. The amount of
memory to be allocated to the SGA include db_cache_size, shared_pool_size and log_buffer.
When Oracle receives a request to retrieve data, it will first check the internal memory structures to
see if the data is already in the buffer.
Following are its Tasks

• Storage for data blocks that have been retrieved from data files.

• Provides optimization boost for DML operations (UPDATES)

• Managed via the LRU algorithm

db_keep_cache_size - Segments that contain frequently accessed blocks should be assigned to the
keep buffer pool so that the blocks of those segments will not be inadvertently removed, thus
impacting performance.
db_recycle_cache_size - Any segments whose blocks tend to be accessed with less frequency should
be assigned to the recycle pool so that it does not flush the other segments, either in the default
cache or the keep pool.


REDO LOG BUFFER
The redo log buffer is a RAM area (defined by the initialization parameter log_buffer) that works to
save changes to data, in case something fails and Oracle has to put it back into its original state .
When Oracle SQL updates a table (a process called Data Manipulation Language, or DML), redo
images are created and stored in the redo log buffer

• Serves for assistance with database recovery tasks

• Records all changes made to database blocks

• Places changes recorded to redo entries for redo logs

Oracle will eventually flush the redo log buffer to disk, only after a commit operation occurs.

LARGE POOL
This area is only used if shared server architecture, also called multi -threaded server (MTS), is used, or if parallel query is utilized. The large pool holds the user global areas when MTS is used and holds the parallel query execution message queues for parallel query
The large pool is optional memory component and it provides RAM for

•  UGA holds session based information for the a shared server

•  Oracle XA Interface is involved in case of distributed transactions

•  I/O Server Processes

• Parallel Query Buffers

• Oracle Backup and Restore Operations using RMAN.

JAVA POOL
Caching parsed Java programs, used for java objects, Java methods and other java execution memory.
java_pool_size parameter controls the amount of memory for this area.
The JAVA Pool holds the JAVA execution code in a similar manner to the PL/SQL cache in the shared pool. The JAVA pool is used by many internal routines, such as import and export, and should be sized at approximately 60 megabytes if no other JAVA will be utilized in the user applications.

SORT EXTENT POOL
The SEP tracks extents either in the shared pool area, ie. the UGA in MTS instances and in the PGA
user's process area in non-MTS instances. Thus the SEP is always in the SGA since it tracks sort
extents but the actual sort extents themselves are in several areas.

STREAMS POOL
Cache Oracle Streams objects.
Oracle streams allows data propagation between Oracle Databases (homogeneous) and between
Oracle and non-Oracle databases (heterogeneous environment).
Oracle Streams can be used for:

• Replication

• Message Queuing

•  Loading data into a Data Warehouse

•  Event Notification

•  Data Protection

FLASHBACK MEMORY
Flashback lets you view past states of database objects or to return database objects to a previous
state without using point-in-time media recovery
The flashback memory buffer area is used with the flashback features which were first introduced in
Oracle 10g such as the ability to perform flashback transaction query(retrieves data from a past
point in time), flashback table(recovers a table to its state at a past point in time, without having to
perform a point in time recovery), flashback database(restore the whole database back to a point in
time) and flashback versions(shows you different versions of data rows, plus start and end times of a
particular transaction that created that row query), flashback drop( allows you to reverse the effects
of a drop table statement, without resorting to a point-in-time recovery).
There are a number of flashback levels

•  row level - flashback query, flashback versions query, flashback transaction query

• table level -flashback table, flashback drop

• database level - flashback database

ORACLE BACKGROUND PROCESS
select * from v$session where type ='BACKGROUND';
Here are some of the most important Oracle background processes:

ARCH - (Optional) Archive process writes filled redo logs to the archive log location(s). In RAC, the
various ARCH processes can be utilized to ensure that copies of the archived redo logs for each
instance are available to the other instances in the RAC setup should they be needed for recovery.

CJQ - Job Queue Process (CJQ) - Used for the job scheduler. The job scheduler includes a main
program (the coordinator) and slave programs that the coordinator executes. The parameter
job_queue_processes controls how many parallel job scheduler jobs can be executed at one time.

CKPT - Checkpoint process writes checkpoint information to control files and data file headers.

CQJ0 - Job queue controller process wakes up periodically and checks the job log. If a job is due, it
spawns Jnnnn processes to handle jobs.

DBWR - Database Writer or Dirty Buffer Writer process is responsible for writing dirty buffers from
the database block cache to the database data files. Generally, DBWR only writes blocks back to the
data files on commit, or when the cache is full and space has to be made for more blocks. The
possible multiple DBWR processes in RAC must be coordinated through the locking and global cache processes to ensure efficient processing is accomplished.

FMON - The database communicates with the mapping libraries provided by storage vendors
through an external non-Oracle Database process that is spawned by a background process called
FMON. FMON is responsible for managing the mapping information. When you specify the
FILE_MAPPING initialization parameter for mapping data files to physical devices on a storage
subsystem, then the FMON process is spawned.

LGWR - Log Writer process is responsible for writing the log buffers out to the redo logs. In RAC,
each RAC instance has its own LGWR process that maintains that instance’s thread of redo logs.

LMON - Lock Manager process

MMON - The Oracle 10g background process to collect statistics for the Automatic Workload
Repository (AWR).

MMNL - This process performs frequent and lightweight manageability-related tasks, such as session history capture and metrics computation.

MMAN - is used for internal database tasks that manage the automatic shared memory. MMAN
serves as the SGA Memory Broker and coordinates the sizing of the memory components.

PMON - Process Monitor process recovers failed process resources. If MTS (also called Shared Server Architecture) is being utilized, PMON monitors and restarts any failed dispatcher or server
processes. In RAC, PMON’s role as service registration agent is particularly important.

Pnnn - (Optional) Parallel Query Slaves are started and stopped as needed to participate in parallel
query operations.

RBAL - This process coordinates rebalance activity for disk groups in an Automatic Storage
Management instance.

SMON - System Monitor process recovers after instance failure and monitors temporary segments
and extents. SMON in a non-failed instance can also perform failed instance recovery for other failed
RAC instance.

WMON - The "wakeup" monitor process

Data Guard/Streams/replication Background processes
DMON - The Data Guard Broker process.

SNP - The snapshot process.

MRP - Managed recovery process - For Data Guard, the background process that applies archived
redo log to the standby database.

ORBn - performs the actual rebalance data extent movements in an Automatic Storage Management
instance. There can be many of these at a time, called ORB0, ORB1, and so forth.

OSMB - is present in a database instance using an Automatic Storage Management disk group. It
communicates with the Automatic Storage Management instance.

RFS - Remote File Server process - In Data Guard, the remote file server process on the standby
database receives archived redo logs from the primary database.

QMN - Queue Monitor Process (QMNn) - Used to manage Oracle Streams Advanced Queuing. See
QMNC background task for 10g and beyond.

Oracle Real Application Clusters (RAC) Background Processes
The following are the additional processes spawned for supporting the multi -instance coordination:
DIAG: Diagnosability Daemon – Monitors the health of the instance and captures the data for
instance process failures.

LCKx - This process manages the global enqueue requests and the cross-instance broadcast.
Workload is automatically shared and balanced when there are multiple Global Cache Service
Processes (LMSx).

LMON - The Global Enqueue Service Monitor (LMON) monitors the entire cluster to manage the
global enqueues and the resources. LMON manages instance and process failures and the associated
recovery for the Global Cache Service (GCS) and Global Enqueue Service (GES). In particular, LMON handles the part of recovery associated with global resources. LMON-provided services are also known as cluster group services (CGS)

LMDx - The Global Enqueue Service Daemon (LMD) is the lock agent process that manages enqueue manager service requests for Global Cache Service enqueues to control access to global enqueues and resources. The LMD process also handles deadlock detection and remote enqueue requests.  Remote resource requests are the requests originating from another instance.

LMSx - The Global Cache Service Processes (LMSx) are the processes that handle remote Global
Cache Service (GCS) messages. Real Application Clusters software provides for up to 10 Global Cache Service Processes. The number of LMSx varies depending on the amount of messaging traffic among nodes in the cluster.
The LMSx handles the acquisition interrupt and blocking interrupt requests from the remote
instances for Global Cache Service resources. For cross-instance consistent read requests, the LMSx
will create a consistent read version of the block and send it to the requesting instance. The LMSx
also controls the flow of messages to remote instances.
The LMSn processes handle the blocking interrupts from the remote instance for the Global Cache
Service resources by:

•  Managing the resource requests and cross-instance call operations for the shared resources.

• Building a list of invalid lock elements and validating the lock elements during recovery.

• Handling the global lock deadlock detection and Monitoring for the lock conversion timeouts.

REFERENCES

http://www.dba-oracle.com/
https://asktom.oracle.com/
http://www.toadworld.com/
http://parthokonar.wordpress.com/2009/09/01/latches-and-enqueues/
http://jonathanlewis.wordpress.com/2007/01/21/shared-sql/
http://oracledba.ezpowell.com/oracle/instanceArchitectureInternalInstanceMemory.html
http://arup.blogspot.in/2013/04/streams-pool-is-only-for-streams-think.html
http://www.datadisk.co.uk/html_docs/oracle/flashback.htm

Provisioning Database Access

http://docs.oracle.com/cd/B19306_01/network.102/b14266/admusers.htm#i1006107


Create a User and Grant the Create Session System Privilege

CREATE USER jward
    IDENTIFIED BY AZ7BC2
    DEFAULT TABLESPACE data_ts
    QUOTA 100M ON TBS_NAME
    PROFILE USERS;

GRANT create session TO jward;

RESETTING USERS PASSWORD

ALTER USER andy IDENTIFIED BY swordfish; 

Dropping Users - When a user is dropped, the user and associated schema are removed from the data dictionary and all schema objects contained in the user schema, if any, are immediately dropped.

DROP USER jones CASCADE;

User and Profile Information in Data Dictionary Views

View	Description
DBA_USERS	Describes all users of the database
ALL_USERS	Lists users visible to the current user, but does not describe them
USER_USERS	Describes only the current user
DBA_TS_QUOTAS USER_TS_QUOTAS	Describes tablespace quotas for users
USER_PASSWORD_LIMITS	Describes the password profile parameters that are assigned to the user
USER_RESOURCE_LIMITS	Displays the resource limits for the current user
DBA_PROFILES	Displays all profiles and their limits
RESOURCE_COST	Lists the cost for each resource
V$SESSION	Lists session information for each current session, includes user name
V$SESSTAT	Lists user session statistics
V$STATNAME	Displays decoded statistic names for the statistics shown in the V$SESSTAT view
PROXY_USERS	Describes users who can assume the identity of other users

 

Listing All Users and Associated Information

SELECT USERNAME, PROFILE, ACCOUNT_STATUS FROM DBA_USERS;
 
USERNAME        PROFILE         ACCOUNT_STATUS  
--------------- --------------- --------------- 

Listing All Tablespace Quotas

SELECT * FROM DBA_TS_QUOTAS;

TABLESPACE    USERNAME    BYTES     MAX_BYTES    BLOCKS    MAX_BLOCKS
----------    ---------  --------   ----------   -------   ----------

Listing All Profiles and Assigned Limits

SELECT * FROM DBA_PROFILES
   ORDER BY PROFILE;

PROFILE             RESOURCE_NAME              RESOURCE   LIMIT             
-----------------   ---------------            ---------- --------------

Viewing Memory Use for Each User Session

SELECT USERNAME, VALUE || 'bytes' "Current UGA memory"
   FROM V$SESSION sess, V$SESSTAT stat, V$STATNAME name
WHERE sess.SID = stat.SID
   AND stat.STATISTIC# = name.STATISTIC#
   AND name.NAME = 'session uga memory';

To see the maximum UGA memory ever allocated to each session since the instance started, replace 'session uga memory' in the preceding query with 'session uga memory max'.

A user privilege is a right to execute a particular type of SQL statement, or a right to access another user's object.

Roles, on the other hand, are created by users (usually administrators) and are used to group together privileges
or other roles. They are a means of facilitating the granting of 
multiple privileges or roles to users. 

A profile is a named set of resource limits and password parameters that 

restrict database usage and instance resources for a user.

Create a profile

CREATE PROFILE clerk LIMIT
    SESSIONS_PER_USER 1
    IDLE_TIME 30
    CONNECT_TIME 600;

Drop a profile 

DROP PROFILE clerk CASCADE;

Predefined Roles

Role Name	Created By (Script)	Description
CONNECT	SQL.BSQ	Includes only the following system privilege: CREATE SESSION
RESOURCE	SQL.BSQ	Includes the following system privileges: CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER, CREATE TYPE
DBA	SQL.BSQ	All system privileges WITH ADMIN OPTION
EXP_FULL_DATABASE	CATEXP.SQL	Provides the privileges required to perform full and incremental database exports and includes: SELECT ANY TABLE, BACKUP ANY TABLE, EXECUTE ANY PROCEDURE, EXECUTE ANY TYPE, ADMINISTER RESOURCE MANAGER, and INSERT, DELETE, and UPDATE on the tables SYS.INCVID, SYS.INCFIL, and SYS.INCEXP. Also the following roles: EXECUTE_CATALOG_ROLE and SELECT_CATALOG_ROLE.
IMP_FULL_DATABASE	CATEXP.SQL	Provides the privileges required to perform full database imports. Includes an extensive list of system privileges (use view DBA_SYS_PRIVS to view privileges) and the following roles: EXECUTE_CATALOG_ROLE and SELECT_CATALOG_ROLE.
DELETE_CATALOG_ROLE	SQL.BSQ	Provides DELETE privilege on the system audit table (AUD$)
EXECUTE_CATALOG_ROLE	SQL.BSQ	Provides EXECUTE privilege on objects in the data dictionary. Also, HS_ADMIN_ROLE.
SELECT_CATALOG_ROLE	SQL.BSQ	Provides SELECT privilege on objects in the data dictionary. Also, HS_ADMIN_ROLE.
RECOVERY_CATALOG_OWNER	CATALOG.SQL	Provides privileges for owner of the recovery catalog. Includes: CREATE SESSION, ALTER SESSION, CREATE SYNONYM, CREATE VIEW, CREATE DATABASE LINK, CREATE TABLE, CREATE CLUSTER, CREATE SEQUENCE, CREATE TRIGGER, and CREATE PROCEDURE
HS_ADMIN_ROLE	CATHS.SQL	Used to protect access to the Heterogeneous Services (HS) data dictionary tables (grants SELECT) and packages (grants EXECUTE). It is granted to SELECT_CATALOG_ROLE and EXECUTE_CATALOG_ROLE such that users with generic data dictionary access also can access the HS data dictionary.
AQ_USER_ROLE	CATQUEUE.SQL	Obsolete, but kept mainly for release 8.0 compatibility. Provides execute privilege on DBMS_AQ and DBMS_AQIN.
AQ_ADMINISTRATOR_ROLE	CATQUEUE.SQL	Provides privileges to administer Advance Queuing. Includes ENQUEUE ANY QUEUE, DEQUEUE ANY QUEUE, and MANAGE ANY QUEUE, SELECT privileges on AQ tables and EXECUTE privileges on AQ packages.

Creating a Role

CREATE ROLE clerk IDENTIFIED BY bicentennial;

Dropping Roles

DROP ROLE clerk;

Granting System Privileges and Roles

GRANT CREATE SESSION, accts_pay TO jward;

Creating a New User with the GRANT Statement

GRANT CONNECT TO ssmith IDENTIFIED BY p1q2r3;

Oracle enables you to create a new user with the GRANT statement. If you specify a password using the IDENTIFIED BY
 clause, and the user name/password does not exist in the database, then
 a new user with that user name and password is created. The above
example creates ssmith as a new user while granting ssmith the CONNECT system privilege 

 

Granting Object Privileges

The following statement grants the SELECT, INSERT, and DELETE object privileges for all columns of the emp table to the users, jfee and tsmith:

GRANT SELECT, INSERT, DELETE ON emp TO jfee, tsmith; 

Revoking System Privileges and Roles

The following statement revokes the CREATE TABLE system privilege and the accts_rec role from tsmith:

REVOKE CREATE TABLE, accts_rec FROM tsmith;

Revoking Object Privileges

The following statement revokes the SELECT and INSERT privileges on the emp table from the users jfee and tsmith:

REVOKE SELECT, insert ON emp FROM jfee, tsmith; 

The following statement revokes all object privileges for the dept table that you originally granted to the human_resource role

REVOKE ALL ON dept FROM human_resources; 

Viewing Privilege and Role Information

To access information about grants of privileges and roles, you can query the following data dictionary views:

View	Description
DBA_COL_PRIVS ALL_COL_PRIVS USER_COL_PRIVS	DBA view describes all column object grants in the database. ALL view describes all column object grants for which the current user or PUBLIC is the object owner, grantor, or grantee. USER view describes column object grants for which the current user is the object owner, grantor, or grantee.
ALL_COL_PRIVS_MADE USER_COL_PRIVS_MADE	ALL view lists column object grants for which the current user is object owner or grantor. USER view describes column object grants for which the current user is the grantor.
ALL_COL_PRIVS_RECD USER_COL_PRIVS_RECD	ALL view describes column object grants for which the current user or PUBLIC is the grantee. USER view describes column object grants for which the current user is the grantee.
DBA_TAB_PRIVS ALL_TAB_PRIVS USER_TAB_PRIVS	DBA view lists all grants on all objects in the database. ALL view lists the grants on objects where the user or PUBLIC is the grantee. USER view lists grants on all objects where the current user is the grantee.
ALL_TAB_PRIVS_MADE USER_TAB_PRIVS_MADE	ALL view lists the all object grants made by the current user or made on the objects owned by the current user. USER view lists grants on all objects owned by the current user.
ALL_TAB_PRIVS_RECD USER_TAB_PRIVS_RECD	ALL view lists object grants for which the user or PUBLIC is the grantee. USER view lists object grants for which the current user is the grantee.
DBA_ROLES	This view lists all roles that exist in the database.
DBA_ROLE_PRIVS USER_ROLE_PRIVS	DBA view lists roles granted to users and roles. USER view lists roles granted to the current user.
DBA_SYS_PRIVS USER_SYS_PRIVS	DBA view lists system privileges granted to users and roles. USER view lists system privileges granted to the current user.
ROLE_ROLE_PRIVS	This view describes roles granted to other roles. Information is provided only about roles to which the user has access.
ROLE_SYS_PRIVS	This view contains information about system privileges granted to roles. Information is provided only about roles to which the user has access.
ROLE_TAB_PRIVS	This view contains information about object privileges granted to roles. Information is provided only about roles to which the user has access.
SESSION_PRIVS	This view lists the privileges that are currently enabled for the user.
SESSION_ROLES	This view lists the roles that are currently enabled to the user.

Some examples of using these views follow. For these examples, assume the following statements have been issued:

CREATE ROLE security_admin IDENTIFIED BY honcho;

GRANT CREATE PROFILE, ALTER PROFILE, DROP PROFILE,
    CREATE ROLE, DROP ANY ROLE, GRANT ANY ROLE, AUDIT ANY,
    AUDIT SYSTEM, CREATE USER, BECOME USER, ALTER USER, DROP USER
    TO security_admin WITH ADMIN OPTION;

GRANT SELECT, DELETE ON SYS.AUD$ TO security_admin;

GRANT security_admin, CREATE SESSION TO swilliams;

GRANT security_admin TO system_administrator;

GRANT CREATE SESSION TO jward;

GRANT SELECT, DELETE ON emp TO jward;

GRANT INSERT (ename, job) ON emp TO swilliams, jward; 

Listing All System Privilege Grants

The following query returns all system privilege grants made to roles and users:

SELECT * FROM DBA_SYS_PRIVS;

GRANTEE            PRIVILEGE                         ADM
--------------     --------------------------------- ---
SECURITY_ADMIN     ALTER PROFILE                     YES
SECURITY_ADMIN     ALTER USER                        YES
SECURITY_ADMIN     AUDIT ANY                         YES
SECURITY_ADMIN     AUDIT SYSTEM                      YES
SECURITY_ADMIN     BECOME USER                       YES
SECURITY_ADMIN     CREATE PROFILE                    YES
SECURITY_ADMIN     CREATE ROLE                       YES
SECURITY_ADMIN     CREATE USER                       YES
SECURITY_ADMIN     DROP ANY ROLE                     YES
SECURITY_ADMIN     DROP PROFILE                      YES
SECURITY_ADMIN     DROP USER                         YES
SECURITY_ADMIN     GRANT ANY ROLE                    YES
SWILLIAMS          CREATE SESSION                    NO
JWARD              CREATE SESSION                    NO

 

Listing All Role Grants

SELECT * FROM DBA_ROLE_PRIVS;

GRANTEE            GRANTED_ROLE                         ADM
------------------ ------------------------------------ ---

Listing the Current Privilege Domain of Your Session

The following query lists all roles currently enabled for the issuer:

SELECT * FROM SESSION_ROLES; 

The following query lists all system privileges currently available in the security domain of the issuer, both from explicit privilege grants and from enabled roles:

SELECT * FROM SESSION_PRIVS;

Listing Roles of the Database

The DBA_ROLES data dictionary view can be used to list all roles of a database and the authentication used for each role.

The following query lists all the roles in the database:

SELECT * FROM DBA_ROLES;

Listing Information About the Privilege Domains of Roles

The ROLE_ROLE_PRIVS, ROLE_SYS_PRIVS, and ROLE_TAB_PRIVS data dictionary views contain information on the privilege domains of roles. For example, the following query lists all the roles granted to the system_admin role:

SELECT GRANTED_ROLE, ADMIN_OPTION
   FROM ROLE_ROLE_PRIVS
   WHERE ROLE = 'SYSTEM_ADMIN';

GRANTED_ROLE              ADM
----------------          ----
SECURITY_ADMIN            NO 

The following query lists all the system privileges granted to the security_admin role:

SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE = 'SECURITY_ADMIN';

 

The following query lists all the object privileges granted to the security_admin role:

SELECT TABLE_NAME, PRIVILEGE FROM ROLE_TAB_PRIVS
    WHERE ROLE = 'SECURITY_ADMIN';

 

 

 

 

 

 

 

 

 

 

 

 

 

System DBA views - User Management and Administration

Who are you?

SQL> SELECT *
  2  FROM USER_USERS;

OUTPUT:

USERNAME     USER_ID   DEFAULT_TABLESPACE      TEMPORARY TABLESPACE  CREATED
----------   ------    --------------------    --------------------  --------

USER_USERS view allows you to view how your Oracle ID was set up, when it
was set up, and it also shows other user-specific, vital statistics. 

How to view all users that exist in the database - Use ALL_USERS view 

SQL> SELECT *
  2  FROM ALL_USERS;

USERNAME        USER_ID         CREATED
--------------  -------    ------------

What Are Your Privileges?

Use the following views 

USER_SYS_PRIVS - examine your system privileges

USER_ROLE_PRIVS  - view information about roles you have been granted within the database

What Do You Have Access To?

USER_CATALOG -  is simply a catalog of the tables, views, synonyms, and sequences owned by the current user 

ALL_CATALOG - enables you to see tables owned by other individuals.

USER_OBJECTS - view to select general information about a user's
owned objects, such as the name, type, date created, date modified, and the status
of the object.

USER_TABLES - view the information in detail

ALL_TABLES - allows you to see all the tables to which you have access,
instead of just the tables you own 

As a database user, you can monitor the growth of tables and indexes in your catalog
by querying the USER_SEGMENTS view. As the name suggests.

USER_SEGMENTS gives you information about each segment, such as storage information and extents taken. A
segment may consist of a table, index, cluster rollback, temporary, or cache.

ALL_TAB_PRIVS view lists all privileges that you have as a database user on each
table available to you. 

read-only access = SELECT privileges

When you create objects, you usually need to know where to place them in the database unless you allow your target destination to take the default. An Oracle database is broken up into tablespaces, each of which are capable of storing objects. Each tablespace is allocated a certain amount of disk space, according to what is available on the system. Disk space is usually acquired through the system administrator (SA).

USER_TABLESPACES - will list the tablespaces that you have access to, the default initial and next sizes of objects created within them, and their status.

SQL> SELECT SUBSTR(TABLESPACE_NAME,1,30) TABLESPACE_NAME,
  2         INITIAL_EXTENT,
  3         NEXT_EXTENT,
  4         PCT_INCREASE,
  5         STATUS
  6  FROM USER_TABLESPACES;

TABLESPACE_NAME                INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE STATUS
------------------------------ -------------- ----------- ------------ ------

This type of query is very useful when you are creating objects, such as tables and indexes, which will require storage. When a table or index is created, if the initial and next storage parameters are not specified in the DDL, the table or index will take the tablespace's default values. The same concept applies to PCT INCREASE, which is an Oracle parameter specifying the percentage of allocated space an object should take when it grows. If a value for PCT INCREASE is not specified when the table or index is created, the database server will allocate the default value that is specified for the corresponding tablespace. Seeing the default values enables you to determine whether you need to use a storage clause in the CREATE statement. 

 Sometimes, however, you need to know more than which tablespaces you may access, that is, build tables under. For example, you might need to know what your limits are within the tablespaces so that you can better manage the creation and sizing of your objects. The USER_TS_QUOTAS view provides the necessary information. The next query displays a user's space limits for creating objects in the database. 

 

SQL> SELECT SUBSTR(TABLESPACE_NAME,1,30) TABLESPACE_NAME,
  2         BYTES, MAX_BYTES
  3  FROM USER_TS_QUOTAS;

TABLESPACE_NAME                     BYTES  MAX_BYTES
------------------------------ ---------- ----------

BYTES identifies the total number of bytes in that tablespace that are associated with the user. MAX BYTES identifies the maximum bytes allotted to the user, or the user's quota, on the tablespace. The first two values in this column are self-explanatory. The -1 in the third row means quota unlimited--that is, no limits are placed on the user for that tablespace.  

 

System DBA Views

DBA_USERS - you are able to see the vital information on each user.

DBA_ROLE_PRIVS - provides information about database roles that have been granted to users.

DBA_ROLE_PRIVS - provides information about database roles that have been granted to users. The first column is the grantee, or user. The second column displays the granted role. Notice that every role granted to the user corresponds to a record in the table. ADM identifies whether the role was granted with the Admin option, meaning that the user is able to grant the matching role to other users. The last column is DEFAULT, stating whether the matching role is a default role for the user.

 

SQL> SELECT *
  2  FROM SYS.DBA_SYS_PRIVS
  3  WHERE GRANTEE = 'RJENNINGS';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
AHASTINGS                      CREATE SESSION                           NO
VROY                           UNLIMITED TABLESPACE                     NO

DBA_SYS_PRIVS view lists all system-level privileges that have been granted to the user. This view is similar to DBA_ROLE_PRIVS

DBA_CATALOG is the same thing as the USER_CATALOG, only the owner of the table is included. DBA_CATALOG is a view that the DBA can use to take a quick look at all tables.

DBA_TABLES view gives specific information about database tables, mostly concerning storage

DBA_SYNONYMS view provides a list of all synonyms that exist in the database

DBA_IND - listing all indexes that belong to a schema and matching them up with their corresponding table.

DBA_TABLESPACES to see a list of all tablespaces.

DBA_SEGMENTS provides information about each segment, or object in the database such as storage allocation, space used, and extents

SQL> SELECT SUBSTR(SEGMENT_NAME,1,30) SEGMENT_NAME,
  2         SUBSTR(SEGMENT_TYPE,1,12) SEGMENT_TYPE,
  3         BYTES,
  4         EXTENTS,
  5  FROM SYS.DBA_SEGMENTS

SEGMENT_NAME                   SEGMENT_TYPE      BYTES    EXTENTS
------------------------------ ------------ ---------- ----------

DBA_EXTENTS provides information about each extent of a segment.

SQL> SELECT SUBSTR(OWNER,1,10) OWNER,
  2         SUBSTR(SEGMENT_NAME,1,30) SEGMENT_NAME,
  3         EXTENT_ID,
  4         BYTES
  5  FROM SYS.DBA_EXTENTS

OWNER      SEGMENT_NAME                    EXTENT_ID     BYTES
---------- ------------------------------ ---------- --------

Oracle allocates space to the database by using "data files." Space logically exists within a tablespace, but data files are the physical entities of tablespaces    

SQL> SELECT SUBSTR(TABLESPACE_NAME,1,25) TABLESPACE_NAME,
  2         SUBSTR(FILE_NAME,1,40) FILE_NAME,
  3         BYTES
  4  FROM SYS.DBA_DATA_FILES;

TABLESPACE_NAME           FILE_NAME                                     BYTES
------------------------- ---------------------------------------- ----------

 

Open a Editor from SQL*PLUS

SQL>define_editor=vi    // for UNIX
SQL>define_editor=notepad    //for Windows



SQL>edit    // this would open a vi in Unix


afiedt.buf is the file that would be opened. You can then edit the file with a command\query .


Ensure you put as / as a end of the file


Look out for a file called glogin.sql which would ensure setting is permanent in the system line size or page size

Formatting SQL*PLUS command line terminal

SQL>column <column_name> format a20
// format the column_name with 20 alphabets

SQL>set pagesize 100
//set page size view to 100 alphabets, this decides how much length of the putty terminal will be occupied with the same headings. Like how much letter does a page consist of

SQL>set linesize 150
//set the size of the line to 150 alphabets

For more formatting options check out
http://goo.gl/DPXV9

How to check if Oracle DB is started by spfile or pfile

Below is the code that can be used to check

SELECT DECODE (VALUE, NULL, 'PFILE', 'SPFILE')
"Init File Type"
FROM V$PARAMETER
WHERE NAME = 'spfile';

Oracle Database Startup Modes

No Mount Mode

Open and Read the Parameter file

Create\Append the Alert Log file

Memory Structures are allocated

Background process is started

Instance is started

Administrative Tasks in No Mount mode

Ø  To create Database

Ø  To recreate Control file

Mount Mode

Open and Read the Parameter file

Create\Append the Alert Log file

Memory Structures are allocated

Background process is started

Instance is started.

Open the Control file and read, check for physical existence of file.

Mount the Database. Data file\Redo Log files

Administrative Tasks in Mount mode

Ø  To perform system datafile recovery

Ø  To perform full database complete recovery

Ø  To perform incomplete recovery

Ø  To rename system datafile or redo log file

Ø  To place database to archive log mode to noarchive log mode

Ø  Other administrative tasks

Open Mode

Open and Read the Parameter file

Create\Append the Alert Log file

Memory Structures are allocated

Background process is started

Instance is started.

Open the Control file and read, check for physical existence of file.

Mount the Database. Data file\Redo Log files

Sync status of the Database

Database will be open

Administrative Tasks in Mount mode

To make database avaialble for users

To perform transactions