Win a copy of Head First Agile this week in the Agile forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

Allow Oracle INDEX tablespace to be created in different tablespace  RSS feed

 
Migrated From Jforum.net
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
would be great if we could add the ability to load the indicies into a different tablespace from the data. The CREATE INDEX statements would become:


CREATE INDEX idx_foo ON jforum_foo(foo_id)
TABLESPACE $INDEXSPACE;

I'll be happy to mod the Oracle SQL file if you can provide me the syntax for the substitution.
[originally posted on jforum.net by thx1855]
 
Migrated From Jforum.net
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The .sql script should run fine without any modifications needed by the user.

I mean, you can make the modifications to the script, but it must run fine if the user doesn't make any changes to it manually.

Rafael
[originally posted on jforum.net by Rafael Steil]
 
Migrated From Jforum.net
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It depends on the configuration. Our Oracle instances are configured to not allow index creation in the data tablespaces. The JForum installation script errored out when it tried to create the index in the "wrong" tablespace.

I believe there are a couple ways to solve this.

1) Add the explicit TABLESPACE param to the CREATE INDEX statements

or

2) add the USING INDEX TABLESPACE param to the CREATE TABLE statements

I chose option #1 'cause it was easy =)
[originally posted on jforum.net by Anonymous]
 
Migrated From Jforum.net
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
anon post above is mine. forgot to log in...
[originally posted on jforum.net by thx1855]
 
Migrated From Jforum.net
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm not 100% familiar with Oracle.. Can you please provide some real db_struct.sql example file, so I - or anyone else - can digg on it?

Thanks a lot

Rafael
[originally posted on jforum.net by Rafael Steil]
 
Migrated From Jforum.net
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You could use either:
when creating a table or
when creating the index

Adding the TABLESPACE to the CREATE INDEX is probably the easiest and most understandable way to do it. Let's see if any other Oracle DBA types chime in =)
[originally posted on jforum.net by thx1855]
 
Migrated From Jforum.net
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you, I'll try that here.

Rafael
[originally posted on jforum.net by Rafael Steil]
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!