| I want to partition Adv Queue table but I get ORA-02216 [message #121924] |
Wed, 01 June 2005 15:11  |
aymercury
Messages: 3
Registered: June 2005
|
Junior Member |
|
|
I get ORA-02216 error when I run this:
dbms_aqadm.create_queue_table(
queue_table => 'part_tab',
queue_payload_type => 'sys.aq$_jms_text_message',
multiple_consumers => false,
storage_clause => '
TABLESPACE DATA
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
PARTITION BY HASH (MSGID) (
PARTITION P1 TABLESPACE PART_DATA1,
PARTITION P2 TABLESPACE PART_DATA2,
PARTITION P3 TABLESPACE PART_DATA3
)
');
I have other AQueues working, I can create AQ table without partition options. Those tablespaces do exist.
How can I specify partition options for an AQ table?
The documentation explicitly says that AQ table is the same as any other oracle table, and it must be tuned for performance. But I see only one way to tune AQ table is to play with it's physical layout. I'd appreciate any help!
----------------------------------------------------------------
http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10785/perform.htm
Basic Tuning Tips
Oracle Streams AQ table layout is similar to a layout with ordinary database tables and indexes.
Using Storage Parameters
Storage parameters can be specified when creating a queue table using the storage_clause parameter. Storage parameters are inherited by other IOTs and tables created with the queue table. The tablespace of the queue table should have sufficient space to accommodate data from all the objects associated with the queue table. With retention specified, the history table as well as the queue table can grow to be quite big.
I/O Configuration
Because Oracle Streams AQ is very I/O intensive, you will usually need to tune I/O to remove any bottlenecks.
|
|
|
|
| Re: I want to partition Adv Queue table but I get ORA-02216 [message #121936 is a reply to message #121924] |
Wed, 01 June 2005 17:01  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
I don't know anything about AQ, so the following comments are guessing.
What is the exact error message?
What happens if in your storage clause you eliminate all of the pctfree and pctused and other stuff and solely specify the partiitoning and tablespaces for each partition? Let the other stuff inherit from the tablespace level to simplify things.
What happens if you don't specify tablespaces at all, just the multiple partitions, and let them go into default tablespace?
What version of oracle are you running and does it support hash partitions on regular tables? Can you successfully hash partition a regular table?
What happens if you try to make it a range partition instead of hash? How about list partition?
Also, keep in mind that partitioning a table is not a magic solution to get you better performance. You need to have a specific goal purpose that the partition scheme will achieve.
|
|
|
|
| Re: I want to partition Adv Queue table but I get ORA-02216 [message #121938 is a reply to message #121936] |
Wed, 01 June 2005 17:10 |
aymercury
Messages: 3
Registered: June 2005
|
Junior Member |
|
|
| smartin wrote on Wed, 01 June 2005 17:31 |
I don't know anything about AQ, so the following comments are guessing.
What is the exact error message?
What happens if in your storage clause you eliminate all of the pctfree and pctused and other stuff and solely specify the partiitoning and tablespaces for each partition? Let the other stuff inherit from the tablespace level to simplify things.
What happens if you don't specify tablespaces at all, just the multiple partitions, and let them go into default tablespace?
What version of oracle are you running and does it support hash partitions on regular tables? Can you successfully hash partition a regular table?
What happens if you try to make it a range partition instead of hash? How about list partition?
Also, keep in mind that partitioning a table is not a magic solution to get you better performance. You need to have a specific goal purpose that the partition scheme will achieve.
|
OK, one by one... 
ORA-02216: tablespace name expected
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 2333
ORA-06512: at "SYS.DBMS_AQADM", line 58
ORA-06512: at line 2
I tried to specify partitioning only with no success. All those pctfree are fine (without partition, of course).
We run 10.1.0.3 on that particular server. I have "normal" partitioned tables on that box.
I haven't tried other partitioning ways, will give it a shot tomorrow. Meantime I've opened TAR for that issue. Seems like this feature is simply not implemented...
|
|
|
|
| Re: I want to partition Adv Queue table but I get ORA-02216 [message #121940 is a reply to message #121936] |
Wed, 01 June 2005 17:14 |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
I suspect that the interface does not support this.
The documentation for DBMS_AQADM.CREATE_QUEUE_TABLE says this about the "storage_clause" argument:
| Quote: |
The storage parameter is included in the CREATE TABLE statement when the queue table is created. The storage_clause argument can take any text that can be used in a standard CREATE TABLE storage_clause argument.The storage parameter can be made up of any combinations of the following parameters: PCTFREE, PCTUSED, INITRANS, MAXTRANS, TABLEPSACE, LOB, and a table storage clause.
|
Agreed that is a bit confusing because PCTFREE, PCTUSED etc are not normally part of the storage clause, but there is no mention of a partitioning clause.
[Updated on: Wed, 01 June 2005 17:17] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
