Consider the Initial Tablespace declared as
CREATE TABLESPACE tbs_perm_02DATAFILE 'tbs_perm_02.dat' SIZE 10MREUSEAUTOEXTEND ON NEXT 10M MAXSIZE 200M;The declared space is exhausted and i need to add more datafile.
As
ALTER TABLESPACE tbs_perm_02ADD DATAFILE 'tbs_perm_02.dat'SIZE 20MAUTOEXTEND ON;The point here is that the table place is already exhausted its initially declared value of 10M Plus 200M. But When We ALTER the tablespace with extended datafile, wll that work out? Or is there another way to extend or alter in this case.
Best Answer
That will not work because the datafile you add has the same name as the first data file.The size/autoextend/maxsize - clause refers to the data file, not to the tablespace. So the datafile is exhausted, not the tablespace. You can either add a second datafile to your tablespace
ALTER TABLESPACE tbs_perm_02 ADD DATAFILE 'tbs_perm_02_02.dat' SIZE 20M AUTOEXTEND ON NEXT 10M MAXSIZE 200M;or you can change the MAXSIZE of the datafile of your tablespace
ALTER DATABASE DATAFILE 'tbs_perm_02.dat' RESIZE AUTOEXTEND ON NEXT 10M MAXSIZE 400M;Datafiles can be a part of file system or ASM Storage. If database is using ASM storage then you need to check the diskgroup name to add the datafiles.
Steps to add datafile in ASM:
Check diskgroup location to add datafiles:
show parameter db_create_filedest;
ALTER TABLESPACE tablespace_name ADD DATAFILE 'LOCATION' SIZE 10M AUTOEXTEND ON MAXSIZE 10G;Maxsize: upto 32G for 8k blocksize and 64G for 16K block size.
To add Datafiles located on file_system:
select file_name, bytes/1024/1024/1024,MAXBYTES/1024/1024/1024, autoextensible from dba_data_files where TABLESPACE_NAME='&TABLESPACE_NAME';ALTER TABLESPACE tablespace_name ADD DATAFILE 'location' SIZE 10M AUTOEXTEND ON MAXSIZE 10G; ex:
ALTER TABLESPACE tablespace_name ADD DATAFILE '/U01/ORACLE/DATAFILES/TESTDB/users02.dbf' SIZE 10M AUTOEXTEND ON MAXSIZE 10G;