Tablespace scripts




Check Table space
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;


Query to report tablespaces having less than 20% free space left considering auto-extensible db-files:- SQL>  select tablespace_name,(round(sum(Free_Space_MB),2)) "Free_Space_MB",(round(sum(Total_Space_MB),2))  "Total_MB",(round(100*sum(Free_Space_MB)/sum(Total_Space_MB),2)) "% Free" from (select a.tablespace_name,a.file_name,(((a.maxbytes-a.bytes) + nvl(b.Free_Space_B,0))/1024/1024) Free_Space_MB,(maxbytes/1024/1024)  Total_Space_MB from (select * from dba_data_files where autoextensible='YES' and increment_by != 0 and maxbytes >= bytes) a left outer join (select FILE_ID,(sum(bytes)) Free_Space_B from dba_free_space group by FILE_ID) b on a.file_id=b.file_id UNION select a.tablespace_name,a.file_name,(nvl(b.Free_Space_B,0)/1024/1024) Free_Space_MB,(bytes/1024/1024) Total_Space_MB from (select * from dba_data_files where autoextensible='NO' or (autoextensible='YES' and maxbytes < bytes )) a left outer join  (select FILE_ID,(sum(bytes)) Free_Space_B from dba_free_space group by FILE_ID) b on a.file_id=b.file_id) where tablespace_name in (select tablespace_name from dba_tablespaces where contents not in ('UNDO','TEMPORARY')) group by tablespace_name having (round(100*sum(Free_Space_MB)/sum(Total_Space_MB),2)) < 20;


 DB size with Tablespace

set head on
set pagesize 30
select NVL(tablespace_name,'** Total **') "Tablespace Name",
sum("allocated") "Allocated(M)",
sum("used") "Used(M)",
sum("free") "Free(M)",
sum(df_cnt) "#(File)"
from
(
select a.tablespace_name, trunc(b.assigned/1048576) "allocated",
trunc((b.assigned-a.free)/1048576) "used",
trunc(a.free/1048576) "free",
df_cnt
from
(
select tablespace_name, sum(bytes) free
from dba_free_space
group by tablespace_name ) a,
(
select tablespace_name, sum(bytes) assigned, count(*) df_cnt
from dba_data_files
group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name
UNION ALL
SELECT tablespace_name||'[TMP]', trunc(sum(bytes)/1048576), null, null, count(*) df_cnt
from dba_temp_files
group by tablespace_name
)
group by rollup(tablespace_name) ;



Table Truncated
The below given table Truncated. Please check it once.
 SQL> truncate table APPLSYS.FND_LOG_MESSAGES;
Table truncated.

ADD More SPACE
 ALTER TABLESPACE temp2 ADD TEMPFILE '/sap_temp/IPLUAT/db/apps_st/data/temp2_02.dbf' SIZE 5000M;

Add tablespace on QA:-
login sys as sysdba
Tablespace name- APPS_TS_TX_DATA

SQL> alter tablespace APPS_TS_TX_DATA add datafile '/idata1/IFFQAEBS1/data1/a_txn_data47.dbf' size 10G;

Tablespace name- APPS_TS_TX_IDX.

/idata1/IFFQAEBS1/data1/a_txn_ind19.dbf

SQL> select * from dba_data_files where tablespace_name like '%APPS_TS_TX_IDX'

SQL>  select * from dba_data_files where tablespace_name like '%APPS_TS_TX_IDX' order by file_id;

SQL> alter tablespace APPS_TS_TX_IDX add datafile '/idata1/IFFQAEBS1/data1/a_txn_ind21.dbf' size 4G;

Tablespace altered.
SQL> Alter tablespace APPS_TS_TX_IDX add datafile '/idata/IFFQAEBS/data/a_txn_ind02.dbf' size 10G;

Tablespace altered.
 
 Increase the Size of Tablespace

If you want to increase the size of tablespace, its so simple.
You can do this by enterprise manager console. Increase the size of datafile for particular tablespace.

OR
For Example

ALTER DATABASE
DATAFILE ‘/u03/oradata/ userdata02. dbf’
RESIZE 200M;

If you don’t have any free space on that partition of disk then you can add another
datafile on the other partition  for particular tablespace.

For example-

ALTER TABLESPACE app_data
ADD DATAFILE ‘/u01/oradata/ userdata03. dbf’
SIZE 200M;

Now you can insert data with in this tablespace.

SQL> select * from dba_tablespaces;

SQL> select * from user_tablespaces;

SQL> select * from dba_data_files;

SQL> select * from dba_free_space;

SQL> desc dba_free_space;

 Name              Null?    Type
 ----------------- -------- ------------
 TABLESPACE_NAME            VARCHAR2(30)
 FILE_ID                    NUMBER
 BLOCK_ID                   NUMBER
 BYTES                      NUMBER
 BLOCKS                     NUMBER
 RELATIVE_FNO               NUMBER




Check each of the Data, Index and temporary table-spaces for extend and blocks Allocation details.

SQL> SET LINES 1000
SELECT SEGMENT_NAME,TABLESPACE_NAME,EXTENTS,BLOCKS
FROM DBA_SEGMENTS;




SQL> SELECT SEGMENT_NAME,TABLESPACE_NAME,EXTENTS,BLOCKS
FROM DBA_SEGMENTS WHERE TABLESPACE_NAME=’STAR01D’;


SQL> SELECT THREAD#, SEQUENCE#, ARCHIVED, STATUS FROM V$LOG;
SQL> SELECT THREAD#, SEQUENCE#, ARCHIVED, STATUS FROM V$LOG
WHERE STATUS= 'CURRENT'
Table and tablespace location

Finding out who owns a table and what tablespace it is in is a pretty
common need of the DBA. In this query, we use the dba_tables view to
find the owner and tablespace name of the EMP table.

SQL>  select owner, table_name, tablespace_name
  2   from dba_tables
  3  where table_name='EMP';

OWNER                TABLE_NAME           TABLESPACE_NAME
-------------------- -------------------- -------------
SCOTT                EMP                  USERS
POLL                 EMP                  USERS


SQL>  select tablespace_name from dba_tablespaces where tablespace_name='APPS_PS_SX_DATA';

TABLESPACE_NAME
------------------------------
APPS_PS_SX_DATA

As we can see from this query, we have two tables called EMP, owned by two different users (Scott and Poll). Both tables are contained in the USERS tablespace.
A good exercise for you might be to try to join this query with a view like DBA_EXTENTS and figure out just how big these tables are allocated.

Check location of   DATA_FILE

SQL> select name from V$datafile;

NAME
--------------------------------------------------------------------------------
/u01/sonu/data/system01.dbf
/u01/sonu/data/system02.dbf
/u01/sonu/data/system03.dbf
/u01/sonu/data/system04.dbf
/u01/sonu/data/system05.dbf
/u01/sonu/data/system06.dbf
/u01/sonu/data/system07.dbf
/u01/sonu/data/system08.dbf
/u01/sonu/data/system09.dbf



 Check Data Transfer:-

select s.sid, s.username, s.osuser, to_char(sysdate-(last_call_et/24/60/60),'HH24:MI:SS') last_call, sq.address,
  sq.disk_reads / decode(sq.executions,0,1,sq.executions) DiskReadperExec,
  sq.buffer_gets / decode(sq.executions,0,1,sq.executions) buffer_gets,
  sq.executions, sq.ROWS_PROCESSED,
  decode(sq.executions,0,0,sq.CPU_TIME/sq.EXECUTIONS/1000000) cpu,
  decode(sq.executions,0,0,sq.ELAPSED_TIME/sq.EXECUTIONS/1000000) elapsed,
  decode(s.audsid,userenv('sessionid'),'show sessions',sq.sql_text) sql_text
from v$sql sq, v$session s
where s.status='ACTIVE'
--and sq.executions > 0
and s.sql_hash_value = sq.hash_value
and s.sql_Address = sq.address
/*and s.SQL_CHILD_NUMBER = sq.child_number*/
and s.user# = sq.parsing_user_id
order by elapsed ;
 TABLE LIST 

select owner, table_name, num_rows, LAST_ANALYZED
From dba_tables
where table_name in (
'AP_INVOICES_ALL', 'AP_INVOICE_DISTRIBUTIONS_ALL', 'AR_PAYMENT_SCHEDULES_ALL',
'RA_CUSTOMER_TRX_ALL', 'RA_CUSTOMER_TRX_LINES_ALL' ,
'HZ_PARTIES', 'HZ_CUST_ACCOUNTS',
'AS_SALES_LEADS', 'AS_ACCESSES_ALL_ALL',
'BOM_STRUCTURES_B', 'BOM_COMPONENTS_B',
'CS_INCIDENTS_ALL_B',
'FA_ADJUSTMENTS', 'FA_DEPRN_DETAIL', 'FA_DEPRN_SUMMARY',
'FND_USERS',
'GL_JE_HEADERS', 'GL_JE_LINES',
'MRP_GROSS_REQUIREMENTS', 'MRP_RECOMMENDATIONS', 'MRP_FULL_PEGGING',
'MRP_BOM_COMPONENTS', 'MTL_MATERIAL_TRANSACTIONS',
'MTL_TRANSACTION_ACCOUNTS', 'MTL_SYSTEM_ITEMS_B',
'HR_ORGANIZATION_INFORMATION', 'HR_OPERATING_UNITS',
'MTL_PARAMETERS',
'OE_ORDER_HEADERS_ALL', 'OE_ORDER_LINES_ALL',
'PO_HEADERS_ALL', 'PO_LINES_ALL', 'PO_VENDORS',
'WF_ITEM_ACTIVITY_STATUSES', 'WF_ITEM_ATRIBUTE_VALUES',
'WF_NOTIFICATIONS', 'WF_NOTIFICATION_ATTRIBUTES' ,
'WSH_DELIVERY_DETAILS' , 'WSH_DELIVERY_ASSIGNMENTS',
'WSH_NEW_DELIVERIES', 'WSH_DELIVERY_LEGS',
'WSH_TRIP_STOPS', 'WSH_TRIPS' )
order by table_name ;
Check Table locked

Query to find locked tables in 11g.


Run following from sysdba:

select owner||'.'||object_name obj
,oracle_username||' ('||s.status||')' oruser
,os_user_name osuser
,machine computer
,l.process unix
,''''||s.sid||','||s.serial#||'''' ss
,r.name rs
,to_char(s.logon_time,'yyyy/mm/dd hh24:mi:ss') time
from v$locked_object l
,dba_objects o
,v$session s
,v$transaction t
,v$rollname r
where l.object_id = o.object_id
and s.sid=l.session_id
and s.taddr=t.addr
and t.xidusn=r.usn
order by osuser, ss, obj


To kill a perticular session :
alter system kill session '<>';
e.g.
alter system kill session '101,1120';


Steps for releasing lock on a table:

 <APPS>

Finding Locks  select session_id "sid",SERIAL# "Serial",  substr(object_name,1,20) "Object",  substr(os_user_name,1,10) "Terminal",  substr(oracle_username,1,10) "Locker",  nvl(lockwait,'active') "Wait",  decode(locked_mode,  2, 'row share',  3, 'row exclusive',  4, 'share',  5, 'share row exclusive',  6, 'exclusive', 'unknown') "Lockmode",  OBJECT_TYPE "Type"  FROM  SYS.V_$LOCKED_OBJECT A,  SYS.ALL_OBJECTS B,  SYS.V_$SESSION c  WHERE  A.OBJECT_ID = B.OBJECT_ID AND  C.SID = A.SESSION_ID  ORDER BY 1 ASC, 5 Desc  Finding Blocking sessions :  select l1.sid, ' IS BLOCKING ', l2.sid  from v$lock l1, v$lock l2 where l1.block =1 and l2.request > 0  and l1.id1=l2.id1 and l1.id2=l2.id2  select s1.username '@' s1.machine ' ( SID=' s1.sid ' ) is blocking '  s2.username '@' s2.machine ' ( SID=' s2.sid ' ) ' AS blocking_status  from v$lock l1, v$session s1, v$lock l2, v$session s2 where s1.sid=l1.sid and s2.sid=l2.sid  and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 ; Sessions with highest CPU consumption :  SELECT s.sid, s.serial#, p.spid as "OS PID",s.username, s.module, st.value/100 as "CPU sec"  FROM v$sesstat st, v$statname sn, v$session s, v$process p  WHERE sn.name = 'CPU used by this session' -- CPU  AND st.statistic# = sn.statistic#  AND st.sid = s.sid  AND s.paddr = p.addr  AND s.last_call_et <> (SYSDATE - 240/1440) -- sessions logged on within 4 hours  ORDER BY st.value;  Sessions with the highest time for a certain wait  : SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, se.time_waited  FROM v$session_event se, v$session s, v$process p  WHERE se.event = '&event_name'  AND s.last_call_et <> (SYSDATE - 240/1440) -- sessions logged on within 4 hours  AND se.sid = s.sid  AND s.paddr = p.addr  ORDER BY se.time_waited;  Sessions with highest DB Time usage : SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, st.value/100 as "DB Time (sec)"  , stcpu.value/100 as "CPU Time (sec)", round(stcpu.value / st.value * 100,2) as "% CPU"  FROM v$sesstat st, v$statname sn, v$session s, v$sesstat stcpu, v$statname sncpu, v$process p  WHERE sn.name = 'DB time' -- CPU  AND st.statistic# = sn.statistic#  AND st.sid = s.sid  AND sncpu.name = 'CPU used by this session' -- CPU  AND stcpu.statistic# = sncpu.statistic#  AND stcpu.sid = st.sid  AND s.paddr = p.addr  AND s.last_call_et <> (SYSDATE - 240/1440) -- sessions logged on within 4 hours  AND st.value > 0;      Step1:To verify the lock object Here is the import query: --------------------------------------------------------------- SELECT o.owner, o.object_name, o.object_type, o.last_ddl_time, o.status, l.session_id, l.oracle_username, l.locked_mode FROM dba_objects o, gv$locked_object l WHERE o.object_id = l.object_id; Step 2: Find the serial# for the sessions holding the lock: SQL> select SERIAL# from v$session where SID=667; SERIAL# ---------- 21091 SQL> alter system kill session '667,21091'; System altered.

 This is to get the pkg-body definition from database select TEXT from dba_source where name=upper('xxiff_om_orders_conv_pkg') and owner='APPS' and LINE <10000  and TYPE='PACKAGE BODY';






This is to get the pkg definition from database select TEXT from dba_source where name=upper('xxiff_om_orders_conv_pkg') and owner='APPS' and LINE <10000  and TYPE='PACKAGE BODY';

Before patching - check the invalids and copy paste the output in PAL ( invalids - tab) set pages 199 set lines 199 col OBJECT_NAME for a30 select OWNER, OBJECT_NAME, OBJECT_TYPE, LAST_DDL_TIME, CREATED from dba_objects where status='INVALID';

Package Object Scripts:- tauseef scripts

set pagesize 0 set trimspool on spool invp.lst select 'Alter ' || decode(Object_type,'PACKAGE BODY','PACKAGE',Object_type ) ||' ' ||'"'||object_name||'"' || ' Compile ' ||decode(Object_type,'PACKAGE BODY','Body;',';' ) from user_objects WHERE STATUS = 'INVALID' order by decode(Object_type, 'PACKAGE BODY','8', 'PACKAGE','7','FUNCTION','6','PROCEDURE','5', 'VIEW','4','9'||Object_type ) / spool off @invp.lst



FNDOPP Manager Log file =========================== SELECT fcpp.concurrent_request_id req_id, fcp.node_name, fcp.logfile_name FROM fnd_conc_pp_actions fcpp, fnd_concurrent_processes fcp WHERE fcpp.processor_id = fcp.concurrent_process_id AND fcpp.action_type = 6 AND fcpp.concurrent_request_id = &&Request_id


 Find TABLE with SPACE

SQL> select TABLESPACE_NAME,(TABLESPACE_SIZE)/(1024*1024*1024) SIZE_IN_GB,(ALLOCATED_SPACE)/(1024*1024*1024) ALLOCATED_IN_GB,(FREE_SPACE)/(1024*1024*1024) FREE_IN_GB FROM dba_temp_free_space;

TABLESPACE_NAME                SIZE_IN_GB ALLOCATED_IN_GB FREE_IN_GB
------------------------------ ---------- --------------- ----------
TEMP1                                  12      1.81445313 11.0576172
TEMP2                                  40       .00390625 39.9960938

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

TABLESPACE_NAME                SUM(BYTES/1024/1024)
------------------------------ --------------------
APPS_UNDOTS1                                  49004

SQL> select tablespace_name,sum(BYTES/1024/1024) from dba_free_space where tablespace_name like 'APPS_DEM1' group by tablespace_name;

TABLESPACE_NAME                SUM(BYTES/1024/1024)
------------------------------ --------------------
APPS_DEM1                                     44371


How to find FND-LOG with Concurrent Request id <10543095>

spool /tmp/10543095.xls <EXCEL format>

select log_sequence, module,message_text
from fnd_log_messages
where transaction_context_id in (select transaction_context_id
from fnd_log_transaction_context
where transaction_id = 10543095)
order by log_sequence;


How to Use Spool
 
SQL> SET LINESIZE 999 VERIFY OFF FEEDBACK OFF PAGESIZE 50000 SQL > SET MARKUP HTML ON ENTMAP ON SPOOL ON PREFORMAT OFF
SQL> spool /tmp/sonu.xls
Run scripts
SQL> spool off 




 
-- list all tablespaces with their associated files, the 
-- tablespace's allocated space, free space, and the 
-- next free extent:
 
clear breaks
SET linesize 130
SET pagesize 60
break ON tablespace_name skip 1
col tablespace_name format a15
col file_name format a50
col tablespace_kb heading 'TABLESPACE|TOTAL KB'
col kbytes_free heading 'TOTAL FREE|KBYTES'
 
SELECT dd.tablespace_name tablespace_name, dd.file_name file_name, dd.bytes/1024 TABLESPACE_KB, SUM(fs.bytes)/1024 KBYTES_FREE, MAX(fs.bytes)/1024 NEXT_FREE
FROM sys.dba_free_space fs, sys.dba_data_files dd
WHERE dd.tablespace_name = fs.tablespace_name
AND dd.file_id = fs.file_id
GROUP BY dd.tablespace_name, dd.file_name, dd.bytes/1024
ORDER BY dd.tablespace_name, dd.file_name;
 
 
 
-- list datafiles, tablespace names, and size in MB:
 
col file_name format a50
col tablespace_name format a10
 
SELECT file_name, tablespace_name, ROUND(bytes/1024000) MB
FROM dba_data_files
ORDER BY 1;
 
 
 
-- list tablespaces, size, free space, and percent free
-- query originally developed by Michael Lehmann 
 
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;

No comments:

ORA-01552: cannot use system rollback segment for non-system tablespace 'TEMP'

 ORA-01552: cannot use system rollback segment for non-system tablespace "string" Cause: Used the system rollback segment for non...