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


The Eucharistic Miracles of the World
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>;
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:


Website Stats


Post a Comment Copyright 2011-21 All Rights Reserved | Site Map | Contact | Disclaimer