How to create an Oracle tablespace in six minutes

After a long period of learning to create Oracle tablespaces, I would like to share with you that you will certainly have a lot of gains after reading this article. I hope this article will teach you more things.

1. First query free space

Copy codeThe Code is as follows: select tablespace_name, file_id, block_id, bytes, blocks from dba_free_space;

2. Add Oracle tablespace

First, query the data file name, size, and path information. The statement is as follows:

Copy codeThe Code is as follows: select tablespace_name, file_id, bytes, file_name from dba_data_files;

3. The statement for modifying the file size is as follows:

Copy codeThe Code is as follows: alter database datafile
'The path of the data file to be added, that is, the path found above
'Resize 800 M;

4. Create an Oracle tablespace

Copy codeThe Code is as follows: create tablespace test
Datafile '/home/app/oracle/oradata/oracle8i/test01.dbf' size 8 M
Autoextend on
Next 5 M
Maxsize 10 M;

Create tablespace sales
Datafile '/home/app/oracle/oradata/oracle8i/sales01.dbf' size 800 M
Autoextend on
Next 50 M
Maxsize unlimited
Maxsize unlimited is not limited in size

Create tablespace sales
Datafile '/home/app/oracle/oradata/oracle8i/sales01.dbf' size 800 M
Autoextend on
Next 50 M
Maxsize 1000 M
Extent management local uniform;
Unform indicates the same partition size. The default value is 1 M.

Create tablespace sales
Datafile '/home/app/oracle/oradata/oracle8i/sales01.dbf' size 800 M
Autoextend on
Next 50 M
Maxsize 1000 M
Extent management local uniform size 500 K;
The unform size is 500 kb, indicating that the partition size is the same.

Create tablespace sales
Datafile '/home/app/oracle/oradata/oracle8i/sales01.dbf' size 800 M
Autoextend on
Next 50 M
Maxsize 1000 M
Extent management local autoallocate;
Autoallocate indicates that the partition size changes automatically and dynamically with the table size. Large tables use small tables in large regions and small tables use cells.

Create tablespace sales
Datafile '/home/app/oracle/oradata/oracle8i/sales01.dbf' size 800 M
Autoextend on
Next 50 M
Maxsize 1000 M
Temporary;
Temporary create a dictionary to manage temporary tablespace

Create temporary tablespace sales
Tempfile '/home/app/oracle/oradata/oracle8i/sales01.dbf' size 800 M
Autoextend on
Next 50 M
Maxsize 1000 M
Create a temporary tablespace for local management. If it is a temporary tablespace, replace datafile in all statements with tempfile.

By default, the 8i system creates a temporary tablespace for dictionary management. To create a temporary tablespace for local management, the temporary tablespace keyword must be added.
Do not use the atuoallocate parameter when creating local temporary tablespace management. The system creates the uniform management mode by default.

Add data files to the tablespace:
Alter tablespace sales add
Datafile '/home/app/oracle/oradata/oracle8i/sales02.dbf' size 800 M
Autoextend on next 50 M
Maxsize 1000 M;

Create a local temporary Oracle tablespace. If it is a temporary tablespace, replace the datafile in all statements with the tempfile8i system, and create a dictionary to manage the temporary tablespace by default, to create a local management temporary tablespace, you must add the temporary tablespace keyword to create a local management temporary tablespace. Do not use the atuoallocate parameter. By default, the system creates an uniform management mode.

Add data files to the tablespace:

Copy codeThe Code is as follows: alter tablespace sales add
Datafile '/home/app/oracle/oradata/oracle8i/sales02.dbf' size 800 M
Autoextend on next 50 M
Maxsize 1000 M;

5. Modify auto scaling attributes:

Copy codeThe Code is as follows: alter database datafile
'/Home/app/oracle/oradata/oracle8i/sales01.dbf ',
'/Home/app/oracle/oradata/oracle8i/sales02.dbf'
'/Home/app/oracle/oradata/oracle8i/sales01.dbf
Autoextend off;

The preceding section describes how to create an Oracle tablespace. Here we will share it with you and hope it will be useful to you.