Creating a tablespace

From MyWiki
Jump to: navigation, search
CREATE TABLESPACE <tablespacename>
DATAFILE    <filename>     [SIZE <nn> [K|M]| REUSE]     [AUTOEXTEND ON
[  NEXT n [K|M]]     [MAXSIZE    [n [K|M ]   |   UNLIMITED]]  |  OFF
[   MINIMUM  EXTENT  integer  [K|M]  ]
[   BLOCKSIZE  integer  [K] ]
[  TEMPORARY   |   PERMANENT   ]
[   EXTENT MANAGEMENT [  LOCAL |  DICTIONARY ]  ]
[   LOGGING  |  NOLOGGING  ]
[   ONLINE  |  OFFLINE   ]
[  SEGMENT  SPACE  MANAGEMENT [ MANUAL  |  AUTO  ]  ];

In 9i By default a locally managed tablespace is created.

To create dictionary managed tablespace
CREATE TABLESPACE USERS
DATAFILE '/dbs/database/data1.dbf' size 25M
EXTENT MANAGEMENT DICTIONARY;

You can convert dictionary managed tablespace to locally managed using the

DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('tablespace_name)

But during database creation if you have created the system tablespace as local, then you will not be able to create a dictionary managed tablespace.