Vinod S R’s Blog

Normally sequences are not exported if we give partial export.

For such cases we can use sql to generate quries for sequence droping and creating

select ‘drop sequence ‘||sequence_name||’;’ from user_sequences where sequence_name like’SEQ_DB%’;

select ‘create sequence ‘||sequence_name||
‘ minvalue ‘|| MIN_VALUE ||
‘ maxvalue ‘|| MAX_VALUE ||
‘ start with ‘||last_number||
‘ increment by ‘|| INCREMENT_BY ||
‘ cache ‘||CACHE_SIZE ||
decode(ORDER_FLAG,’Y',’ order ‘)||
decode(CYCLE_FLAG,’Y',’ cycle ‘)||
‘;’

In Oracle there is a easy way to track the current queries processed by the database.

Suppose you want to track the Insert queries in your DB .

then for that use this query

select substr(sql_text,instr(sql_text,’INTO “‘),30) table_name,
rows_processed,
round((sysdate-to_date(first_load_time,’yyyy-mm-dd hh24:mi:ss’))*24*60,1) minutes,
trunc(rows_processed/((sysdate-to_date(first_load_time,’yyyy-mm-dd hh24:mi:ss’))*24*60)) rows_per_min
from   sys.v_$sqlarea
where  sql_text like ‘INSERT %INTO “%‘
and  command_type = 2
and  open_versions > 0;

Replace the text in blue color (‘INSERT %INTO “%‘) According to your purpose

Here is the query to get the locked tables in oracle

SELECT l.inst_id,SUBSTR(L.ORACLE_USERNAME,1,8) ORA_USER, SUBSTR(L.SESSION_ID,1,3) SID,
S.serial#,
SUBSTR(O.OWNER||’.'||O.OBJECT_NAME,1,40) OBJECT, P.SPID OS_PID,
DECODE(L.LOCKED_MODE, 0,’NONE’,
1,’NULL’,
2,’ROW SHARE’,
3,’ROW EXCLUSIVE’,
4,’SHARE’,
5,’SHARE ROW EXCLUSIVE’,
6,’EXCLUSIVE’,
NULL) LOCK_MODE
FROM sys.GV_$LOCKED_OBJECT L, DBA_OBJECTS O, sys.GV_$SESSION S, sys.GV_$PROCESS P
WHERE L.OBJECT_ID = O.OBJECT_ID
and l.inst_id = s.inst_id
AND L.SESSION_ID = S.SID
and s.inst_id = p.inst_id
AND S.PADDR = P.ADDR(+)
order by l.inst_id  ;

And to get the details of a particular session given by the sid in the above query use this query

select STATUS ,  PROCESS , PROGRAM , LOGON_TIME  from v$session where sid=<SID>

To get the table space usage in oracle use this query

select    a.TABLESPACE_NAME,
ROUND(a.BYTES/1024000) “Used (MB)”,
ROUND(b.BYTES/1024000) “Free (MB)”,
round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) “% USED”
from
(
select  TABLESPACE_NAME,
sum(BYTES) BYTES
from    dba_data_files
group   by TABLESPACE_NAME
)
a,
(
select  TABLESPACE_NAME,
sum(BYTES) BYTES ,
max(BYTES) largest
from    dba_free_space
group   by TABLESPACE_NAME
)
b
where    a.TABLESPACE_NAME=b.TABLESPACE_NAME
and a.TABLESPACE_NAME like ‘%’
order      by ((a.BYTES-b.BYTES)/a.BYTES) desc ;

If You want to list a particular table space  replace  a.TABLESPACE_NAME like ‘%’ with a.TABLESPACE_NAME like ‘MY_TABLE_SPACE’

To get the temporary tablespace usuage use this query

SELECT tablespace_name, SUM(bytes_used), SUM(bytes_free)
FROM V$temp_space_header
GROUP BY tablespace_name;