Topic:              Moving Tables from One Tablespace to Another
Last Revision Date: 11 September 1994
Author:Reem Adranly

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.

METHOD II: - PER USER BASIS:

 1.  Export the user/entire database.

 2.  Manipulate the user resource priviledge so that he has no
     priviledge to create in the tablespacen 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 users don't 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.  Grant connect to scott identified by tiger
                   2.  Grant resource on user_data to scott;
                   3.  Alter user scott default tablespace user_data
                                      temporary tablespace user_data;

        NOTE:  Granting an individual resource (i.e. 'grant resource to scott
               identified by tiger') is giving them resource priviledge on
               all tablespaces.

 CHANGING EXISTING USERS:

 When revoking priviledges from a user, you must use the same revoke
 statement as the one used to grant the priviledge.  For example,
 granting resource to user then revoking resource on system tablespace
 doesn't work. You must revoke resource then grant resource on XXX.

 TEST PRIVILEGE:  Once the user has been created or altered, you can
 test that this is all correct by connecting as the user <b>and</b> trying to
 create a table in that tablespace.  An error 'ora-1536 "no resource
 privilege or space quota exceeded for tablespace '%s'.

        RESTRICTION:  You cannot revoke resource from the system tablespace
                      from a DBA.  If you wish a DBAs data to be in a
                      tablespace besides system, you must revoke DBA privilege
                      from that person so that you may revoke resource from
                      the system tablespace.  Simply changing his default is
                      not enough.

        NOTE:  This is documented on page 3-3 of Utilities Users Guide.

 NOTE:  YOU MUST IMPORT USER BY USER, OTHERWISE, THE 'GRANT RESOURCE TO
        USER'  WILL BE ISSUED <b>AND</b> YOUR USER'S OLD RESOURCE PRIVILEDGES
        WILL BE BROUGHT IN.  THIS WILL CAUSE IMPORT TO CREATE THE TABLES
        IN THE ORIGINAL TABLESPACE.  THE UTILITIES GUIDE SUGGESTS THAT
        YOU REVOKE RESOURCE FROM ALL USERS BEFORE DOING THE FULL EXPORT,
        THIS WILL ALLOW YOU TO DO A FULL IMPORT.

DETERMINING CURRENT USER RESOURCE PRIVILEGE:

The dictionary views DBA_TS_QUOTAS <b>and</b> DBA_USERS provide the key to
evaluating which resource privileges a user has been granted.

a. SELECT RESOURCE_PRIV FROM DBA_USERS WHERE USERNAME = 'USR1'

  1. If resource_priv=1, the user has blanket resource privileges
     on all tablespaces. You can correctly assume that the DBA has issued
     'grant resource to usr1'. Accordingly you must revoke this blanket
     resource grant with a 'revoke resource from usr1'.

  2. If resource_priv=0, the user has resource privileges on
     specific tablespaces. Query DBA_TS_QUOTAS to determine which table-
     spaces.

b. SELECT MAX_BLOCKS, TABLESPACE_NAME FROM DBA_TS_QUOTAS WHERE

  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.

  3. If max_blocks=0, the user previously had a quota on this tablespace
     but it since has been revoked.

NOTE:  If DBA priviledge is 1 in DBA_USERS, regardless of other parameters, the
user has blanket resource on all tablespaces in the database.

</PRE>