Home » RDBMS Server » Server Utilities » changing tablesapce of tables &urgent
| changing tablesapce of tables &urgent [message #73054] |
Sat, 24 January 2004 19:18  |
Susan
Messages: 102
Registered: October 2001
|
Senior Member |
|
|
Dear Sir,
i had query about database admin level.Can u ask to ur friend as DBA.
our company created a db around 4months before.The Big mistake done is they created the tables in system tablesapce..SO as u know the system becoming slow when data is increasing so they till now they r adding datafiles but now its slow.
there r 400 tables.they did'nt partioned large tables.So how to change the tablespace without invalidating the table's data and dependents(views ,indexes,constraints).I used export in user level if its table level some of the objects will be invalidated.whether we can use move command with alter table command.
i am not sure .there r 8 outlets accesing this database.So whether have to shutdown the database or can do it online.
whether any problems for applcations when accessing the data
pls give a solution
susan
|
|
|
|
|
|
|
|
| Re: changing tablesapce of tables &urgent [message #73057 is a reply to message #73056] |
Sun, 25 January 2004 16:36  |
 |
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Did you click on the link that I provided and read the entire discussion? I believe it answers your questions. However, please see the simplified example below. It demonstrates how to move the tablespaces. In the example below, I have put the table in one tablespace and the index in another. If you run the moveall.sql script in the link that I provided, it creates a tmp.sql script that does this and more for every table in the schema. You can edit the tmp.sql script and make any changes before runing it. In the example below, I have demonstrated that the constraints remain in effect.
test@ORA92> CREATE TABLE test_table
2 (col1 NUMBER,
3 col2 NUMBER,
4 CONSTRAINT test_table_col1_pk PRIMARY KEY (col1),
5 CONSTRAINT test_talble_col2_ck CHECK (col2 < 10));
Table created.
test@ORA92> SELECT tablespace_name FROM user_tables WHERE table_name = 'TEST_TABLE';
TABLESPACE_NAME
------------------------------
SYSTEM
test@ORA92> SELECT index_name, tablespace_name FROM user_indexes WHERE table_name = 'TEST_TABLE';
INDEX_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEST_TABLE_COL1_PK SYSTEM
test@ORA92> SELECT constraint_name, status FROM user_constraints WHERE table_name = 'TEST_TABLE';
CONSTRAINT_NAME STATUS
------------------------------ --------
TEST_TALBLE_COL2_CK ENABLED
TEST_TABLE_COL1_PK ENABLED
test@ORA92> ALTER TABLE test_table MOVE TABLESPACE users;
Table altered.
test@ORA92> ALTER INDEX test_table_col1_pk REBUILD TABLESPACE indx;
Index altered.
test@ORA92> SELECT tablespace_name FROM user_tables WHERE table_name = 'TEST_TABLE';
TABLESPACE_NAME
------------------------------
USERS
test@ORA92> SELECT index_name, tablespace_name FROM user_indexes WHERE table_name = 'TEST_TABLE';
INDEX_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEST_TABLE_COL1_PK INDX
test@ORA92> SELECT constraint_name, status FROM user_constraints WHERE table_name = 'TEST_TABLE';
CONSTRAINT_NAME STATUS
------------------------------ --------
TEST_TALBLE_COL2_CK ENABLED
TEST_TABLE_COL1_PK ENABLED
test@ORA92> INSERT INTO test_table VALUES (1, 2);
1 row created.
test@ORA92> INSERT INTO test_table VALUES (2, 3);
1 row created.
test@ORA92> INSERT INTO test_table VALUES (1, 4);
INSERT INTO test_table VALUES (1, 4)
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.TEST_TABLE_COL1_PK) violated
test@ORA92> INSERT INTO test_table VALUES (1, 11);
INSERT INTO test_table VALUES (1, 11)
*
ERROR at line 1:
ORA-02290: check constraint (TEST.TEST_TALBLE_COL2_CK) violated
|
|
|
|
|
|
| Re: changing tablesapce of tables &urgent [message #73061 is a reply to message #73058] |
Mon, 26 January 2004 02:09 |
|
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
It would be best to convert your long columns to clob columns, then do your moving. There are various ways to convert your longs to clobs. In the example below, I have done so in a manner that leaves your constraints on other columns intact. There is also a separate command for moving the clob. And you need to make sure you rebuild your index tablespace after you move your clob to it. Please see the example below.
test@ORA92> CREATE TABLE test_table
2 (col1 NUMBER,
3 col2 NUMBER,
4 col3 LONG,
5 CONSTRAINT test_table_col1_pk PRIMARY KEY (col1),
6 CONSTRAINT test_talble_col2_ck CHECK (col2 < 10))
7 /
Table created.
test@ORA92> INSERT INTO test_table VALUES (1, 2, 'This is test record 1')
2 /
1 row created.
test@ORA92> COMMIT
2 /
Commit complete.
test@ORA92> CREATE TABLE test_table_clob
2 (col1,
3 col2,
4 col3)
5 AS
6 SELECT col1,
7 col2,
8 TO_LOB (col3)
9 FROM test_table
10 /
Table created.
test@ORA92> ALTER TABLE test_table DROP COLUMN col3
2 /
Table altered.
test@ORA92> ALTER TABLE test_table ADD (col3 CLOB)
2 /
Table altered.
test@ORA92> UPDATE test_table tt
2 SET tt.col3 =
3 (SELECT ttc.col3
4 FROM test_table_clob ttc
5 WHERE tt.col1 = ttc.col1
6 AND tt.col2 = ttc.col2)
7 /
1 row updated.
test@ORA92> DROP TABLE test_table_clob
2 /
Table dropped.
test@ORA92> DESC test_table
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
COL1 NOT NULL NUMBER
COL2 NUMBER
COL3 CLOB
test@ORA92> SELECT tablespace_name FROM user_tables WHERE table_name = 'TEST_TABLE'
2 /
TABLESPACE_NAME
------------------------------
SYSTEM
test@ORA92> SELECT index_name, tablespace_name, index_type
2 FROM user_indexes WHERE table_name = 'TEST_TABLE'
3 /
INDEX_NAME TABLESPACE_NAME INDEX_TYPE
------------------------------ ------------------------------ ---------------------------
SYS_IL0000034703C00003$$ SYSTEM LOB
TEST_TABLE_COL1_PK SYSTEM NORMAL
test@ORA92> SELECT constraint_name, status FROM user_constraints WHERE table_name = 'TEST_TABLE'
2 /
CONSTRAINT_NAME STATUS
------------------------------ --------
TEST_TALBLE_COL2_CK ENABLED
TEST_TABLE_COL1_PK ENABLED
test@ORA92> ALTER TABLE test_table MOVE TABLESPACE users
2 /
Table altered.
test@ORA92> ALTER TABLE test_table MOVE LOB (col3) STORE AS (TABLESPACE indx)
2 /
Table altered.
test@ORA92> ALTER INDEX test_table_col1_pk REBUILD TABLESPACE indx
2 /
Index altered.
test@ORA92> SELECT tablespace_name FROM user_tables WHERE table_name = 'TEST_TABLE'
2 /
TABLESPACE_NAME
------------------------------
USERS
test@ORA92> SELECT index_name, tablespace_name, index_type
2 FROM user_indexes WHERE table_name = 'TEST_TABLE'
3 /
INDEX_NAME TABLESPACE_NAME INDEX_TYPE
------------------------------ ------------------------------ ---------------------------
SYS_IL0000034703C00003$$ INDX LOB
TEST_TABLE_COL1_PK INDX NORMAL
test@ORA92> SELECT constraint_name, status FROM user_constraints WHERE table_name = 'TEST_TABLE'
2 /
CONSTRAINT_NAME STATUS
------------------------------ --------
TEST_TALBLE_COL2_CK ENABLED
TEST_TABLE_COL1_PK ENABLED
test@ORA92> INSERT INTO test_table VALUES (2, 3, 'This is test record 2')
2 /
1 row created.
test@ORA92> INSERT INTO test_table VALUES (1, 4, 'This is test record 3')
2 /
INSERT INTO test_table VALUES (1, 4, 'This is test record 3')
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.TEST_TABLE_COL1_PK) violated
test@ORA92> INSERT INTO test_table VALUES (3, 11, 'This is test record 4')
2 /
INSERT INTO test_table VALUES (3, 11, 'This is test record 4')
*
ERROR at line 1:
ORA-02290: check constraint (TEST.TEST_TALBLE_COL2_CK) violated
test@ORA92> COLUMN col3 FORMAT A30
test@ORA92> SELECT * FROM test_table
2 /
COL1 COL2 COL3
---------- ---------- ------------------------------
1 2 This is test record 1
2 3 This is test record 2
|
|
|
|
| Re: changing tablesapce of tables &urgent [message #73062 is a reply to message #73061] |
Mon, 26 January 2004 19:47 |
Susan
Messages: 102
Registered: October 2001
|
Senior Member |
|
|
hi
Really really thanks for ur reply its worked in test database.The problem is there
r 400 tables in production database.is there any procedure to done it
short time or parallelly or wheteher to do one after one.Whether its affect transactions in progress
by susan
|
|
|
|
| Re: changing tablesapce of tables &urgent [message #73067 is a reply to message #73062] |
Tue, 27 January 2004 12:41 |
|
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I was surprised to find that in Oracle 9i, changing a long to a clob is now as simple as:
ALTER TABLE table_name MODIFY (long_column_name CLOB);
I have written the following two procedures that should be all you need to change all of the user's longs to clobs and move all of the tables to the users tablespace and all of the clobs and indexes to the index tablespace:
-- convert longs to clobs:
CREATE OR REPLACE PROCEDURE longs_to_clobs
AS
BEGIN
FOR rec IN
(SELECT table_name, column_name FROM user_tab_columns WHERE data_type = 'LONG')
LOOP
EXECUTE IMMEDIATE 'ALTER TABLE ' || rec.table_name
|| ' MODIFY (' || rec.column_name || ' CLOB)';
END LOOP;
END;
/
SHOW ERRORS
EXECUTE longs_to_clobs
--
--
-- move tablespaces:
CREATE OR REPLACE PROCEDURE move_tablespaces
(p_table_tablespace_name IN user_tablespaces.tablespace_name%TYPE,
p_index_tablespace_name IN user_tablespaces.tablespace_name%TYPE)
AS
BEGIN
FOR rec IN
(SELECT table_name FROM user_tables)
LOOP
EXECUTE IMMEDIATE 'ALTER TABLE ' || rec.table_name
|| ' MOVE TABLESPACE ' || p_table_tablespace_name;
END LOOP;
FOR rec IN
(SELECT table_name, column_name FROM user_tab_columns WHERE data_type = 'CLOB')
LOOP
EXECUTE IMMEDIATE 'ALTER TABLE ' || rec.table_name
|| ' MOVE LOB (' || rec.column_name || ') STORE AS (TABLESPACE '
|| p_index_tablespace_name || ')';
END LOOP;
FOR rec IN
(SELECT index_name FROM user_indexes WHERE index_type <> 'LOB')
LOOP
EXECUTE IMMEDIATE 'ALTER INDEX ' || rec.index_name
|| ' REBUILD TABLESPACE ' || p_index_tablespace_name;
END LOOP;
END move_tablespaces;
/
SHOW ERRORS
EXECUTE move_tablespaces ('USERS', 'INDX')
Here is a demonstration of the above two procedures. I only used one table, but it will do this for all of the tables:
test@ORA92> -- create tables and data for testing
test@ORA92> -- and display column types, tablespaces and constraints:
test@ORA92> CREATE TABLE test_table
2 (number_col1 NUMBER,
3 varchar2_col2 VARCHAR2(10),
4 col3 LONG,
5 date_col4 DATE,
6 CONSTRAINT test_table_col1_pk PRIMARY KEY (number_col1),
7 CONSTRAINT test_talble_col2_ck CHECK (varchar2_col2 < 'Z'))
8 /
Table created.
test@ORA92> INSERT INTO test_table
2 VALUES (1, 'TEST1', 'Test record 1 col3', SYSDATE)
3 /
1 row created.
test@ORA92> COMMIT
2 /
Commit complete.
test@ORA92> DESC test_table
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
NUMBER_COL1 NOT NULL NUMBER
VARCHAR2_COL2 VARCHAR2(10)
COL3 LONG
DATE_COL4 DATE
test@ORA92> SELECT tablespace_name FROM user_tables WHERE table_name = 'TEST_TABLE'
2 /
TABLESPACE_NAME
------------------------------
SYSTEM
test@ORA92> SELECT index_name, tablespace_name, index_type
2 FROM user_indexes WHERE table_name = 'TEST_TABLE'
3 /
INDEX_NAME TABLESPACE_NAME INDEX_TYPE
------------------------------ ------------------------------ ---------------------------
TEST_TABLE_COL1_PK SYSTEM NORMAL
test@ORA92> SELECT constraint_name, status FROM user_constraints WHERE table_name = 'TEST_TABLE'
2 /
CONSTRAINT_NAME STATUS
------------------------------ --------
TEST_TALBLE_COL2_CK ENABLED
TEST_TABLE_COL1_PK ENABLED
test@ORA92> --
test@ORA92> --
test@ORA92> -- convert longs to clobs:
test@ORA92> CREATE OR REPLACE PROCEDURE longs_to_clobs
2 AS
3 BEGIN
4 FOR rec IN
5 (SELECT table_name, column_name FROM user_tab_columns WHERE data_type = 'LONG')
6 LOOP
7 EXECUTE IMMEDIATE 'ALTER TABLE ' || rec.table_name
8 || ' MODIFY (' || rec.column_name || ' CLOB)';
9 END LOOP;
10 END;
11 /
Procedure created.
test@ORA92> SHOW ERRORS
No errors.
test@ORA92> EXECUTE longs_to_clobs
PL/SQL procedure successfully completed.
test@ORA92> --
test@ORA92> --
test@ORA92> -- move tablespaces:
test@ORA92> CREATE OR REPLACE PROCEDURE move_tablespaces
2 (p_table_tablespace_name IN user_tablespaces.tablespace_name%TYPE,
3 p_index_tablespace_name IN user_tablespaces.tablespace_name%TYPE)
4 AS
5 BEGIN
6 FOR rec IN
7 (SELECT table_name FROM user_tables)
8 LOOP
9 EXECUTE IMMEDIATE 'ALTER TABLE ' || rec.table_name
10 || ' MOVE TABLESPACE ' || p_table_tablespace_name;
11 END LOOP;
12 FOR rec IN
13 (SELECT table_name, column_name FROM user_tab_columns WHERE data_type = 'CLOB')
14 LOOP
15 EXECUTE IMMEDIATE 'ALTER TABLE ' || rec.table_name
16 || ' MOVE LOB (' || rec.column_name || ') STORE AS (TABLESPACE '
17 || p_index_tablespace_name || ')';
18 END LOOP;
19 FOR rec IN
20 (SELECT index_name FROM user_indexes WHERE index_type <> 'LOB')
21 LOOP
22 EXECUTE IMMEDIATE 'ALTER INDEX ' || rec.index_name
23 || ' REBUILD TABLESPACE ' || p_index_tablespace_name;
24 END LOOP;
25 END move_tablespaces;
26 /
Procedure created.
test@ORA92> SHOW ERRORS
No errors.
test@ORA92> EXECUTE move_tablespaces ('USERS', 'INDX')
PL/SQL procedure successfully completed.
test@ORA92> --
test@ORA92> --
test@ORA92> -- display column types, tablespaces and constraints
test@ORA92> -- and test constraints and check data:
test@ORA92> DESC test_table
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
NUMBER_COL1 NOT NULL NUMBER
VARCHAR2_COL2 VARCHAR2(10)
COL3 CLOB
DATE_COL4 DATE
test@ORA92> SELECT tablespace_name FROM user_tables WHERE table_name = 'TEST_TABLE'
2 /
TABLESPACE_NAME
------------------------------
USERS
test@ORA92> SELECT index_name, tablespace_name, index_type
2 FROM user_indexes WHERE table_name = 'TEST_TABLE'
3 /
INDEX_NAME TABLESPACE_NAME INDEX_TYPE
------------------------------ ------------------------------ ---------------------------
SYS_IL0000035043C00003$$ INDX LOB
TEST_TABLE_COL1_PK INDX NORMAL
test@ORA92> SELECT constraint_name, status FROM user_constraints WHERE table_name = 'TEST_TABLE'
2 /
CONSTRAINT_NAME STATUS
------------------------------ --------
TEST_TALBLE_COL2_CK ENABLED
TEST_TABLE_COL1_PK ENABLED
test@ORA92> INSERT INTO test_table (number_col1, varchar2_col2, col3, date_col4)
2 VALUES (2, 'TEST2', 'Test record 2 col3', SYSDATE)
3 /
1 row created.
test@ORA92> INSERT INTO test_table (number_col1, varchar2_col2, col3, date_col4)
2 VALUES (1, 'TEST3', 'Test record 3 col3', SYSDATE)
3 /
INSERT INTO test_table (number_col1, varchar2_col2, col3, date_col4)
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.TEST_TABLE_COL1_PK) violated
test@ORA92> INSERT INTO test_table (number_col1, varchar2_col2, col3, date_col4)
2 VALUES (4, 'Z4', 'Test record 4 col3', SYSDATE)
3 /
INSERT INTO test_table (number_col1, varchar2_col2, col3, date_col4)
*
ERROR at line 1:
ORA-02290: check constraint (TEST.TEST_TALBLE_COL2_CK) violated
test@ORA92> COLUMN col3 FORMAT A30
test@ORA92> SELECT * FROM test_table
2 /
NUMBER_COL1 VARCHAR2_C COL3 DATE_COL4
----------- ---------- ------------------------------ ---------
1 TEST1 Test record 1 col3 27-JAN-04
2 TEST2 Test record 2 col3 27-JAN-04
test@ORA92> SPOOL OFF
|
|
|
|
| Thanks lot [message #73074 is a reply to message #73067] |
Thu, 29 January 2004 04:55 |
Susan
Messages: 102
Registered: October 2001
|
Senior Member |
|
|
hi barbara
Really really tahnkfull for ur sincere help and good
patience cos i am an beginer in dba ur skills r really outstanding
with regards
susan
|
|
|
|
Goto Forum:
Current Time: Mon Jul 01 15:44:07 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
