Migrate tablespace from dictionary managed to locally in Oracle

Migrate tablespace from dictionary managed to locally in Oracle

Following are the steps for Migrating the tablespace from dictionary managed to locally managed:

1. Offline all the data files.

select 'alter tablespace '||tablespace_name||' read only;' from dba_tablespaces where contents not like 'temporary' and contents not like 'undo' and tablespace_name not in ('SYSTEM');

2. Shutdown the database or instance.

SQL> shutdown immediate

3. Startup the database in restricted mode:

SQL> startup restrict

4. Migrated the system tablespace to local managed:

execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('USERS');

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.