Monday May 16, 2011

Move to Locally Managed Tablespaces

As I've got asked during the workshop in Warsaw how to migrate Dictionary Managed to Locally Managed tablespaces here's some additional information and an example.

To find out if a tablespace is dictionary or locally managed you'd use this query:

SQL> select TABLESPACE_NAME, EXTENT_MANAGEMENT from DBA_TABLESPACES;


The procedure to migrate to locally managed tablespace is:

SQL> EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('tbs');

And the whole migration procedure would look like this:

STARTUP RESTRICT EXCLUSIVE;
EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('TBS');
-- do this for all tablespaces except SYSTEM, TEMP and SYSAUX
DROP TABLESPACE TEMP;
-- necessary if there's no "real" temp tablespace definded yet - see Note:160426.1
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE 'f' SIZE 10M;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;
ALTER TABLESPACE SYSAUX OFFLINE;
ALTER TABLESPACE USERS READ ONLY;
-- for all tablespaces except SYSTEM, RBS, TEMP and SYSAUX
EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM');
ALTER TABLESPACE SYSAUX ONLINE;
ALTER TABLESPACE USERS READ WRITE;
-- for all tablespaces except SYSTEM, RBS, TEMP and SYSAUX
SHUTDOWN IMMEDIATE

STARTUP


Thursday May 12, 2011

Recap: Upgrade Workshop in Warsaw

Thanks to everybody who did attend on a bright nice sunny day to our workshop in Warsaw today - in the most unusal venue I've had so far for a workshop: a cinema :-)

And please download the newest version of the slides via this link.

Warsaw


Thanks :-)
About

Mike Dietrich - Oracle Mike Dietrich
Master Product Manager - Database Upgrade & Migrations - Oracle Corp

Based near Munich/Germany and spending plenty of time in airplanes to run either upgrade workshops or work onsite or remotely with reference customers. Acting as interlink between customers/partners and the Upgrade Development.

Follow me on TWITTER

Contact me via LinkedIn or XING

Search

Archives
« August 2015
SunMonTueWedThuFriSat
      
2
3
6
7
8
9
10
11
12
13
15
16
22
23
25
26
27
28
29
30
31
     
Today
Oracle related Tech Blogs
Slides Download Center
Visitors since 17-OCT-2011
White Paper and Docs
Workshops
Viewlets and Videos
Workshop Map
This week on my Rega & Pono
Upgrade Reference Papers