Oracle Session and Its Properties

# What is the difference between v$, gv$, x$ v_$ and gv_$ ?
v$ - these objects are synonyms not view
gv$ - these objects are also synonyms where g stand for "global" it contains some extra columns for RAC information.
v_$ - these are the actual views where v$ synonyms have created from it.
gv_$ - these are also same actual view which contains more information about RAC
x_$ - there are the actual X$ structure Tables which are mapped to v$ views


# V$ View which stores all the information of Dynamic view
v$fixed_view_definition
v$fixed_table

# Set a session to perform activity behalf of another user if you are login as sys/system
ALTER SESSION SET CURRENT_SCHEMA=WIRES;


# Find out your session information 
SELECT SYS_CONTEXT('USERENV', 'SID') FROM DUAL; -- SYS_CONTEXT funtion have multiple parameter as our requirment
SELECT USERENV('SID') "SID" FROM DUAL;
select p.pid,p.spid,p.pname,p.username,p.serial#, p.program,s.username,s.logon_time from v$process p, v$session s where s.paddr=p.addr;
select sid, serial# from v$session s where paddr = (select addr from v$process where spid = 23048);

# To Find Current running statement of Session
set pagesize 50000
set linesize 30000
set long 500000
set head off

select sesion.sid, sql_text from v$sqltext sqltext, v$session sesion
where sesion.sql_hash_value = sqltext.hash_value
and sesion.sql_address = sqltext.address;
and sesion.sql_id = sqltext.sql_id;
and sesion.username is not null order by sqltext.piece;

select a.sid, a.username,b.sql_id, b.sql_fulltext 
from v$session a, v$sql b 
where a.sql_id = b.sql_id 
and a.status = 'ACTIVE' and a.username = 'KUMARA45';

select s.username USername, substr(sa.sql_text,1,54) TEST_DATA
from v$process p, v$session s, v$sqlarea sa
where p.addr=s.paddr
and s.username = 'SIEBEL';
and s.sql_address=sa.address(+)
and sa.sql_text like '%DEL%'
and s.sql_hash_value=sa.hash_value(+);


# Find out the Locked Session Details of user
select substr(a.os_user_name,1,15) "OS_User"
,substr(a.oracle_username,1,8) "DB_User"
,substr(b.owner,1,8) "Schema"
,substr(b.object_name,1,30) "Object_Name"
,substr(b.object_type,1,10) "Object_Type"
,substr(c.segment_name,1,15) "RBS"
,substr(d.used_urec,1,12) "#_of-UndoRecords"
,e.sid,e.serial#
from v$locked_object a
,dba_objects b
,dba_rollback_segs c
,v$transaction d
,v$session e
where a.object_id =  b.object_id
and a.xidusn  = c.segment_id
and a.xidusn  = d.xidusn
and a.xidslot = d.xidslot
and d.addr    = e.taddr;

SELECT a.sid,a.serial#, a.username,c.os_user_name,a.terminal,
b.object_id,substr(b.object_name,1,40) object_name,sysdate
from v$session a, dba_objects b, v$locked_object c
where a.sid = c.session_id
and b.object_id = c.object_id;

select c.owner,c.object_name,c.object_type,b.sid,b.serial#,b.status,b.osuser,b.machine
from v$locked_object a , v$session b, dba_objects c
where b.sid = a.session_id and a.object_id = c.object_id;

select event,p1,p2,p3 from v$session_wait where wait_time=0 and event='enqueue';

# Find out the long running session
set lines 200
col "Index Operation" for a60 trunc
col "ETA Mins" format 999.99
col "Runtime Mins" format 999.99

select sess.sid as "Session ID", sql.sql_text as "Index Operation",
longops.totalwork, longops.sofar,
longops.elapsed_seconds/60 as "Runtime Mins",
longops.time_remaining/60 as "ETA Mins"
from v$session sess, v$sql sql, v$session_longops longops
where
sess.sid=longops.sid
and sess.sql_address = sql.address
and sess.sql_address = longops.sql_address
and sess.status  = 'ACTIVE'
and longops.totalwork > longops.sofar
and upper(sql.sql_text) like '%INDEX%'
order by 3, 4;

Oracle SQL*Loader Tips and Tricks

# Oracle SqlLoader from Command Line

sqlldr system/pump5k1n control=LOV_LOAD.ctl data=LOV_Value.txt LOG=LOV_Value.log BAD=LOV_Value.bad

sqlldr system/pump5k1n control=LOV_TYPE_LOAD.ctl data=New_LOV_Type.txt LOG=New_LOV_Type.log BAD=New_LOV_Type.bad

DBA_OBJECTS and Calculate the Size of Oracle Objects and Database Files

# How to calculate the Size of Database Datafiles, Tablespace and Temp-Tablespace

select "Reserved_Space(MB)", "Reserved_Space(MB)" - "Free_Space(MB)" "Used_Space(MB)","Free_Space(MB)" from(select(select sum(bytes/(1014*1024)) from dba_data_files) "Reserved_Space(MB)", (select sum(bytes/(1024*1024)) from dba_free_space) "Free_Space(MB)" from dual);

select sum(bytes/1024/1024/1024) from dba_data_files;

select ( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) + ( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) + ( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) + ( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB" from dual;

select sum(bytes)/1024/1024/1024 temp_size from dba_temp_files;

select TABLESPACE_NAME, sum(bytes)/1024/1024 "DB Size in MB" from dba_data_files group by tablespace_name;

select sum(bytes / (1024*1024)) "DB Size in MB" from dba_data_files where tablespace_name='RECONAREA';

select sum(bytes / (1024*1024)) "Temp Size in MB" from dba_temp_files;

# How to calculate the size of tables

select owner,segment_name,sum(bytes)/1024/1024/1024 as GB from dba_segments where owner='SCOTT' and segment_name='EMP_TABLE' group by owner,segment_name;

select sum(bytes)/1024/1024/1024 as size_in_gig,segment_name from dba_segments where segment_name in ('SS7DATA','MSCDATA') and owner='RECON' group by segment_name;

select owner,segment_name,sum(bytes)/1024/1024/1024 as GB from dba_segments where owner='TRACK_OWNER' and segment_name='PR_ADDTL_DATA' group by owner,segment_name;

# DBA_OBJECTS Queries

select OWNER, object_type, count(object_name) from dba_objects where OWNER in ('USER1','USER2','USER3') group by object_type, owner order by owner;

select count(*) from dba_objects where owner in ('USER1','USER2','USER3') and object_type not in ('TABLE','DATABASE LINK');

select count(*) from dba_objects where owner in ('USER1','USER2','USER3') and object_type in ('MATERIALIZED VIEW');

select object_type, count(object_name) as Count from dba_objects group by object_type order by cc;

select object_type,status,count(*) from dba_objects where owner='REPOSITORY1' group by object_type, status order by object_type, status;

select owner,object_name,status from dba_objects where status= 'VALID' and owner in ('REPOSITORY1','EMBARC_ETL','EMBARC_SAS');

select owner,object_name,object_type, status from dba_objects where owner ='GPAS_STAGING' and object_name like '%GM_GPAS_REFRESH_ALL_MV%';

# Calculate the Size of TableSpaces

SELECT /* + RULE */  df.tablespace_name "Tablespace",

       df.bytes / (1024 * 1024) "Size (MB)",

       SUM(fs.bytes) / (1024 * 1024) "Free (MB)",

       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",

       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"

  FROM dba_free_space fs,

       (SELECT tablespace_name,SUM(bytes) bytes

          FROM dba_data_files

         GROUP BY tablespace_name) df

 WHERE fs.tablespace_name (+)  = df.tablespace_name

 GROUP BY df.tablespace_name,df.bytes

UNION ALL

SELECT /* + RULE */ df.tablespace_name tspace,

       fs.bytes / (1024 * 1024),

       SUM(df.bytes_free) / (1024 * 1024),

       Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),

       Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)

  FROM dba_temp_files fs,

       (SELECT tablespace_name,bytes_free,bytes_used

          FROM v$temp_space_header

         GROUP BY tablespace_name,bytes_free,bytes_used) df

 WHERE fs.tablespace_name (+)  = df.tablespace_name

 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used

 ORDER BY 4 DESC;

SELECT df.tablespace_name TABLESPACE, df.total_space TOTAL_SPACE,

fs.free_space FREE_SPACE, df.total_space_mb TOTAL_SPACE_MB,

(df.total_space_mb - fs.free_space_mb) USED_SPACE_MB,

fs.free_space_mb FREE_SPACE_MB,

ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE

FROM (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,

   ROUND(SUM(bytes) / 1048576) TOTAL_SPACE_MB

   FROM dba_data_files

   GROUP BY tablespace_name) df,

  (SELECT tablespace_name, SUM(bytes) FREE_SPACE,

    ROUND(SUM(bytes) / 1048576) FREE_SPACE_MB

    FROM dba_free_space

    GROUP BY tablespace_name) fs

WHERE df.tablespace_name = fs.tablespace_name(+)

ORDER BY fs.tablespace_name;

Manage Oracle Tablespace, Datafile and Tempfile.

Use different views for viewing information about temp files and Datafiles with V$TEMPFILE and DBA_TEMP_FILES views are analogous to the V$DATAFILE and DBA_DATA_FILES views.

Note: Remember we can extend one Datafile up it Max limit around 32GB only, It depends on a block size of oracle.
Default Block Size: 8192(Byte) Means 8Kb, please run this query to cross verify:

SQL> Select (maxblocks*8192)/1024/1024/1024 Size_GB from dba_data_files where rownum=1;


# Tablespace Creation and Resizing Datafile
SQL> col TABLESPACE_NAME format a20;
SQL> col FILE_NAME format a50;
SQL> SELECT TABLESPACE_NAME,FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='USERS';

CREATE TABLESPACE tb_name DATAFILE '/mount1/directory1/directory1/my_data_01.dbf' SIZE 20G;
ALTER TABLESPACE tb_name ADD DATAFILE '/mount1/directory1/directory1/my_data_01.dbf' SIZE 10G;
ALTER DATABASE DATAFILE '/mount1/directory1/directory1/my_data_01.dbf' AUTOEXTEND ON;
ALTER DATABASE DATAFILE '/mount1/directory1/directory1/my_data_01.dbf' RESIZE 24G;


# Temp Tablespace Creation and Managing its Datafile
By default, a single temporary tablespace named TEMP is created for each new Oracle Database installation. You can create additional temporary tablespaces with the CREATE TABLESPACE statement.

SQL> col TABLESPACE_NAME format a20;
SQL> col FILE_NAME format a50;
SQL> SELECT TABLESPACE_NAME,FILE_NAME FROM DBA_TEMP_FILES WHERE TABLESPACE_NAME='TEMP';
SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

CREATE TEMPORARY TABLESPACE lmtemp TEMPFILE '/mount2/directory1/directory1/lmtemp01.dbf' SIZE 15G AUTOEXTEND ON;
ALTER TABLESPACE lmtemp ADD TEMPFILE '/mount2/directory1/directory1/lmtemp02.dbf' SIZE 10G REUSE;
ALTER DATABASE TEMPFILE '/mount2/directory1/directory1/lmtemp02.dbf' RESIZE 26G;
ALTER TABLESPACE TEMP ADD TEMPFILE '/mount2/directory1/directory1/lmtemp03.dbf' SIZE 512M AUTOEXTEND ON NEXT 250M MAXSIZE UNLIMITED;

# Drop Tablespace Datafile along with data.
DROP TABLESPACE users INCLUDING CONTENTS;
DROP TABLESPACE users INCLUDING CONTENTS AND DATAFILES;
ALTER TABLESPACE lmtemp DROP TEMPFILE '/mount2/directory1/directory1/lmtemp02.dbf';
ALTER TABLESPACE tb_name DROP DATAFILE '/mount1/directory1/directory1/my_data_01.dbf';