Collections:
Add Another Datafile to a Tablespace in Oracle
How To Add Another Datafile to a Tablespace in Oracle?
✍: FYIcenter.com
If 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/fyicenter
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;
TABLESPACE_NAME FILE_NAME BYTES
--------------- --------------------------------- ---------
USERS C:\ORACLEXE\ORADATA\XE\USERS.DBF 104857600
SYSAUX C:\ORACLEXE\ORADATA\XE\SYSAUX.DBF 461373440
UNDO C:\ORACLEXE\ORADATA\XE\UNDO.DBF 94371840
SYSTEM C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF 356515840
MY_SPACE C:\TEMP\MY_SPACE.DBF 10485760
MY_SPACE C:\TEMP\MY_SPACE_2.DBF 5242880
SQL> SELECT TABLESPACE_NAME, FILE_ID, BYTES
2 FROM USER_FREE_SPACE
3 WHERE TABLESPAE_NAME IN ('MY_SPACE');
TABLESPACE_NAME FILE_ID BYTES
------------------------------ ---------- ----------
MY_SPACE 6 5177344
MY_SPACE 5 10354688
This script created one tablespace with two data files.
⇒ Error: Cannot Identify/Lock Data File in Oracle
⇐ Bring a Tablespace Online in Oracle
2019-04-09, 3177🔥, 0💬
Popular Posts:
How to set the current database in SQL Server? Once you are connected to the SQL Server, you should ...
What Happens to the Current Transaction If a START TRANSACTION Is Executed in MySQL? If you are in a...
How To Install Oracle Database 10g XE in Oracle? To install 10g universal edition, double click, Ora...
Can You Drop an Index Associated with a Unique or Primary Key Constraint in Oracle? You can not dele...
What are DDL (Data Definition Language) statements for tables in SQL Server? DDL (Data Definition La...