Importing data into user-defined tablespaces

Whenever data is imported from Oracle 8i on Unix into Oracle 8i on Windows 2000 Server, the data seems to be imported into the same tablespace on which the user is created. Actually, it imports the data into the SYSTEM tablespace, which increases in size. Moreover, by importing the data, extents of the tables are also imported. These extents play the major role in increasing data size. By using this tip, data size can be decreased and the tables can be moved into that particular tablespace. You can use the following scripts to bring it in your required tablespace. The last part moves the table from one tablespace to another, as was done in a script submitted by Sameer Wadhwa.

Note that there is a problem with this tip. When I create duplicate tables by selecting the original table, it creates the duplicate table of same structure having the same data, but it does not create the primary key and foreign key constraints. If anybody knows a solution to this, please let me know by emailing the editor at SearchDatabase.com.

Follow these steps. By rebuilding the tables, you'll be able to decrease the size that was occupied by the extents.

1. Get the names of original tables
2. Create duplicate tables
3. Drop original tables
4. Rename duplicate tables as original tables
5. Bring tables in your required tablespace

SPOOL C:MYTS.SQL
SET HEAD OFF
SET FEED OFF

-- CREATE DUPLICATE TABLES
SELECT 'CREATE TABLE '|| TNAME ||'2 AS SELECT * FROM '||TNAME||';' FROM TAB
WHERE TABTYPE='TABLE';


-- DROP ORIGINAL TABLES
SELECT 'DROP TABLE '||SUBSTR(TNAME, 1, LENGTH(TNAME)-1)||';' FROM TAB 
WHERE TABTYPE='TABLE';

-- RENAME DUPLICATE TABLES AS ORIGINAL TABLES
SELECT 'RENAME '||TNAME||' TO '||SUBSTR(TNAME, 1, LENGTH(TNAME)-1)||';' FROM TAB 
WHERE TABTYPE='TABLE';

-- MOVE TABLES FROM ONE TABLESPACE TO OTHER TABLESPACE
SELECT 'ALTER TABLE '||TNAME||' MOVE TABLESPACE [TABLESPACE NAME];' FROM TAB
WHERE TABTYPE='TABLE';

SPOOL OFF
SET HEAD ON
SET FEED ON

Reader Feedback

Doug D. writes: A much better way to do this is to generate a script which generates the following output:

ALTER TABLE TABLE_NAME MOVE ONLINE TABLESPACE ABC;

The above assumes a heap table. This preserves the constraints.

Mark A. writes: This tip is for fixing a situation that the DBA should not have let happen. A better solution is to REVOKE UNLIMITED TABLESPACE from the user and set quota on the allowed tablespaces. This prevents user objects being created in SYSTEM, e.g.:

-- cruser.sql
SET VERIFY OFF
SET FEEDBACK OFF
PROMPT
DEFINE USERNAME=&1
DEFINE TS=&2

PROMPT CREATE USER &&USERNAME DEFAULT TABLESPACE &&TS
CREATE USER &&USERNAME IDENTIFIED BY demo
DEFAULT TABLESPACE &&TS TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON &&TS
/
GRANT CONNECT,RESOURCE TO &&USERNAME
/
REVOKE UNLIMITED TABLESPACE FROM &&USERNAME
/
PROMPT

UNDEFINE USERNAME
UNDEFINE TS
SET VERIFY ON
SET FEEDBACK ON

Jolene S. writes: Don't forget to mention that when you move a table, the indexes become "UNUSABLE" and must be rebuilt and that statistics are lost -- so if you are in CBO, you must analyze the tables.

Brian Pealsand writes: This tip will not work for any and all tables that contain a LONG or LONG RAW column. But then, neither will the suggestions of moving the table with the ALTER TABLE MOVE command. DBAs can go a long way to help themselves if they assign appropriate default tablespaces, revoke UNLIMITED TABLESPACE from users, and assign appropriate quotas. Alternatively, one could create empty tables (the INDEXFILE option of IMP will help generate a script) in the appropriate tablespace and import with the IGNORE=Y option.

Another thing I don't like about this tip is that it creates a duplicate table for no apparently good reason. Why not skip steps 2,3,4 and just go to step 5? Steps 2,3,4 are an exercise in wasting one's time. Not to mention that any indexes, constraints, triggers, and grants on the original table will all be lost once Step 3 is performed.

For More Information

• Feedback: E-mail the editor with your thoughts about this tip.
• More tips: Hundreds of free Oracle tips and scripts.
• Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
• Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
• Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
• Best Web Links: Oracle tips, tutorials, and scripts from around the Web.