Move the Oracle Spatial objects to a User defined tablespace

1. Check the user MDSYS default tablespace

select username,default_tablespace from dba_users where username = 'MDSYS';

USERNAME DEFAULT_TABLESPACE
-------- ------------------
MDSYS SYSAUX


2. Create a new tablespace on which spatail data move.

Create tablespace spatial_tbs datafile 'D:\ORACLEXE\APP\ORACLE\ORADATA\XE\spatail01.dbf' size 100m autoextend on;


3. Login with MDSYS username

SQLPLUS MDSYS
PASSWORD:

Note: Run both 4 and 5 step one by one with MDSYS user. Donot run anything inbetween them.

4. Run the script which move the data dictionary object with help of script SDOMVDCT.SQL
sdomvdct.sql – Move the DiCTionary data to a new tablespace

$ORACLE_HOME/md/admin/sdomvdct.sql;

Example:
SQL> @D:\oraclexe\app\oracle\product\11.2.0\server\md\admin\sdomvdct.sql
Procedure created.
No errors.
Grant succeeded.


5. Run the script for start moving process

SQL> exec MDSYS.MOVE_SDO('SPATIAL_TBS');
PL/SQL procedure successfully completed.

Note: This procedure cannot be used to move Spatial objects from a tablespace other than SYSAUX.

6. Check the object move to new tablespace

SQL> select segment_name from dba_segments where tablespace_name = 'SPATIAL_TBS';

SEGMENT_NAME
--------------------------
SDO_INDEX_METADATA_TABLE
SDO_GEOM_METADATA_TABLE


7. You can change default tablespace for MDSYS

SQL> alter user MDSYS default tablespace SPATIAL_TBS;
User altered.