How To Create a New Tablespace?
Page 1 of 1
How To Create a New Tablespace?
If you want a new dataspace, you can use the CREATE TABLESPACE ... DATAFILE statement
as shown in the following script:
SQL> CREATE TABLESPACE my_space
2 DATAFILE '/temp/my_space.dbf' SIZE 10M;
Tablespace created.
SQL> SELECT TABLESPACE_NAME, STATUS, CONTENTS FROM USER_TABLESPACES;
TABLESPACE_NAME STATUS CONTENTS
---------------- --------------- ---------
SYSTEM ONLINE PERMANENT
UNDO ONLINE UNDO
SYSAUX ONLINE PERMANENT
TEMP ONLINE TEMPORARY
USERS ONLINE PERMANENT
MY_SPACE ONLINE PERMANENT
SQL> SELECT TABLESPACE_NAME, FILE_NAME, BYTES FROM DBA_DATA_FILES;
TABLESPACE_NAME FILE_NAME BYTES
---------------- ---------------------------------- ----------
USERS \ORACLEXE\ORADATA\XE\USERS.DBF 104857600
SYSAUX \ORACLEXE\ORADATA\XE\SYSAUX.DBF 461373440
UNDO \ORACLEXE\ORADATA\XE\UNDO.DBF 94371840
SYSTEM \ORACLEXE\ORADATA\XE\SYSTEM.DBF 356515840
MY_SPACE \TEMP\MY_SPACE.DBF 10485760
So one statement created two structures: a tablespace and a data file. If you check your file
system with Windows file explorer, you will see the data file is located in the \temp directory of.
The data file size is about 10MB. Its contents should be blank and full of \x00 at this time.
as shown in the following script:
SQL> CREATE TABLESPACE my_space
2 DATAFILE '/temp/my_space.dbf' SIZE 10M;
Tablespace created.
SQL> SELECT TABLESPACE_NAME, STATUS, CONTENTS FROM USER_TABLESPACES;
TABLESPACE_NAME STATUS CONTENTS
---------------- --------------- ---------
SYSTEM ONLINE PERMANENT
UNDO ONLINE UNDO
SYSAUX ONLINE PERMANENT
TEMP ONLINE TEMPORARY
USERS ONLINE PERMANENT
MY_SPACE ONLINE PERMANENT
SQL> SELECT TABLESPACE_NAME, FILE_NAME, BYTES FROM DBA_DATA_FILES;
TABLESPACE_NAME FILE_NAME BYTES
---------------- ---------------------------------- ----------
USERS \ORACLEXE\ORADATA\XE\USERS.DBF 104857600
SYSAUX \ORACLEXE\ORADATA\XE\SYSAUX.DBF 461373440
UNDO \ORACLEXE\ORADATA\XE\UNDO.DBF 94371840
SYSTEM \ORACLEXE\ORADATA\XE\SYSTEM.DBF 356515840
MY_SPACE \TEMP\MY_SPACE.DBF 10485760
So one statement created two structures: a tablespace and a data file. If you check your file
system with Windows file explorer, you will see the data file is located in the \temp directory of.
The data file size is about 10MB. Its contents should be blank and full of \x00 at this time.
Similar topics
» How To Create a Table in a Specific Tablespace?
» How To Add Another Datafile to a Tablespace?
» What Is an Oracle Tablespace?
» How To Rename a Tablespace?
» What Happens to the Data Files If a Tablespace Is Dropped?
» How To Add Another Datafile to a Tablespace?
» What Is an Oracle Tablespace?
» How To Rename a Tablespace?
» What Happens to the Data Files If a Tablespace Is Dropped?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
Sun Jan 31, 2010 12:35 am by Micheal
» Accessing tables of other users in SQL
Sun Jan 31, 2010 12:34 am by Micheal
» Grouping commands in SQL
Sun Jan 31, 2010 12:32 am by Micheal
» Operations on Result Sets in SQL
Sun Jan 31, 2010 12:31 am by Micheal
» Subqueries in SQL
Sun Jan 31, 2010 12:30 am by Micheal
» Joining Relations in SQL
Sun Jan 31, 2010 12:28 am by Micheal
» Delete comand in SQL
Sun Jan 31, 2010 12:27 am by Micheal
» Update command in SQL
Sun Jan 31, 2010 12:26 am by Micheal
» Insert command in SQL
Sun Jan 31, 2010 12:23 am by Micheal