All About Temporary Tablespace Part I

There are lots of confusion about the temporary tablespaces.Here i have tried to covered the basic of temporary tablespace and one of the famous related error i.e; ORA-1652 says "unable to extend temp segment" . This problem can be solved as following : 

1.)  Increase the size of  temporary tablespace either by resizing the tempfile or by adding the tempfile.

2.) Check the SQL statements which is consuming the large temp tablespace and kill the corresponding session.(not proper solution).

3.) Check the SQL and tuned it.

Here i have explain what we can do when our database runs out of space.

Introduction : 

Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables. For example, if we join two large tables, and Oracle cannot do the sort in memory (see sort_area_size initialisation parameter), space will be allocated in a temporary tablespace for doing the sort operation. Other SQL operations that might require disk sorting are: create Index , Analyse, Select Distinct, Order By, Group By, Union, Intersect , Minus, Sort-Merge joins, etc.

A temporary tablespace contains transient data that persists only for the duration of the session. Temporary tablespaces can improve the concurrency of multiple sort operations, reduce their overhead, and avoid Oracle Database space management operations. Oracle can also allocate temporary segments for temporary tables and indexes created on temporary tables. Temporary tables hold data that exists only for the duration of a transaction or session. Oracle drops segments for a transaction-specific temporary table at the end of the transaction and drops segments for a session-specific temporary table at the end of the session. If other transactions or sessions share the use of that temporary table, the segments containing their data remain in the table. Here, we will cover the following points in next link : 

1.) How Oracle managed sorting operations
2.) How DBA determines and handle the database when temporary tablespace running out of space

Click Here for next Part II :

For more detail about temporary tablespace  Click Here

Drop all object of Schemas

Sometimes we need to drop the all objects of schemas while importing. The basic approach is drop the schemas and recreate the schemas. This method is quite efficient . To perform this operation we need the system or sysdba privileges to create and drop the user. If anyone have not the system and sysdba privileges then, dropping all objects is the option . Below is the Demo where we will drop all objects of "SCOTT" schemas.

Step 1 : Generate the scripts for dropping the schemas :

SQL> spool C:\genera_dropall.sql

SQL> select 'drop '||object_type||' '|| object_name||  DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS;',';')  from user_objects;

'DROP'||OBJECT_TYPE||''||OBJECT_NAME||DECODE(OBJECT_TYPE,'TABLE','CASCADECONSTRAINTS;',';')          
------------------------------------------------------------------------------------------------------------
drop TABLE SALGRADE CASCADE CONSTRAINTS;                                                                                                                                                        
drop TABLE BONUS CASCADE CONSTRAINTS;                                                                                                                                                        
drop INDEX PK_EMP;                                                                                                                                                                      
drop TABLE EMP CASCADE CONSTRAINTS;                                                                                                                                        
drop TABLE DEPT CASCADE CONSTRAINTS;                                                                                                                                                    
drop INDEX PK_DEPT;                                                                                                                                                                        
6 rows selected.
Since we have the drop script as 'genera_dropall.sql'

Step  2  :  Now we will drop all_objects i.e, 'genera_dropall.sql' script is used to drop everything in schemas.

SQL>@"genera_dropall.sql" 
Now check the object in scott schemas

SQL> select * from tab;
TNAME                                                       TABTYPE           CLUSTERID
------------------------------                               ------------             --------------
BIN$5JksbkFeSai/0JTwbJOenQ==$0           TABLE
BIN$KtthiEIaRZmkv/5+FoYu5A==$0              TABLE
BIN$L/qcqzTxTsm8XHkDrfANOg==$0          TABLE
BIN$opUCTunxRf+0AUbhOzzBgw==$0        TABLE

The SQL was written against Oracle (hence the "purge recyclebin" at the bottom and the exclusion of objects already in the recycle bin from the "drop" loop).

Step  3  : Purge the recyclebin objects.

SQL>purge recyclebin ;
Recyclebin purged.

This will produce a list of drop statements. Not all of them will execute - if we drop with cascade, dropping the PK_* indices will fail. But in the end, you will have a pretty clean schema. Confirm with: 

SQL> select * from user_objects;
no rows selected


Enjoy    :-) 

ORA-01555: Snapshot Too Old

UNDO is  the  backbone  of  the  READ CONSISTENCY  mechanism  provided  by  Oracle. Multi-User Data  Concurrency  and  Read  Consistency  mechanism  make  Oracle  stand  tall  in  Relational  Database Management  Systems  (RDBMS) world .

Best  of  all,  automatic  undo  management  allows  the  DBA  to  specify  how  long  undo  information should  be  retained  after  commit, preventing  “snapshot too old”   errors  on  long  running  queries. This  is  done  by  setting  the UNDO_RETENTION  parameter.  The   default  is  900  seconds (5 minutes), and  we  can set  this  parameter  to  guarantee  that  Oracle  keeps undo  logs  for  extended  periods  of time. The  flashback  query  can  go  upto  the  point  of  time specified as a value in  the UNDO_RETENTION parameter.

Why ORA-01555 error occur? 

1.) Oracles  does  this  by reading  the  "before image"  of  changed  rows  from  the  online  undo  segments.  If  we  have  lots  of  updates, long running SQL  i.e , rollback records  needed  by  a  reader  fo  consistent read are overwritten by other writers.

2.) It may also due small size of undo and small undo_retention period .

To  solve  this  issues  we need  to  increase the  undo  tablepsace  and  undo  retention  period. Now  the issue  is  how  much  should  be  the  optimal value of  undo  retention and undo tablespace. For this we use the advisor. By using OEM, it is quite easy to estimate the size and time duration of undo.

Calculate Optimal Undo_Retention  :
The following query will help us to optimize the UNDO_RETENTION parameter :


Optimal Undo Retention = Actual Undo Size / (DB_BLOCK_SIZE × UNDO_BLOCK_REP_SEC)

To calculate Actual Undo Size :

SQL> SELECT SUM(a.bytes)/1024/1024 "UNDO_SIZE_MB"
            FROM v$datafile a, v$tablespace b,dba_tablespaces c
           WHERE c.contents = 'UNDO'
           AND c.status = 'ONLINE'
           AND b.name = c.tablespace_name
           AND a.ts# = b.ts#;

Undo Blocks per Second : 

SQL> SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) "UNDO_BLOCK_PER_SEC"

FROM v$undostat ;

DB Block Size  :

SQL> SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [Byte]"   FROM   v$parameter
  WHERE name = 'db_block_size';

We can do all in one query as

SQL> SELECT d.undo_size/(1024*1024) “ACT_UNDO_SIZE [MB]“, 
              SUBSTR(e.value,1,25) “ UNDO_RTN [Sec] “, 
              ROUND((d.undo_size / (to_number(f.value) * 
              g.undo_block_per_sec))) “OPT_UNDO_RET[Sec]” 
       FROM ( 
                       SELECT SUM(a.bytes) undo_size 
                        FROM v$datafile a, 
                         v$tablespace b, 
                        dba_tablespaces c 
                        WHERE c.contents = 'UNDO' 
                       AND c.status = 'ONLINE' 
                       AND b.name = c.tablespace_name 
                       AND a.ts# = b.ts# 
                       ) d, 
                            v$parameter e, 
                             v$parameter f, 
( 
       SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) 
             undo_block_per_sec 
         FROM v$undostat 
       ) g 
              WHERE e.name = 'undo_retention' 
              AND f.name = 'db_block_size'
/
ACT_UNDO_SIZE [MB]         UNDO_RTN [Sec]              OPT_UNDO_RET[Sec]

------------------------             ----------------------            ----------------------------
                      50                                  900                                              24000

Calculate Needed UNDO Size :

If  we  are  not  limited  by  disk  space, then  it would  be  better  to  choose  the  UNDO_RETENTION time that is best for us (for FLASHBACK, etc.). Allocate the appropriate size to the UNDO tablespace according to the database activity :                                                                                                                                                              

Formula :Undo Size = Optimal Undo Retention × DB_BLOCK_SIZE × UNDO_BLOCK_PER_SEC



Here again we can find in a single :

SQL> SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
       (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
       g.undo_block_per_sec) / (1024*1024)
      "NEEDED UNDO SIZE [MByte]"
  FROM (
       SELECT SUM(a.bytes) undo_size
         FROM v$datafile a,
              v$tablespace b,
              dba_tablespaces c
        WHERE c.contents = 'UNDO'
          AND c.status = 'ONLINE'
          AND b.name = c.tablespace_name
          AND a.ts# = b.ts#
       ) d,
      v$parameter e,
       v$parameter f,
       (
       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
         undo_block_per_sec
         FROM v$undostat
       ) g
 WHERE e.name = 'undo_retention'
  AND f.name = 'db_block_size'
/

We can avoid ORA-01555  error as follows :

1.) Do not run discrete transactions while sensitive queries or transactions are running, unless we are confident that the data sets required are mutually exclusive.

2.) Schedule long running queries and transactions out of hours, so that the consistent gets will not need to rollback changes made since the snapshot SCN. This also reduces the work done by the server, and thus improves performance.

3.) Code long running processes as a series of restartable steps.

4.) Shrink all rollback segments back to their optimal size manually before running a sensitive query or transaction to reduce risk of consistent get rollback failure due to extent deallocation.

5.) Use a large optimal value on all rollback segments, to delay extent reuse.

6.) Don't fetch across commits. That is, don't fetch on a cursor that was opened prior to the last commit, particularly if the data queried by the cursor is being changed in the current session.

7.) Commit less often in tasks that will run at the same time as the sensitive query, particularly in PL/SQL procedures, to reduce transaction slot reuse.


Enjoy       :-)

How to configure Shared Server?

A  shared  server  process allows  a  single  server  process  to  service  several  clients,  based on the premise  that  usually  in  an  OLTP  environment, a user  is  more often  than  not, reading  and  editing data on  the screen  than  actually  executing  a  DML. What  this  means  is  that,  there will  be  chunks  of  time when  the dedicated  server  process, dedicated  to  a  particular c lient  will  be  sitting  idle. It  is  this idleness that is exploited by the shared server process in servicing several clients together.

Shared  server is enabled  by  setting  the  SHARED_SERVERS  initialization  parameter  to a  value greater  than  0. The other  shared  server  initialization  parameters  need  not  be  set. Because shared  server  requires  at least  one dispatcher  in order  to work, a  dispatcher  is  brought  up even  if  no dispatcher  has  been  configured. The  SHARED_SERVERS  initialization parameter  specifies  the  minimum  number  of shared  servers  that we  want  created  when  the instance is started. After  instance startup, Oracle Database can dynamically adjust the number  of  shared  servers  based  on  how  busy  existing shared servers are and the length of the request queue.

In  typical  systems, the number of  shared  servers stabilizes  at  a  ratio of  one  shared  server  for  every  ten  connections . For  OLTP  applications, when  the rate of   requests  is  low, or  when  the  ratio  of  server usage  to  request  is  low,  the connections-to-servers  ratio could  be  higher . If  we  know  the average  load  on  our  system, then we  can  set  SHARED_SERVERS  to  an  optimal  value. The Below  example   shows  how  we  can  use  this  parameter .

For Example 

Assume a database  is  being  used  by  a  telemarketing  center  staffed  by  1000  agents. On  average,  each  agent  spends  90%  of  the  time  talking  to  customers  and  only  10%  of  the  time  looking  up  and updating  records.  To  keep  the  shared  servers from  being  terminated  as  agents  talk  to  customers  and  then  spawned  again  as  agents  access  the  database, a  DBA  specifies  that  the  optimal  number  of shared  servers  is  100 . However, not  all  work  shifts are  staffed  at  the  same  level. On  the  night  shift,  only  200  agents  are  needed. Since  SHARED_SERVERS  is  a  dynamic parameter, a  DBA  reduces  the number of  shared  servers  to  20  at  night, thus  allowing  resources  to  be  freed up  for other  tasks  such  as  batch  jobs .

Setting the Initial Number of Dispatchers  

We  can  specify multiple  dispatcher  configurations  by  setting  DISPATCHERS  to  a  comma  separated list  of  strings, or  by  specifying  multiple  DISPATCHERS parameters  in  the  initialization  file. If  we specify  DISPATCHERS  multiple times, the  lines  must  be  adjacent  to  each  other  in  the  initialization parameter  file.  Internally,  Oracle  Database  assigns  an  INDEX  value  (beginning with zero)  to  each DISPATCHERS parameter. We  can later refer  to  that  DISPATCHERS  parameter  in  an  ALTER SYSTEM  statement  by  its  index  number. Some  examples  of  setting  the  DISPATCHERS  initialization parameter follow. 

DISPATCHERS="(PROTOCOL=TCP)(DISPATCHERS=2)"  
DISPATCHERS="(ADDRESS=(PROTOCOL=TCP)(HOST=144.25.16.201))(DISPATCHERS=2)"
To  force  the  dispatchers  to  use  a  specific  port  as  the  listening  endpoint,  add  the  PORT  attribute  as follows:
DISPATCHERS="(ADDRESS=(PROTOCOL=TCP)(PORT=5000))"
DISPATCHERS="(ADDRESS=(PROTOCOL=TCP)(PORT=5001))"


Determining the Number of Dispatchers :  

Once  we  know  the  number  of  possible  connections  for  each  process  for  the  operating  system, calculate  the  initial  number  of  dispatchers  to  create  during  instance  startup,  for  each  network  protocol,  using the following formula:

Number of dispatchers =  CEIL ( max. concurrent sessions / connections for each dispatcher )
CEIL returns the result roundest up to the next whole integer.

For example, assume  a  system  that  can  support  970  connections  for  each  process,  and  that  has :

A  maximum  of  4000  sessions  concurrently  connected  through   TCP/IP  and  A  maximum  of   2,500 sessions  concurrently  connected  through  TCP/IP  with  SSL  then   DISPATCHERS  attribute  for  TCP/IP  should  be  set  to  a  minimum  of  five  dispatchers (4000 / 970), and for TCP/IP with SSL three dispatchers (2500 / 970) :

DISPATCHERS='(PROT=tcp)(DISP=5)', '(PROT-tcps)(DISP=3)'
Depending on performance, we  may need to adjust the number of dispatchers.

Steps to configure shared server :   To  configure   shared  server  we  have  to  enable  the  following parameter . All the  below  parameters  are dynamic . Below  is  Demo to  configur e the  shared  server. 

1.) alter system set shared_servers= 25;
2.) alter system set max_shared_servers= 50;
3.)  alter system set dispatcherS= '(PROT=tcp)(DISP=30)';
4.) Add (SERVER = SHARED) in tnsnames.ora file.
 the  tnsnames.ora  file  look  like

NOIDA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = XXXX)(PORT = 1521))
    )
    (CONNECT_DATA =
 (SERVER = SHARED)
      (SERVICE_NAME = noida)
    )

To check the status of server fire the below query :

SQL> select distinct server,username from  v$session ; 
SERVER                   USERNAME
-------------          -----------------
DEDICATED                SYS
DEDICATED

Once, i found that after configuring the shared server the above query shows the server status as 'NONE' .

What does it mean if SERVER = 'NONE' in v$session?

On  googling ,  i  found  that , in  Shared  Server  configuration  when  we  see  value  'NONE'  ,  it  means there  is  no  task  being  processed  by  shared  server  for  that  session. The  server  column  will  infact  show  status  of   'SHARED'  if  there is  some  task  being  processed  at  that  particular  time  by  the shared  server  process  for  that  session. Hence to check the status ,  fire  some  big  query  and  then  check the server  status .

Disabling Shared Server : 

We  can  disable  shared  server  by  setting   SHARED_SERVERS   to  0. we  can  do  this  dynamically with the  'alter system'  statement. When  we  disable  shared  server,  no  new  clients  can  connect  in  shared   mode.  However,  Oracle  Database  retains  some  shared  servers  until  all  shared  server connections  are closed. The  number  of shared  servers  retained  is  either  the  number  specified  by  the  preceding  setting  of  shared_servers   or   the  value of  the  max_ shared_servers   parameter ,  whichever  is  smaller. If  both  shared_servers  and  max_ shared_servers  are  set  to  0, then  all  shared  servers  will terminate and  requests  from  remaining  shared  server clients  will  be  queued  until  the  value of  shared_servers  or max_ shared_servers  is raised again . To terminate dispatchers once all shared server clients disconnect, enter this statement:

SQL> alter system set dispatchers='' ;  

Enjoy   :-) 

How To Check Which Oracle Features are Enabled ?

There is a very good view DBA_FEATURE_USAGE_STATISTICS . Its function is to display information about database feature usage statistics. Oracle 10g will keep track of any of its features that are being used.   As an example, if we SELECT from any of the Oracle Diagnostic Pack and Tuning Pack views like V$ACTIVE_SESSION_HISTORY, the database will record our usage of it in this DBA view.  

Some of the information tracked are :

•  Name of the feature
•  Number of times the system has detected usage for the feature
•  First sample time the system detected usage of the feature
•  Last sample time the system detected usage of the feat

The below sql will give the detail of the oracle services which are being used.

SQL> select NAME, VERSION, DETECTED_USAGES, CURRENTLY_USED, FIRST_USAGE_DATE, LAST_USAGE_DATE from DBA_FEATURE_USAGE_STATISTICS where CURRENTLY_USED  = 'TRUE' order by 1,2;

Output : 

How to  check the Oracle Edition  : 
We can the check the oracle edition i.e; whether it is enterprise edition or standard edition by using the below command :

SQL>select * from product_component_version;

PRODUCT                                                     VERSION                 STATUS

----------------------------------                        -------------               ------------
NLSRTL                                                         11.2.0.1.0             Production
Oracle Database 11g Enterprise Edition      11.2.0.1.0             Production
PL/SQL                                                         11.2.0.1.0             Production
TNS for 32-bit Windows:                              11.2.0.1.0             Production


Enjoy    :-)