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
