Add Datafile, Create tablespace to Oracle database runing on ASM or with Oracle-managed files? Fix ORA-01276

advertisements

_____________________________________________________________________________________________________________________

It is very simple to add a datafile or create a tablespace to a database which is running on ASM or with Oracle-managed files. You don’t need to worry about the next file name etc. You will get following error if you try to specify a file name while creation of tablespace or adding a datafile to existing tablespace.

ORA-01276: Cannot add file +DATA/test01/datafile/users.260.755000671.  File has an Oracle Managed Files file name.

You will have to check the value for parameter DB_CREATE_FILE_DEST before adding a datafile.

SQL> show parameter DB_CREATE_FILE_DEST

NAME                                 TYPE        VALUE

------------------------------------ ----------- --------------

db_create_file_dest                  string      +DATA

Syntax For tablespace Creation:

CREATE TABLESPACE <tablespace_name>;

OR

CREATE bigfile TABLESPACE <tablespace_name> DATAFILE '+DATA'  SIZE 2147483648   AUTOEXTEND ON ;

 Syntax For Adding datafile to existing tablespace:

ALTER TABLESPACE <tablespace_name> add datafile ‘+DATA'  SIZE 2147483648   AUTOEXTEND ON ;

Example to create a tablespace with an Oracle-managed datafile of 100M that is not autoextensible:

CREATE TABLESPACE user_data DATAFILE AUTOEXTEND OFF;

_____________________________________________________________________________________________________________________

Website Stats