参考:http://www.eygle.com/archives/2006/09/move_queue_table_out_system.html
今天一个兄弟咨询如果将复制队列表移出系统(SYSTEM)表空间。
实验过程:
set linesize 120
col segment_name for a30
col segment_type for a20
col tablespace_name for a20
select segment_name,segment_type,tablespace_name,bytes/1024
from dba_segments where segment_name like '%DEF$_%';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES/1024
------------------------------ -------------------- -------------------- ----------
DEF$_AQCALL TABLE SYSTEM 64
DEF$_AQERROR TABLE SYSTEM 64
DEF$_ERROR TABLE SYSTEM 64
DEF$_DESTINATION TABLE SYSTEM 64
DEF$_CALLDEST TABLE SYSTEM 64
DEF$_DEFAULTDEST TABLE SYSTEM 64
DEF$_LOB TABLE SYSTEM 64
DEF$_TEMP$LOB TABLE SYSTEM 64
DEF$_PROPAGATOR TABLE SYSTEM 64
DEF$_ORIGIN TABLE SYSTEM 64
DEF$_PUSHED_TRANSACTIONS TABLE SYSTEM 64
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES/1024
------------------------------ -------------------- -------------------- ----------
DEF$_TRANORDER INDEX SYSTEM 64
DEF$_ERROR_PRIMARY INDEX SYSTEM 64
DEF$_DESTINATION_PRIMARY INDEX SYSTEM 64
DEF$_CALLDEST_PRIMARY INDEX SYSTEM 64
DEF$_CALLDEST_N2 INDEX SYSTEM 64
DEF$_DEFAULTDEST_PRIMARY INDEX SYSTEM 64
DEF$_LOB_PRIMARY INDEX SYSTEM 64
DEF$_LOB_N1 INDEX SYSTEM 64
DEF$_PROPAGATOR_PRIMARY INDEX SYSTEM 64
DEF$_PUSHED_TRAN_PRIMARY INDEX SYSTEM 64
21 rows selected.
SQL> desc dbms_aqadm
PROCEDURE ADD_ALIAS_TO_LDAP
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ALIAS VARCHAR2 IN
OBJ_LOCATION VARCHAR2 IN
PROCEDURE ADD_CONNECTION_TO_LDAP
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CONNECTION VARCHAR2 IN
HOST VARCHAR2 IN
PORT BINARY_INTEGER IN
SID VARCHAR2 IN
DRIVER VARCHAR2 IN DEFAULT
TYPE BINARY_INTEGER IN DEFAULT
PROCEDURE ADD_CONNECTION_TO_LDAP
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CONNECTION VARCHAR2 IN
JDBC_STRING VARCHAR2 IN
USERNAME VARCHAR2 IN DEFAULT
PASSWORD VARCHAR2 IN DEFAULT
TYPE BINARY_INTEGER IN DEFAULT
PROCEDURE ADD_SUBSCRIBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
QUEUE_NAME VARCHAR2 IN
SUBSCRIBER AQ$_AGENT IN
RULE VARCHAR2 IN DEFAULT
TRANSFORMATION VARCHAR2 IN DEFAULT
QUEUE_TO_QUEUE BOOLEAN IN DEFAULT
DELIVERY_MODE BINARY_INTEGER IN DEFAULT
PROCEDURE ALTER_AQ_AGENT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
AGENT_NAME VARCHAR2 IN
CERTIFICATE_LOCATION VARCHAR2 IN DEFAULT
ENABLE_HTTP BOOLEAN IN DEFAULT
ENABLE_SMTP BOOLEAN IN DEFAULT
ENABLE_ANYP BOOLEAN IN DEFAULT
PROCEDURE ALTER_PROPAGATION_SCHEDULE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
QUEUE_NAME VARCHAR2 IN
DESTINATION VARCHAR2 IN DEFAULT
DURATION NUMBER IN DEFAULT
NEXT_TIME VARCHAR2 IN DEFAULT
LATENCY NUMBER IN DEFAULT
DESTINATION_QUEUE VARCHAR2 IN DEFAULT
PROCEDURE ALTER_QUEUE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
QUEUE_NAME VARCHAR2 IN
MAX_RETRIES NUMBER IN DEFAULT
RETRY_DELAY NUMBER IN DEFAULT
RETENTION_TIME NUMBER IN DEFAULT
AUTO_COMMIT BOOLEAN IN DEFAULT
COMMENT VARCHAR2 IN DEFAULT
PROCEDURE ALTER_QUEUE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
QUEUE_TABLE VARCHAR2 IN
COMMENT VARCHAR2 IN DEFAULT
PRIMARY_INSTANCE BINARY_INTEGER IN DEFAULT
SECONDARY_INSTANCE BINARY_INTEGER IN DEFAULT
PROCEDURE ALTER_SUBSCRIBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
QUEUE_NAME VARCHAR2 IN
SUBSCRIBER AQ$_AGENT IN
RULE VARCHAR2 IN
PROCEDURE ALTER_SUBSCRIBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
QUEUE_NAME VARCHAR2 IN
SUBSCRIBER AQ$_AGENT IN
RULE VARCHAR2 IN
TRANSFORMATION VARCHAR2 IN
FUNCTION AQ$_PROPAQ RETURNS DATE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
JOB NUMBER IN
FUNCTION AQ$_PROPAQ RETURNS DATE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
JOB NUMBER IN
NEXT_DATE DATE IN
QNAME VARCHAR2 IN
SCHEMA VARCHAR2 IN
DESTINATION VARCHAR2 IN DEFAULT
TOID_CHAR VARCHAR2 IN DEFAULT
VERSION_CHAR VARCHAR2 IN DEFAULT
START_TIME VARCHAR2 IN
DURATION VARCHAR2 IN DEFAULT
NEXT_TIME VARCHAR2 IN DEFAULT
LATENCY VARCHAR2 IN DEFAULT
PROCEDURE CREATE_AQ_AGENT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
AGENT_NAME VARCHAR2 IN
CERTIFICATE_LOCATION VARCHAR2 IN DEFAULT
ENABLE_HTTP BOOLEAN IN DEFAULT
ENABLE_SMTP BOOLEAN IN DEFAULT
ENABLE_ANYP BOOLEAN IN DEFAULT
PROCEDURE CREATE_NP_QUEUE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
QUEUE_NAME VARCHAR2 IN
MULTIPLE_CONSUMERS BOOLEAN IN DEFAULT
COMMENT VARCHAR2 IN DEFAULT
PROCEDURE CREATE_QUEUE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
QUEUE_NAME VARCHAR2 IN
QUEUE_TABLE VARCHAR2 IN
QUEUE_TYPE BINARY_INTEGER IN DEFAULT
MAX_RETRIES NUMBER IN DEFAULT
RETRY_DELAY NUMBER IN DEFAULT
RETENTION_TIME NUMBER IN DEFAULT
DEPENDENCY_TRACKING BOOLEAN IN DEFAULT
COMMENT VARCHAR2 IN DEFAULT
AUTO_COMMIT BOOLEAN IN DEFAULT
PROCEDURE CREATE_QUEUE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
QUEUE_TABLE VARCHAR2 IN
QUEUE_PAYLOAD_TYPE VARCHAR2 IN
STORAGE_CLAUSE VARCHAR2 IN DEFAULT
SORT_LIST VARCHAR2 IN DEFAULT
MULTIPLE_CONSUMERS BOOLEAN IN DEFAULT
MESSAGE_GROUPING BINARY_INTEGER IN DEFAULT
COMMENT VARCHAR2 IN DEFAULT
AUTO_COMMIT BOOLEAN IN DEFAULT
PRIMARY_INSTANCE BINARY_INTEGER IN DEFAULT
SECONDARY_INSTANCE BINARY_INTEGER IN DEFAULT
COMPATIBLE VARCHAR2 IN DEFAULT
NON_REPUDIATION BINARY_INTEGER IN DEFAULT
SECURE BOOLEAN IN DEFAULT
PROCEDURE DEL_ALIAS_FROM_LDAP
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ALIAS VARCHAR2 IN
PROCEDURE DEL_CONNECTION_FROM_LDAP
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CONNECTION VARCHAR2 IN
PROCEDURE DISABLE_DB_ACCESS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
AGENT_NAME VARCHAR2 IN
DB_USERNAME VARCHAR2 IN
PROCEDURE DISABLE_PROPAGATION_SCHEDULE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
QUEUE_NAME VARCHAR2 IN
DESTINATION VARCHAR2 IN DEFAULT
DESTINATION_QUEUE VARCHAR2 IN DEFAULT
PROCEDURE DROP_AQ_AGENT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
AGENT_NAME VARCHAR2 IN
PROCEDURE DROP_QUEUE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
QUEUE_NAME VARCHAR2 IN
AUTO_COMMIT BOOLEAN IN DEFAULT
PROCEDURE DROP_QUEUE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
QUEUE_TABLE VARCHAR2 IN
FORCE BOOLEAN IN DEFAULT
AUTO_COMMIT BOOLEAN IN DEFAULT
PROCEDURE ENABLE_DB_ACCESS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
AGENT_NAME VARCHAR2 IN
DB_USERNAME VARCHAR2 IN
PROCEDURE ENABLE_JMS_TYPES
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
QUEUE_TABLE VARCHAR2 IN
PROCEDURE ENABLE_PROPAGATION_SCHEDULE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
QUEUE_NAME VARCHAR2 IN
DESTINATION VARCHAR2 IN DEFAULT
DESTINATION_QUEUE VARCHAR2 IN DEFAULT
PROCEDURE GET_PROP_SEQNO
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
QID BINARY_INTEGER IN
DQNAME VARCHAR2 IN
DBNAME VARCHAR2 IN
SEQ BINARY_INTEGER OUT
PROCEDURE GET_TYPE_INFO
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SCHEMA VARCHAR2 IN
QNAME VARCHAR2 IN
GETTDS BOOLEAN IN
RC BINARY_INTEGER OUT
TOID RAW OUT
VERSION NUMBER OUT
TDS LONG RAW OUT
QUEUE_STYLE VARCHAR2 OUT
NETWORK_NAME VARCHAR2 OUT
PROCEDURE GET_TYPE_INFO
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SCHEMA VARCHAR2 IN
QNAME VARCHAR2 IN
GETTDS BOOLEAN IN
RC BINARY_INTEGER OUT
TOID RAW OUT
VERSION NUMBER OUT
TDS LONG RAW OUT
PROCEDURE GET_WATERMARK
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
WMVALUE NUMBER OUT
PROCEDURE GRANT_QUEUE_PRIVILEGE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
PRIVILEGE VARCHAR2 IN
QUEUE_NAME VARCHAR2 IN
GRANTEE VARCHAR2 IN
GRANT_OPTION BOOLEAN IN DEFAULT
PROCEDURE GRANT_SYSTEM_PRIVILEGE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
PRIVILEGE VARCHAR2 IN
GRANTEE VARCHAR2 IN
ADMIN_OPTION BOOLEAN IN DEFAULT
PROCEDURE GRANT_TYPE_ACCESS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
USER_NAME VARCHAR2 IN
PROCEDURE MIGRATE_QUEUE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
QUEUE_TABLE VARCHAR2 IN
COMPATIBLE VARCHAR2 IN
PROCEDURE NONREPUDIATE_RECEIVER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
QUEUE_NAME VARCHAR2 IN
MSGID RAW IN
RCVER_INFO AQ$_AGENT IN
SIGNATURE AQ$_SIG_PROP OUT
PAYLOAD STANDARD OUT
PROCEDURE NONREPUDIATE_RECEIVER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
QUEUE_NAME VARCHAR2 IN
MSGID RAW IN
RCVER_INFO AQ$_AGENT IN
SIGNATURE AQ$_SIG_PROP OUT
PAYLOAD RAW OUT
PROCEDURE NONREPUDIATE_SENDER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
QUEUE_NAME VARCHAR2 IN
MSGID RAW IN
SENDER_INFO AQ$_AGENT IN
SIGNATURE AQ$_SIG_PROP OUT
PAYLOAD STANDARD OUT
PROCEDURE NONREPUDIATE_SENDER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
QUEUE_NAME VARCHAR2 IN
MSGID RAW IN
SENDER_INFO AQ$_AGENT IN
SIGNATURE AQ$_SIG_PROP OUT
PAYLOAD RAW OUT
PROCEDURE PURGE_QUEUE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
QUEUE_TABLE VARCHAR2 IN
PURGE_CONDITION VARCHAR2 IN
PURGE_OPTIONS RECORD IN
BLOCK BOOLEAN IN
DELIVERY_MODE BINARY_INTEGER IN
FUNCTION QUEUE_SUBSCRIBERS RETURNS TABLE OF AQ$_AGENT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
QUEUE_NAME VARCHAR2 &
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7882490/viewspace-496085/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7882490/viewspace-496085/