Oracle tablespace and data files

Create a tablespace in Oracle

1. Create a common tablespace
Create tablespace oracle_tablespace
Datafile '/home/oracle/oradata/orcl/oracle_tablespace.dbf'
Size 100 m
Autoextend on next 10 M maxsize 200 M
Extent management local
Uniform size 1 m;

2. Create an undo tablespace
CREATE undo tablespace undo_oracle
Datafile '/home/oracle/oradata/orcl/undo_oracle.dbf'
Size 50 m
Extent management local;

3. Create a temporary tablespace
CREATE temporary tablespace temporary_oracle
Tempfile '/home/oracle/oradata/orcl/temporary_oracle.dbf'
Size 50 m
Autoextend on next 10 M maxsize 100 M
Extent management local
Uniform size 1 m;

 

Extended tablespace

1. Expand data table space

Alter tablespace oracle_tablespace add datafile '/home/oracle/oradata/orcl/oracle_tablespace1.dbf' size 100 M;

2. Expand the undo tablespace

Alter tablespace oracle_tablespace add datafile '/home/oracle/oradata/orcl/undo_tablespace1.dbf' size 100 M;

3. extended temporary tablespace

Alter tablespace oracle_tablespace add tempfile '/home/oracle/oradata/orcl/temporary_tablespace1.dbf' size 100 M;

Delete a tablespace

-- Delete empty tablespace, but does not contain physical files
Drop tablespace tablespace_name;
-- Delete non-empty tablespace, but does not contain physical files
Drop tablespace tablespace_name including contents;
-- Delete empty tablespace, including physical files
Drop tablespace tablespace_name including datafiles;
-- Delete non-empty tablespace, including physical files
Drop tablespace tablespace_name including contents and datafiles;
-- If the tables in other tablespaces have foreign keys and other CONSTRAINTS associated with the table fields in the current tablespace, cascade constraints must be added.
Drop tablespace tablespace_name including contents and datafiles cascade constraints;

1 2 3 Next Page