Home » Developer & Programmer » Designer » Tables and Indexes in Separate Tablespaces - How To ????
Tables and Indexes in Separate Tablespaces - How To ???? [message #90521] Sun, 18 January 2004 00:13 Go to next message
SCP
Messages: 21
Registered: June 2003
Junior Member
I have a single script file which has scripts for table creation and index creation.The table creation script contains script for Primary key generation.

But when I run my script file all the tables and indexes including primary key will be created in one tablespace.

The problem is, all the tables should be created in one tablespace and all indexes including primary key should be in another tablespace.

How to create separate scripts for tables and other for indexes.

Thanks in Advance.
Re: Tables and Indexes in Separate Tablespaces - How To ???? [message #90525 is a reply to message #90521] Mon, 19 January 2004 13:58 Go to previous messageGo to next message
alfa
Messages: 2
Registered: December 2003
Junior Member
You have to add the parameter
TABLESPACE tablespacename to each CREATE TABLE and CREATE INDEX. The primary key index will end up in the SYSTEM (If I remember correctly) tablespace: You can't control it.

For more on design, there are free articles at
http://www.databasedesign-resource.com
Re: Tables and Indexes in Separate Tablespaces - How To ???? [message #90526 is a reply to message #90521] Wed, 21 January 2004 06:04 Go to previous messageGo to next message
Steve Watts
Messages: 4
Registered: January 2004
Junior Member
Here's an example....

Drop Table MM_DbStats
/

Create Table MM_DbStats
(
dbName VarChar2(9) Not Null /* Database name */
, runDate Date Not Null /* Date statistics where gathered */
, Ts VarChar2(32) Not Null /* Tablespace name */
, TotalMb Number Not Null /* Total size of tablespace */
, FreeMb Number Not Null /* Free space of tablespace */
, Pct_Free Number Not Null /* % Free */
, MaxMb Number Not Null /* Largest chunk free */
, Status VarChar2(8) Not Null /* What is the status of this t/s? */
)
TableSpace Users
Storage
(
Initial 5M
Next 5M
PctIncrease 0
)
PctFree 0
PctUsed 99
/

Alter Table MM_DbStats
Add Constraint MM_DBStats_Pkey
Primary Key
(
dbName
, Ts
, runDate
)
Using Index PctFree 0 TableSpace Users
/
Re: Tables and Indexes in Separate Tablespaces - How To ???? [message #90527 is a reply to message #90521] Thu, 22 January 2004 10:40 Go to previous messageGo to next message
billh
Messages: 35
Registered: August 1999
Member
This is a very simple operation. Create the table without any primary key. Then alter the table to add a primary key constraint. The following code is an example:

create table tabx (
col1 varchar2(3),
col2 varchar2(3) )
tablespace s_data ;

alter table tabx
add constraint pk_col1 primary key (col1)
using index tablespace s_indx ;

the tablespace names can be whatever you want. You can add appropriate storage parameters, or perhaps use locally managed tablespaces and you don't need storage parameters.

if you are calling generic scripts to build the tables and primary keys you can set the tablespace names to be substitution variables.

good luck
Re: Tables and Indexes in Separate Tablespaces - How To ???? [message #90536 is a reply to message #90521] Tue, 27 January 2004 13:42 Go to previous messageGo to next message
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
Senior Member
I like to create the table and all its constraints in one shot, e.g:

def index_tspace = USERS

CREATE TABLE customers
( cus_id NUMBER(10) CONSTRAINT cus_pk PRIMARY KEY USING INDEX TABLESPACE &&index_tspace
, cus_name VARCHAR2(45) NOT NULL CONSTRAINT cus_uk UNIQUE USING INDEX TABLESPACE &&index_tspace )
/

CREATE TABLE orders
( ord_id NUMBER(10) CONSTRAINT ord_pk PRIMARY KEY USING INDEX TABLESPACE &&index_tspace
, ord_cus_id CONSTRAINT ord_cus_fk REFERENCES customers ON DELETE CASCADE
, ord_date DATE DEFAULT SYSDATE NOT NULL )
/
Re: Tables and Indexes in Separate Tablespaces - How To ? [message #90542 is a reply to message #90521] Fri, 06 February 2004 13:15 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Having tables and indexes in separate tablespaces is becoming a bit of an old-wives tale. Unless you actually achieve more spread I/O than using a single tablespace then you don't achieve anything. Assuming your datafiles belonging to your tablespaces are on physically different drives, then having tables&indexes spread amoungst tablespaces could benefit you. Remember though that putting all table/indexes A-M in one thablespace and N-Z in another achieves the same result. If you have a large machine using SAN storage, then even different mount points at the OS level may reside on the same physical drive - you need to check with your SAN admin...
Re: Tables and Indexes in Separate Tablespaces - How To ? [message #90543 is a reply to message #90542] Fri, 06 February 2004 13:19 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
If you don't want to re-write your DDL, you can always move your indexes and/or tables to other tablespaces.

Note - if you move a table - you MUST rebuild the indexes too. Useful script from asktom.oracle.com:
Moveall.sql
set echo off

column order_col1 noprint
column order_col2 noprint

set heading off
set verify off
set feedback off
set echo off

spool tmp.sql

select decode( segment_type, 'TABLE', 
                       segment_name, table_name ) order_col1,
       decode( segment_type, 'TABLE', 1, 2 ) order_col2,
      'alter ' || segment_type || ' ' || segment_name ||
      decode( segment_type, 'TABLE', ' move ', ' rebuild ' ) || 
      chr(10) ||
      ' tablespace &1 ' || chr(10) ||
      ' storage ( initial ' || initial_extent || ' next ' || 
        next_extent || chr(10) ||
      ' minextents ' || min_extents || ' maxextents ' || 
        max_extents || chr(10) ||
      ' pctincrease ' || pct_increase || ' freelists ' || 
        freelists || ');'
  from user_segments, 
       (select table_name, index_name from user_indexes )
 where segment_type in ( 'TABLE', 'INDEX' )
   and segment_name = index_name (+)
 order by 1, 2
/

spool off

set heading on
set verify on
set feedback on
set echo on

REM UNCOMMENT TO AUTO RUN the generated commands
REM ELSE edit tmp.sql, modify as needed and run it
REM @tmp

 --- eof ---- 
Previous Topic: 9i installation
Next Topic: Designer 20 - Server Model
Goto Forum:
  


Current Time: Wed Jul 08 18:39:43 CDT 2020