Category: SQL Scripts For DBA

How to find the PID of the process using a specific port?

# lsof -i :9933
COMMAND     PID   USER   FD   TYPE     DEVICE SIZE/OFF NODE NAME
oracle_51 51639 oracle    6u  IPv4 3233899771      0t0  UDP localhost.localdomain:9933
# netstat -nlp|grep 9933
udp   1053952      0 127.0.0.1:9933          0.0.0.0:*                           51639/oracleTESTDB
# fuser -v -n udp 9933
                     USER        PID ACCESS COMMAND
9933/udp:            oracle    51639 F.... oracle_51639_TESTDB

Check Session id(SID) and SQL statement from OS process id(PID) in Oracle

SELECT b.spid,
a.sid,
a.serial#,
a.username,
a.osuser
FROM v$session a, v$process b
WHERE a.paddr = b.addr AND b.spid = '&spid'
ORDER BY b.spid

-- Check SQL statement associated with Process id in Oracle

SELECT RPAD('USERNAME : ' || s.username, 80) ||
RPAD('OSUSER : ' || s.osuser, 80) ||
RPAD('PROGRAM : ' || s.program, 80) ||
RPAD('SPID : ' || p.spid, 80) ||
RPAD('SID : ' || s.sid, 80) ||
RPAD('SERIAL# : ' || s.serial#, 80) ||
RPAD('MACHINE : ' || s.machine, 80) ||
RPAD('TERMINAL : ' || s.terminal, 80)
--RPAD('SQL TEXT : ' || q.sql_text, 80)
FROM v$session s ,v$process p ,v$sql q
WHERE s.paddr = p.addr AND s.sql_address = q.address AND s.sql_hash_value = q.hash_value
AND p.spid = '&spid'

How to Monitor Undo Tablespace Usage and the Free Space

  1. To check the current size of the Undo tablespace:
SQL>  select sum(a.bytes)/1024/1024/1024  as undo_size_GB, c.tablespace_name 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# group by c.tablespace_name;

UNDO_SIZE_GB TABLESPACE_NAME
------------ ------------------------------
  31.9990234 UNDOTBS1
  31.9990234 UNDOTBS2


2. To check the free space (unallocated) space within Undo tablespace:

SQL> select sum(bytes)/1024/1024/1024 "GB",  tablespace_name from dba_free_space where tablespace_name like 'UNDO%' group by  tablespace_name;

        GB TABLESPACE_NAME
---------- ------------------------------
22.1591797 UNDOTBS1
31.1994629 UNDOTBS2

3.To Check the space available within the allocated Undo tablespace:

SQL>  select tablespace_name, sum(blocks)*8/1024/1024 reusable_space_gb from dba_undo_extents where status='EXPIRED' group by tablespace_name;

TABLESPACE_NAME                REUSABLE_SPACE_GB
------------------------------ -----------------
UNDOTBS1                               9.3729248
UNDOTBS2                              .749023438


4. To Check the space allocated in the Undo tablespace:

SQL>  select tablespace_name, sum(blocks)*8/1024/1024 space_in_use_gb from dba_undo_extents where status IN ('ACTIVE','UNEXPIRED') group by tablespace_name;

TABLESPACE_NAME                SPACE_IN_USE_GB
------------------------------ ---------------
UNDOTBS1                            .437988281
UNDOTBS2                            .041503906

 Alternatively, below one SQL can be used as well:

col TABLESPACE_NAME format a10
col USER_SZ_GB format 99.999
col FREE_GB format 999.99
col USER_SZ_GB format 999.99
col REUSABLE_SPACE_GB format 999.99
col ALLOCATED_GB format 999.99
col TOTAL format 999.99

with free_sz as ( select tablespace_name, sum(f.bytes)/1048576/1024 free_gb from dba_free_space f group by tablespace_name ) , a as ( select tablespace_name , sum(case when status = 'EXPIRED' then blocks end)*8/1048576 reusable_space_gb , sum(case when status in ('ACTIVE', 'UNEXPIRED') then blocks end)*8/1048576 allocated_gb from dba_undo_extents where status in ('ACTIVE', 'EXPIRED', 'UNEXPIRED') group by tablespace_name ) , undo_sz as ( select tablespace_name, df.user_bytes/1048576/1024 user_sz_gb from dba_tablespaces ts join dba_data_files df using (tablespace_name) where ts.contents = 'UNDO' and ts.status = 'ONLINE' ) select tablespace_name, user_sz_gb, free_gb, reusable_space_gb, allocated_gb , free_gb + reusable_space_gb + allocated_gb total from undo_sz join free_sz using (tablespace_name) join a using (tablespace_name) ;

TABLESPACE USER_SZ_GB FREE_GB REUSABLE_SPACE_GB ALLOCATED_GB   TOTAL
---------- ---------- ------- ----------------- ------------ -------
UNDOTBS1        32.00   22.16              9.39          .44   32.00
UNDOTBS2        32.00   31.20               .76          .04   32.00

How to Create SQL Plan Baseline from AWR

In another post How to Create SQL Plan Baseline from Cursor Cache. Here we will demonstrate how to create SQL plan baseline from AWR.

By using the SQL in Find Oracle SQL Execution History Details, we get the best execution plan for sql_id=’6fqb4wf9sgfyv’ and plan_hash_value= 815656277.

Create STS (  SQL Tuning Set )

 BEGIN  
  DBMS_SQLTUNE.CREATE_SQLSET (  
     sqlset_name  => 'STS_6fqb4wf9sgfyv',  
     description  => 'SQL Tuning Set for 6fqb4wf9sgfyv');  
 END;

Load the Good Plan into STS

DECLARE  
  cur sys_refcursor;  
 BEGIN  
  OPEN cur FOR  
   SELECT VALUE(P)  
   FROM TABLE(   dbms_sqltune.select_workload_repository(begin_snap=>101300,end_snap=>101304,basic_filter=>'sql_id = ''6fqb4wf9sgfyv''',attribute_list=>'ALL')  
        ) p;  
    DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'STS_6fqb4wf9sgfyv', populate_cursor=>cur);  
  CLOSE cur;  
 END;  

  /
PL/SQL procedure successfully completed.

Check the Good SQL Loaded into STS

SQL>  select OWNER,NAME,DESCRIPTION,STATEMENT_COUNT from DBA_SQLSET where OWNER='SYS' and NAME='STS_6fqb4wf9sgfyv';

OWNER                          NAME
------------------------------ ------------------------------
DESCRIPTION                                                                                                                                                                                                                                  STATEMENT_COUNT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------
SYS                            STS_6fqb4wf9sgfyv
SQL Tuning Set for 6fqb4wf9sgfyv                                                                                                                                                                                                                   1

SQL> select * from table(dbms_xplan.display_sqlset('STS_6fqb4wf9sgfyv','6fqb4wf9sgfyv'));
...
..
.

Load the Plan From STS

SQL> DECLARE
 good_plans pls_integer;
 BEGIN
  good_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
   sqlset_name => 'STS_6fqb4wf9sgfyv',
   basic_filter=>'plan_hash_value = ''815656277'''
   );
 END;   

 /

PL/SQL procedure successfully completed.

Check the Plan Loaded Successfully

SQL> select SQL_HANDLE,PLAN_NAME,ENABLED,ACCEPTED,FIXED,REPRODUCED,AUTOPURGE,OPTIMIZER_COST , EXECUTIONS,ELAPSED_TIME,CPU_TIME from DBA_SQL_PLAN_BASELINES where SQL_TEXT like 'SELECT O.ORDER_ID, O.ORDER_RE%';

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX REP AUT OPTIMIZER_COST EXECUTIONS ELAPSED_TIME   CPU_TIME
------------------------------ ------------------------------ --- --- --- --- --- -------------- ---------- ------------ ----------
SQL_4e7889c88773e98f           SQL_PLAN_4wy49t23r7ucgbe7808e2 YES YES NO  YES YES         229777          1     61311779   53542413

SQL>  SELECT * FROM   TABLE(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'SQL_PLAN_4wy49t23r7ucgbe7808e2'));

SQL>  SELECT * FROM   TABLE(DBMS_XPLAN.display_sql_plan_baseline(sql_handle=>'SQL_4e7889c88773e98f'));

How to Move LOBSEGMENT LOBINDEX to Another Tablespace

Check LOB current tablespace

SQL> select owner, segment_name, segment_type, tablespace_name , bytes from dba_segments where owner='USER1';

OWNER    SEGMENT_NAME                SEGMENT_TYPE       TABLESPACE_NAME    BYTES
-------- --------------------------- ------------------ ----------------- ----------
USER1    SYS_IL0000057639C00010$$    LOBINDEX           TEMPDATA	       65536
USER1    SYS_LOB0000057639C00010$$   LOBSEGMENT         TEMPDATA	    12713984
USER1    SYS_IL0000057685C00008$$    LOBINDEX           TEMPDATA	       65536
USER1    SYS_LOB0000057685C00008$$   LOBSEGMENT         TEMPDATA	     2228224
USER1    SYS_IL0000057693C00008$$    LOBINDEX           TEMPDATA	       65536
USER1    SYS_LOB0000057693C00008$$   LOBSEGMENT         TEMPDATA	      131072

6 rows selected.

Generate moving LObs sql 

SQL> select 'alter table  '||owner||'.'||table_name||' move lob ('||column_name||') store as (tablespace USER1) ;' from dba_lobs where  owner='USER1';

'ALTERTABLE'||OWNER||'.'||TABLE_NAME||'MOVELOB('||COLUMN_NAME||')STOREAS(TABLESPACE USER1;'
---------------------------------------------------------------------------------------------
alter table  USER1.TABLE1 move lob (DATA) store as (tablespace USER1);
alter table  USER1.TABLE2 move lob (DATA) store as (tablespace USER1);
alter table  USER1.TABLE3 move lob (DATA) store as (tablespace USER1);

move lobs into new tablespace

SQL>alter table  USER1.TABLE1 move lob (DATA) store as (tablespace USER1);
Table altered.

SQL>alter table  USER1.TABLE2 move lob (DATA) store as (tablespace USER1);
Table altered.

SQL>alter table  USER1.TABLE3 move lob (DATA) store as (tablespace USER1);
Table altered.

check lobs moved into new tablespace

SQL> select owner, segment_name, segment_type, tablespace_name , bytes from dba_segments where owner='USER1';

OWNER    SEGMENT_NAME                SEGMENT_TYPE       TABLESPACE_NAME    BYTES
-------- --------------------------- ------------------ ----------------- ----------
USER1    SYS_IL0000057639C00010$$    LOBINDEX           USER1	       65536
USER1    SYS_LOB0000057639C00010$$   LOBSEGMENT         USER1	    12713984
USER1    SYS_IL0000057685C00008$$    LOBINDEX           USER1	       65536
USER1    SYS_LOB0000057685C00008$$   LOBSEGMENT         USER1	     2228224
USER1    SYS_IL0000057693C00008$$    LOBINDEX           USER1	       65536
USER1    SYS_LOB0000057693C00008$$   LOBSEGMENT         USER1	      131072

6 rows selected.

rebuild UNUSABLE indxes

SQL>  select 'alter index '||owner||'.'||INDEX_NAME||' rebuild;' from dba_indexes where owner='USER1' and STATUS='UNUSABLE';

'ALTERINDEX'||OWNER||'.'||INDEX_NAME||'REBUILD;'
------------------------------------------------------
alter index USER1.idx_customers rebuild;
alter index USER1.idx_prods rebuild;
alter index USER1.idx_orders rebuild;
....
..
.

SQL>alter index USER1.idx_customers rebuild;
Index altered.

SQL>alter index USER1.idx_prods rebuild;
Index altered.

SQL>alter index USER1.idx_orders rebuild;
Index altered.

Please note LOBINDEX will be automatically relocated onto the same new tablespace as LOBSEGMENT, so there is no need to run SQL to relocate LOBINDEX . Otherwise the error will occur.

How to Check Oracle Database Table and Index Fragmentation

The following posts demonstrated how to check fragmentations in PostgreSQL:
How to Identify Fragmentation in PostgreSQL RDS
Code to Report Table Index Schema or Database Fragmentation in PostgreSQL

This post will show how to check table and index fragmentations in Oracle database.

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe