Subject: MOVING TABLES FROM ONE TABLESPACE TO ANOTHER IN ORACLE7
Author: RADRANLY
Last Revision Date: 06 March 1995
MOVING TABLES FROM ONE TABLESPACE TO ANOTHER IN ORACLE7
-------------------------------------------------------
This bulletin describes the methods for moving tables from one tablespace to
another under Oracle7. It describes using export/import to make this move
and how the user must be set up for it to work.
METHOD I - PER TABLE BASIS:
1. Export the table.
2. Drop the table.
3. Create the table in desired tablespace.
4. Import the table data, specifying 'IGNORE=Y' with the import command.
METHOD II: - PER USER BASIS:
1. Export the user/entire database.
2. Manipulate the user resource privilege so that he has no
privilege to create in the tablespace the table was originally
in, below are details:
By default an import will try to import tables into the same
tablespace they were exported from. If a user does not have privilege
to this tablespace, import will import his/her data into his/her
default tablespace. SO - just changing the default tablespace is not
enough - THE USER BEING IMPORTED MUST NOT HAVE RESOURCE PRIVILEGE TO
THE TABLESPACE THE EXPORT CAME FROM.
CREATING NEW USERS:
==================
If the database has been recreated, create users in the following
manner:
1. Create user Scott identified by tiger
2. Alter user Scott quota unlimited on user_data;
3. Alter user Scott default tablespace user_data
temporary tablespace user_data;
NOTE: Changing the temporary tablespace has no effect on this procedure.
That part of the Alert User statement is optional.
NOTE: Granting the resource role (i.e. 'grant resource to Scott') will
implicitly grant the user 'UNLIMITED TABLESPACE'. This privilege
allows the user to use an unlimited amount of space in any tablespace.
CHANGING EXISTING USERS:
=======================
When revoking privileges from a user, you must use the same revoke
statement as the one used to grant the privilege. For example,
granting the resource role to user then altering the user to have 0
quota on the system tablespace will not work. You must revoke the
resource role, then alter the user to have quota on specified
tablespaces. Therefore, before beginning to revoke privileges from
a user, you must first see what privileges he currently has.
NOTE: In our example, we want to move the tables of user TEST from the SYSTEM
tablespace to the USERS tablespace.
CHANGING THE DEFAULT TABLESPACE:
===============================
Since we know that import will create tables in the users' default tablespace
if no quota exists on the original tablespace, this should be the first change
we make:
SQL> select username, default_tablespace
2 from dba_users
3 where username='TEST';
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
TEST SYSTEM
SQL> alter user test
2 default tablespace users;
User altered.
SQL> select username, default_tablespace
2 from dba_users
3 where username='TEST';
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
TEST USERS
DETERMINING CURRENT USER'S PRIVILEGES:
=====================================
Check for the Resource Role:
---------------------------
Check to see if the user has been granted the resource role:
SQL> select * from dba_role_privs where grantee='TEST';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
TEST CONNECT NO YES
TEST RESOURCE NO YES
If the RESOURCE role is listed, then it must be revoked and the individual
privileges granted. To see what privileges the RESOURCE role is granted,
issue the following:
SQL> select * from dba_sys_privs where grantee='RESOURCE';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
RESOURCE CREATE CLUSTER NO
RESOURCE CREATE PROCEDURE NO
RESOURCE CREATE SEQUENCE NO
RESOURCE CREATE TABLE NO
RESOURCE CREATE TRIGGER NO
If modifications to the RESOURCE role have been made in your database, you may
see more privileges, otherwise only the above will be listed.
At this point, you can revoke the RESOURCE role from the user and grant him
the necessary privileges:
SQL> revoke resource from test;
Revoke succeeded.
SQL> select * from dba_role_privs where grantee='TEST';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
TEST CONNECT NO YES
Then grant him the same privileges:
SQL> grant CREATE CLUSTER to TEST;
Grant succeeded.
SQL> grant CREATE PROCEDURE to TEST;
Grant succeeded.
SQL> grant CREATE SEQUENCE to TEST;
Grant succeeded.
SQL> grant CREATE TABLE to TEST;
Grant succeeded.
SQL> grant CREATE TRIGGER to TEST;
Grant succeeded.
Check for Unlimited Tablespace:
------------------------------
Next, you must check to see if the user has been explicitly granted the
UNLIMITED TABLESPACE privilege:
SQL> select * from dba_sys_privs where grantee='TEST';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
TEST CREATE CLUSTER NO
TEST CREATE PROCEDURE NO
TEST CREATE SEQUENCE NO
TEST CREATE TABLE NO
TEST CREATE TRIGGER NO
TEST UNLIMITED TABLESPACE NO
If UNLIMITED TABLESPACE privilege appears, this must be revoked:
SQL> revoke unlimited tablespace from test;
Revoke succeeded.
Check for Tablespace Quota:
--------------------------
Finally, you must check to see if the user has been given explicitly quota on
the
tablespace his tables were in (for example, SYSTEM):
SQL> select tablespace_name, max_blocks, blocks
2 from dba_ts_quotas
3 where username='TEST';
TABLESPACE_NAME MAX_BLOCKS BLOCKS
------------------------------ ---------- ----------
SYSTEM -1 5
USERS 512 0
1. If max_blocks=negative number, the user has unlimited space quota
on this tablespace.
2. If max_blocks=positive number, the user has a specific space quota
on this tablespace.
In our example, the user TEST has been granted unlimited quota on tablespace
SYSTEM, but only 512 Oracle Blocks on tablespace USERS. In addition, we see
that the user TEST is using 5 blocks in the SYSTEM tablespace.
NOTE: If the User has been granted the RESOURCE or DBA roles, there WILL NOT
not be a record for them in this table. In addition, no records will appear
in this table if the user is explicitly granted the UNLIMITED TABLESPACE
privilege.
From this information, we know that we must remove the users quota on the
SYSTEM tablespace, otherwise, import will create his tables there:
SQL> alter user test quota 0 on system;
User altered.
SQL> select tablespace_name, max_blocks, blocks
2 from dba_ts_quotas
3 where username='TEST';
TABLESPACE_NAME MAX_BLOCKS BLOCKS
------------------------------ ---------- ----------
USERS 512 0
TEST PRIVILEGE:
==============
Once the user has been created or altered, you can test that this is all
correct by connecting as the user and trying to create a table in that
tablespace. An error 'ora-1536 "no resource privilege or space quota
exceeded for tablespace '%s'".
SQL> connect test/test
Warning: Product user profile information not loaded!
Error in disabling roles in product user profile.
Connected.
SQL> create table x (y number) tablespace system;
create table x (y number) tablespace system
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'SYSTEM'
If the user is able to create the table, DO NOT begin the import if the user
is
able to create the table in the original tablespace. You must go through
the previous queries to see why he is able to do so. Check for the DBA or
RESOURCE role, check for UNLIMITED TABLESPACE privilege, and finally check
for tablespace quota.
RESTRICTION:
===========
If the user has been granted the DBA role, then this must be revoked before
proceeding. If only the DBA role was granted, the user will implicitly
be granted the CONNECT and RESOURCE roles. Therefore, if you revoke the DBA
role, this user have no privileges, and can be created using the instructions
under 'CREATING NEW USERS' above.
You must import user by user, and NOT DO A FULL DATABASE IMPORT. Otherwise,
the users original privileges will be regranted by the import and
will cause import to create the tables in the original tablespace.