Oracle Blog

Saturday, September 5, 2015

How to move AQ Tables

12:08 PM Posted by Yunus SIMSEK No comments
If you need to move Advance Queue tables between table-spaces then you need to follow below 5 steps. 

1- Revoked Enqueu/Deqeueu Privelege from Queue Table. 
2- Stop Queue Table. 
3- Move Queue Table 
4- Grant Enqueue/Dequeu privelege to Queue Table. 
5- Start Queue.


connect / as sysdba 
EXECUTE DBMS_AQADM.REVOKE_QUEUE_PRIVILEGE( privilege => 'ENQUEUE', queue_name => 'APPLSYS.WF_DEFERRED', grantee => 'APPS'); 
EXECUTE DBMS_AQADM.REVOKE_QUEUE_PRIVILEGE( privilege => 'DEQUEUE', queue_name => 'APPLSYS.WF_DEFERRED', grantee => 'APPS'); 

connect applsys/appstra 
set serveroutput on; 
exec dbms_aqadm.stop_queue('APPLSYS.WF_DEFERRED'); 

connect aqmove/aqmove 
set serveroutput on; 
EXEC MOVE_QT_PKG.MOVE_QUEUE_TABLE('APPLSYS','WF_DEFERRED','OLD_TABLESPACE','NEW_TABLESPACE'); 

connect / as sysdba 
EXECUTE DBMS_AQADM.GRANT_QUEUE_PRIVILEGE( privilege => 'ENQUEUE', queue_name => 'APPLSYS.WF_DEFERRED', grantee => 'APPS'); 
EXECUTE DBMS_AQADM.GRANT_QUEUE_PRIVILEGE( privilege => 'DEQUEUE', queue_name => 'APPLSYS.WF_DEFERRED', grantee => 'APPS') 
connect ***/***
exec dbms_aqadm.start_queue('WF_CONTROL'); 

SQL> set linesize 220 
SQL> select a.owner, a.queue_table, b.tablespace_name from dba_queue_tables a, dba_segments b where a.queue_table = b.segment_name and a.owner = b.owner and b.tablespace_name='APPS_TS_QUEUES' order by tablespace_name; 

OWNER QUEUE_TABLE TABLESPACE_NAME 
------------------------------ ------------------------------ ------------------------------ 
APPLSYS WF_CONTROL NEW_TABLESPACE
APPLSYS WF_DEFERRED APPS_TS_QUEUES 
Using above steps will move your Advance Queue tables to different tablespace without an issue. I had an issue and spent weeks to find to solution, solution was simple, stop queue and revoke privilege then move to different tablespace.

if you need to know how to install DBMS_REDEFINITION please follow below instruction. 
Create the aqmove user detailed below or grant the relevant privileges to an existing user that you want to be able to perform the move 
--- Download these 2 packets from MOS(1410195.1) and place to your home directory ----
@move_qt_pkgs 
@move_qt_pkgb.plb

drop user aqmove cascade;
create user aqmove identified by aqmove; 
alter user aqmove default tablespace system;
--Privileges required to use DBMS_REDEFINITION in conjunction with the move package.
grant connect, resource to aqmove; 
grant execute on sys.DBMS_LOB to aqmove;
grant unlimited tablespace to aqmove; 
grant create any table to aqmove; 
grant alter any table to aqmove; 
grant drop any table to aqmove; 
grant lock any table to aqmove; 
grant select any table to aqmove; 
grant create any index to aqmove; 
grant alter any index to aqmove; 
grant create any trigger to aqmove;
grant execute_catalog_role to aqmove; 
grant select_catalog_role to aqmove; 
grant execute on dbms_redefinition to aqmove; 
grant alter session to aqmove;
connect aqmove/aqmove 

--- Execute these packages from your home directory- 

@move_qt_pkgs 
@move_qt_pkgb.plb
Reference :How to perform an Online Move of Advanced Queening Tables using DBMS_REDEFINITION (Doc ID 1410195.1)

Who is using most temp space on Database

11:52 AM Posted by Yunus SIMSEK No comments
Day to day DBA work, I am seeing more and more temp space related issues, here are the some simple queries that can tell you who is eating up temp spaces, and usage of the temp spaces. 

How do you find who using temp space
SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c
WHERE a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
ORDER BY b.tablespace, b.blocks;
Get 10 sessions with largest temp usage
column proginfo format a40
column LASTCALLET format a35
column TABLESPACE format a25
cursor bigtemp_sids is
select * from (
select s.sid,
s.status,
s.sql_hash_value sesshash,
u.SQLHASH sorthash,
s.username,
u.tablespace,
sum(u.blocks*p.value/1024/1024) mbused ,
sum(u.extents) noexts,
nvl(s.module,s.program) proginfo,
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) lastcallet
from v$sort_usage u,
v$session s,
v$parameter p
where u.session_addr = s.saddr
and p.name = 'db_block_size'
group by s.sid,s.status,s.sql_hash_value,u.sqlhash,s.username,u.tablespace,
nvl(s.module,s.program),
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60)
order by 7 desc,3)
where rownum < 11;


Percentage of TEMP Space usage
For dictionary managed temporary tablespace :
select (s.tot_used_blocks/f.total_blocks)*100 as "percent used"
from (select sum(used_blocks) tot_used_blocks from v$sort_segment where tablespace_name='tbsp_name') s, (select sum(blocks) total_blocks from dba_data_files where tablespace_name='tbsp_name') f;

For locally  managed temporary tablespace :
select (s.tot_used_blocks/f.total_blocks)*100 as "percent used" from (select sum(used_blocks) tot_used_blocks from v$sort_segment where tablespace_name='TEMP') s, (select sum(blocks) total_blocks from dba_temp_files where tablespace_name='TEMP') f;