OraFAQ Forum: SQL & PL/SQL » table creation

Home » SQL & PL/SQL » SQL & PL/SQL » table creation

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

table creation [message #6516]

Wed, 23 April 2003 01:25

vp
Messages: 25
Registered: January 2002

Junior Member

Hello Friends,

I am creating table scripts using forward engineering of Rational Rose - Data Modeller. I found that it is possible to specify one storage for the table. But unfortunately indexes(created by primary key) is created on the same tablespace and I would like to have them on a seperate tablespace. I am thinking of writing a utility in the end of table creation, to identify all such indexes and drop and recreate on the seperate tablespace. Is there an elegant solution to this...something like modify constaint.

thanks
vp

Report message to a moderator

Re: table creation [message #6524 is a reply to message #6516]

Wed, 23 April 2003 04:30

Keith
Messages: 88
Registered: March 2000

Member

Use the:

alter index <Index_name> rebuild tablespace
;

You should be able to write a procedure along the lines of:

DECLARE
CURSOR get_indexes IS
SELECT index_name
FROM user_indexes
WHERE tablespace_name = '<OLD TABLESPACE>';
l_sqlerrm VARCHAR2(255);
l_sqlcode VARCHAR2(50);
BEGIN

FOR rec IN get_indexes LOOP

EXECUTE IMMEDIATE ('ALTER INDEX '||rec.index_name||' REBUILD TABLESPACE <NEW TABLESPACE>');

END LOOP;

EXCEPTION WHEN OTHERS THEN
-- Whatever error processing you want
l_sqlerrm := SUBSTR(SQLERRM,1,255);
l_sqlcode := SUBSTR(SQLCODE,1,50);
dbms_output.put_line('Error occurred: '||l_sqlcode||' Message: '||l_sqlerrm);
END;
/

For example:

1* SELECT INDEX_NAME, TABLESPACE_NAME FROM USER_INDEXES
krjf@ora92> /

INDEX_NAME TABLESPACE_NAME
------------------------------ ------------------------------
DEPT_PK USERS
EMP_ENAME_IDX USERS
EMP_MGR_DEPTNO_IDX USERS

krjf@ora92>
krjf@ora92> DECLARE
2 CURSOR get_indexes IS
3 SELECT index_name
4 FROM user_indexes
5 WHERE tablespace_name = 'USERS';
6 l_sqlerrm VARCHAR2(255);
7 l_sqlcode VARCHAR2(50);
8 BEGIN
9
10 FOR rec IN get_indexes LOOP
11
12 EXECUTE IMMEDIATE ('ALTER INDEX '||rec.index_name||' REBUILD TABLESPACE XDB');
13
14 END LOOP;
15
16 EXCEPTION WHEN OTHERS THEN
17 -- Whatever error processing you want
18 l_sqlerrm := SUBSTR(SQLERRM,1,255);
19 l_sqlcode := SUBSTR(SQLCODE,1,50);
20 dbms_output.put_line('Error occurred: '||l_sqlcode||' Message: '||l_sqlerrm);
21 END;
22 /

PL/SQL procedure successfully completed.

krjf@ora92>
krjf@ora92> SELECT INDEX_NAME, TABLESPACE_NAME FROM USER_INDEXES
2 ;

INDEX_NAME TABLESPACE_NAME
------------------------------ ------------------------------
DEPT_PK XDB
EMP_ENAME_IDX XDB
EMP_MGR_DEPTNO_IDX XDB

krjf@ora92>

HTH

keith

Report message to a moderator

Re: table creation [message #6526 is a reply to message #6524]

Wed, 23 April 2003 06:47

Keith
Messages: 88
Registered: March 2000

Member

Some of the brackets have been removed when I posted this (since it thinks they're HTML), but you should be able to work it out.

Let me know if not, and I'll repost.

keith

Report message to a moderator