• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Tim Cooke
  • Junilu Lacar
Sheriffs:
  • Rob Spoor
  • Devaka Cooray
  • Jeanne Boyarsky
Saloon Keepers:
  • Jesse Silverman
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
  • Tim Holloway
Bartenders:
  • Jj Roberts
  • Al Hobbs
  • Piet Souris

Allow Oracle INDEX tablespace to be created in different tablespace

 
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thank you, I'll try that here.

Rafael
[originally posted on jforum.net by Rafael Steil]
 
reply
    Bookmark Topic Watch Topic
  • New Topic