Identify IP Addresses and Host Names

We can identify the IP addresses of all the client connect to server. Below the command to identified  the IP address ,machine name,sid  .

SQL>   select   sid,   machine,     UTL_INADDR.get_host_address (substr(machine,instr(machine,'\')+1))  ip  from   v$session   where   type='USER'   and    username is not null   order  by   sid;

Oracle provides the method to identifying and relating to the IP addresses and host names  for Oracle clients and servers. The few methods  are  : 

1.) UTL_INADDR
2.) SYS_CONTEXT
3.) V$INSTANCE
4.) V$SESSION

1.) UTL_INADDR  :  The UTL_INADDR package provides a PL/SQL procedures to support internet addressing. It provides an API to retrieve host names and IP addresses of local and remote hosts.The utl_inaddr like the two function  : 

1.) get_host_address and 
2.) get_host_name.

Now we check the description of UTL_INADDR 

SQL> desc UTL_INADDR

FUNCTION  GET_HOST_ADDRESS  RETURNS  VARCHAR2
 Argument Name                Type                In/Out         Default?
 -----------------          ------------           --------      -------------
 HOST                         VARCHAR2            IN           DEFAULT
FUNCTION GET_HOST_NAME RETURNS VARCHAR2
 Argument Name               Type                    In/Out             Default?
 ----------------           ---------------        --------          ------------
 IP                                 VARCHAR2            IN                DEFAULT

I.) Get_host_address: The get_host_address function like the argument name "HOST" , data types "varchar2" and by default NULL. The get_host_address get local IP address and remote host given its IP address.

II.) Get_host_name: The get_host_name function like the argument name "IP" , data types "varchar2" and by default NULL. The get_host_name get local host name and remote host given its name.

Let see some example : 

The GET_HOST_ADDRESS function returns the IP address of the specified host name

SQL>select  UTL_INADDR.get_host_address('TECH-284') from  dual;

UTL_INADDR.GET_HOST_ADDRESS('TECH-284')
-----------------------------------------------------------
192.100.0.85

The GET_HOST_NAME function returns the host name of the specified IP address.

SQL> SELECT UTL_INADDR.get_host_name('192.168.52.127') FROM dual;

UTL_INADDR.GET_HOST_NAME('192.168.52.127')
----------------------------------------------------------
tech-199

The host name of the database server is returned if the specified IP address is NULL or omitted.

2.) SYS_CONTEXT : Oracle has a very useful built-in function called SYS_CONTEXT. The SYS_CONTEXT function is able to return the following host and IP address information for the current session.SYS_CONTENT has the following options as 

TERMINAL - An operating system identifier for the current session. This is often the client machine name.
HOST - The host name of the client machine.
IP_ADDRESS - The IP address of the client machine.
SERVER_HOST - The host name of the server running the database instance.

What makes this function more interesting is the fact that Oracle provides a built-in namespace called USERENV with predefined parameters, which describes the current session.

The syntax of this function goes like this:
SYS_CONTEXT ( 'namespace' , 'parameter' [, length] )

Let see some example :

SQL> SELECT SYS_CONTEXT('USERENV','TERMINAL') FROM dual;
SYS_CONTEXT('USERENV','TERMINAL')
---------------------------------------------------
TECH-199

SQL> SELECT SYS_CONTEXT('USERENV','HOST') FROM dual;
SYS_CONTEXT('USERENV','HOST')
------------------------------------------
TECH\TECH-199

SQL> SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') FROM dual;
SYS_CONTEXT('USERENV','IP_ADDRESS')
--------------------------------------------------
192.100.0.112

SQL> SELECT SYS_CONTEXT('USERENV','SERVER_HOST') FROM dual;
SYS_CONTEXT('USERENV','SERVER_HOST')
-----------------------------------------------------
tech-199

For more about sys_context check the below link .
http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/functions150.htm
http://www.adp-gmbh.ch/ora/sql/sys_context.html

3.) V$INSTANCE :  The host_name column of the V$INSTANCE view contains the host name of the server running the instance.

SQL> SELECT host_name FROM v$instance;
HOST_NAME
-----------------
TECH-199

4.) V$SESSION  :  V$SESSION displays session host information for each current session. The column such as terminal and machine give the following details.
TERMINAL: The operating system terminal name for the client.This is often set to the client machine name.
MACHINE :The operating system name for the client machine.This may include the domain name if present.

SQL> SELECT terminal, machine FROM v$session WHERE username = 'HR';
TERMINAL           MACHINE
-------------       --------------------
TECH-199          TECH\TECH-199


Enjoy    :-)

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    :-)

All About Temporary Tablespace Part IV

Monitoring Temporary Space Usage :

We can monitor temporary space usage in the database in real time. At any given time, Oracle can tell us about all of the database’s temporary tablespaces, sort space usage on a session basis, and sort space usage on a statement basis. All of this information is available from v$ views, and the queries shown in this section can be run by any database user with DBA privileges.

Temporary Segments :

The following query displays information about all sort segments in the database. (As a reminder, we use the term “sort segment” to refer to a temporary segment in a temporary tablespace.) Typically, Oracle will create a new sort segment the very first time a sort to disk occurs in a new temporary tablespace. The sort segment will grow as needed, but it will not shrink and will not go away after all sorts to disk are completed. A database with one temporary tablespace will typically have just one sort segment.

SQL> SELECT A.tablespace_name tablespace, D.mb_total,SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free FROM v$sort_segment A,(SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total FROM v$tablespace B, v$tempfile C WHERE B.ts#= C.ts# GROUP BY B.name, C.block_size ) D 
WHERE A.tablespace_name = D.name GROUP by A.tablespace_name, D.mb_total; 

The query displays for each sort segment in the database the tablespace the segment resides in, the size of the tablespace, the amount of space within the sort segment that is currently in use, and the amount of space available. Sample output from this query is as follows:

TABLESPACE     MB_TOTAL     MB_USED       MB_FREE
-----------                ------------          ------------         ---------
TEMP                     10000                   9                    9991

This example shows that there is one sort segment in a 10,000 Mb tablespace called TEMP. Right now, 9 Mb of the sort segment is in use, leaving a total of 9,991 Mb available for additional sort operations. (Note that the available space may consist of unused blocks within the sort segment, unallocated extents in the TEMP tablespace, or a combination of the two.)

Sort Space Usage by Session :

The following query displays information about each database session that is using space in a sort segment. Although one session may have many sort operations active at once, this query summarizes the information by session. 

SQL> SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module, S.program, SUM (T.blocks)* TBS.block_size/1024/1024 mb_used, T.tablespace, COUNT(*) sort_ops FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P WHERE T.session_addr = S.saddr  AND S.paddr = P.addr AND T.tablespace = TBS.tablespace_name GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module, S.program, TBS.block_size, T.tablespace  ORDER BY sid_serial;

The query displays information about each database session that is using space in a sort segment, along with the amount of sort space and the temporary tablespace being used, and the number of sort operations in that session that are using sort space.Sample output from this query is as follows: 

SID_SERIAL  USERNAME OSUSER SPID MODULE PROGRAM   MB_USED TABLESPACE SORT_OPS
----------   -------- ------ ---- ------ --------- ------- ---------- --------
33,16998    RPK_APP    rpk  3061   inv   httpd@db1    9       TEMP       2

This example shows that there is one database session using sort segment space. Session 33 with serial number 16998 is connected to the database as the RPK_APP user. The connection was initiated by the httpd@db1 process running under the rpk operating system user, and the Oracle server process has operating system process ID 3061. The application has identified itself to the database as module “inv.” The session has two active sort operations that are using a total of 9 Mb of sort segment space in the TEMP tablespace.

Sort Space Usage by Statement :
The following query displays information about each statement that is using space in a sort segment.

SQL> SELECT S.sid || ',' || S.serial# sid_serial, S.username, T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace, T.sqladdr address, Q.hash_value, Q.sql_text FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS WHERE T.session_addr = S.saddr AND T.sqladdr = Q.address (+) AND T.tablespace = TBS.tablespace_name ORDER BY S.sid ;

The query displays information about each statement using space in a sort segment,including information about the database session that issued the statement and the temporary tablespace and amount of sort space being used. 

Conclusion : 

When an operation such as a sort, hash, or global temporary table instantiation is too large to fit in memory, Oracle allocates space in a temporary tablespace for intermediate data to be written to disk. Temporary tablespaces are a shared resource in the database, and we can’t set quotas to limit temporary space used by one session or database user. If a sort operation runs out of space, the statement initiating the sort will fail. It may only take one query missing part of its WHERE clause to fill an entire temporary tablespace and cause many users to encounter failure because the temporary tablespace is full. It is easy to detect when failures have occurred in the database due to a lack of temporary space. With the setting of a simple diagnostic event, it is also easy to see the exact text of each statement that fails for this reason. There are also v$ views that DBAs can query at any time to monitor temporary tablespace usage in real time. These views make it possible to identify usage at the database, session, and even statement level. Oracle DBAs can use the techniques outlined in this paper to diagnose temporary tablespace problems and monitor sorting activity in a proactive way.

   

(Ref : Roger Schrag Database Specialists )

Enjoy    :-)