How To Add Another Datafile to a Tablespace?
Submitted by: AdministratorIf you created a tablespace with a data file a month ago, now 80% of the data file is used, you should add another data file to the tablespace. This can be done by using the ALTER TABLESPACE ... ADD DATAFILE statement. See the following sample script:
SQL> connect HR/globalguideline
SQL> CREATE TABLESPACE my_space
2 DATAFILE '/temp/my_space.dbf' SIZE 10M;
Tablespace created.
SQL> ALTER TABLESPACE my_space
2 DATAFILE '/temp/my_space_2.dbf' SIZE 5M;
Tablespace altered.
SQL> SELECT TABLESPACE_NAME, FILE_NAME, BYTES
2 FROM DBA_DATA_FILES;
<pre>TABLESPACE_NAME FILE_NAME BYTES
--------------- --------------------------------- ---------
USERS C:ORACLEXEORADATAXEUSERS.DBF 104857600
SYSAUX C:ORACLEXEORADATAXESYSAUX.DBF 461373440
UNDO C:ORACLEXEORADATAXEUNDO.DBF 94371840
SYSTEM C:ORACLEXEORADATAXESYSTEM.DBF 356515840
MY_SPACE C:TEMPMY_SPACE.DBF 10485760
MY_SPACE C:TEMPMY_SPACE_2.DBF 5242880</pre>
Submitted by: Administrator
SQL> connect HR/globalguideline
SQL> CREATE TABLESPACE my_space
2 DATAFILE '/temp/my_space.dbf' SIZE 10M;
Tablespace created.
SQL> ALTER TABLESPACE my_space
2 DATAFILE '/temp/my_space_2.dbf' SIZE 5M;
Tablespace altered.
SQL> SELECT TABLESPACE_NAME, FILE_NAME, BYTES
2 FROM DBA_DATA_FILES;
<pre>TABLESPACE_NAME FILE_NAME BYTES
--------------- --------------------------------- ---------
USERS C:ORACLEXEORADATAXEUSERS.DBF 104857600
SYSAUX C:ORACLEXEORADATAXESYSAUX.DBF 461373440
UNDO C:ORACLEXEORADATAXEUNDO.DBF 94371840
SYSTEM C:ORACLEXEORADATAXESYSTEM.DBF 356515840
MY_SPACE C:TEMPMY_SPACE.DBF 10485760
MY_SPACE C:TEMPMY_SPACE_2.DBF 5242880</pre>
Submitted by: Administrator
SQL> SELECT TABLESPACE_NAME, FILE_ID, BYTES
2 FROM USER_FREE_SPACE
3 WHERE TABLESPAE_NAME IN ('MY_SPACE');
<pre>TABLESPACE_NAME FILE_ID BYTES
------------------------------ ---------- ----------
MY_SPACE 6 5177344
MY_SPACE 5 10354688
</pre>
This script created one tablespace with two data files.
Submitted by: Administrator
2 FROM USER_FREE_SPACE
3 WHERE TABLESPAE_NAME IN ('MY_SPACE');
<pre>TABLESPACE_NAME FILE_ID BYTES
------------------------------ ---------- ----------
MY_SPACE 6 5177344
MY_SPACE 5 10354688
</pre>
This script created one tablespace with two data files.
Submitted by: Administrator
Read Online Oracle Database Job Interview Questions And Answers
Top Oracle Database Questions
☺ | How Much Memory Your 10g XE Server Is Using? |
☺ | What Is the Relation of a User Account and a Schema? |
☺ | How To Use Values from Other Tables in UPDATE Statements using Oracle? |
☺ | What Is Input Buffer in SQL*Plus? |
☺ | What Happens to Indexes If You Drop a Table? |
Top DB Oracle Categories
☺ | Oracle PL-SQL Interview Questions. |
☺ | Oracle DBA Interview Questions. |
☺ | Oracle D2K Interview Questions. |
☺ | OCI Interview Questions. |
☺ | Oracle RMAN Interview Questions. |