Grant privileges on all tables in particular schema

In oracle, we cannot grant the privileges on schemas level .If we have to grant the privileges on all the tables of a particular schemas, then it is very tedious to grant privileges on all the tables one-by-one to a particular user. This task can be  performed by using a simple pl/sql procedure. Here is a Demo for this : 

Suppose we have to grant "select" privileges on all the tables to user  then we need to do something like this .

SQL>FOR x IN (SELECT * FROM user_tables)
          LOOP
          EXECUTE  IMMEDIATE  'GRANT  SELECT  ON  ' || your.table_names || '  TO <<user>>' ;
           END LOOP ;




Enjoy    :-)

Why and How to Drop Undo Tablespace ?

A condition may be occur when we have to  drop the Undo tablespace. Undo tablespace may be drop in various scenarios .In my case ,Once i have imported few tables with table_exists_action=append parameter in  database and these tables has created lots of undo's  i.e;  near about 102GB. So when we backup the database ,the backup size increases, i.e; backup consumes lots of space. Another scenario may be possible that while clonning if the undo tablespace get missed, then we can recover by just dropping the undo tablespace. Below is demo for drop and re-creating the undo tablespace. 

Step 1 : Shutdown immediate

SQL> shut immediate

Step 2 : Create pfile from spfile and edit pfile to set undo_management=manual (if it is set auto then set it to manual and if this parameter is not in pfile than set it i.e, undo_management=manual  otherwise it will consider it "auto"  management

Step 3 : Startup pfile=<modified pfile>

Step 4 : Drop undo tablespace as

SQL> drop  tablespace <undo_name> including contents and datafiles.

Step 5 : Create Undo tablespace

SQL> create undo tablespace undotbs1 datafile <location> size=100M;

Step 6 : Shutdown the database and edit pfile to reset  "undo_management=AUTO"

Step 7 : create spfile from pfile

SQL> create spfile from pfile=<pfile_location>

Step 8 : Startup the database

SQL> startup 


Enjoy   :-) 

Differences Between Dedicated Servers, Shared Servers, and Database Resident Connection Pooling

Oracle  creates  server  processes  to  handle  the requests  of  user  processes connected  to  an  instance. A  server process can be either a dedicated server process, where one server process services only one user process, or if  our database server is configured for shared server, it can be a shared server process, where a server process can service multiple user processes . Let's have a look

Dedicated Servers:  

1.)  When a client request is received, a new server process and a session are created for the client.

2.)  Releasing database resources involves terminating the session and server process

3.)  Memory requirement is proportional to the number of server processes and sessions. There is one server and one session for each client.
4.)  Session memory is allocated from the PGA.


Shared Servers : 

1.) When the first request is received  from  a client, the  Dispatcher  process places this request on a common  queue. The request is picked up by an available shared server process. The Dispatcher process then manages the communication between the client and the shared server process.

2.) Releasing database resources involves terminating the session

3.) Memory requirement is proportional to the sum of the shared servers and sessions. There is one session for each client.

4.) Session memory is allocated from the SGA.


Database Resident Connection Pooling : 

1.)  When the first request is received from a client, the Connection Broker picks an available pooled server and hands off the client connection to the pooled server.  If no pooled servers are available, the Connection Broker creates one.If the pool has reached its maximum size, the client request is placed onthe wait queue until a pooled server is available.

2.)  Releasing database resources involves releasing the pooled server to the pool.

3.) Memory requirement is proportional to the number of pooled servers and their sessions.There is one session for each pooled server.

4.) Session memory is allocated from the PGA.

Example of Memory Usage for Dedicated Server, Shared Server, and Database Resident Connection Pooling :

Consider an application in which the memory required for each session is 400 KB and the memory required for each server process is 4 MB. The pool size is 100 and the number of shared servers used is 100.If there are 5000 client connections, the memory used by each configuration is as  follows:

Dedicated Server 
Memory used = 5000 X (400 KB + 4 MB) = 22 GB


Shared Server 
Memory used = 5000 X 400 KB + 100 X 4 MB = 2.5 GB
Out of the 2.5 GB, 2 GB is allocated from the SGA.


Database Resident Connection Pooling 
Memory used = 100 X (400 KB + 4 MB) + (5000 X 35KB)= 615 MB
where 35KB is used for others operation




Enjoy    :-)