How to export sequences using sql

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 ‘)||
‘;’

How to track queries in oracle ..

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

How to Get the Locked Tables List In Oracle

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>

How to get the TableSpace usage in Oracle

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;