Management of Oracle Tablespace

1. CREATE TABLE Space

CREATE tablespace Tbs_tr_data
DataFile '/oradata/rtbs_tr_data_001.dbf '
SIZE 64G
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO ONLINE;

ALTER tablespace Tbs_tr_data
ADD datafile '/oradata/rtbs_tr_data_002.dbf '
SIZE 64G
Autoextend OFF;

2, query the basic information table space

--Contains descriptive information for all table spaces in the database
SELECT * from Dba_tablespaces

--Contains the information describing the current user's tablespace
SELECT * from User_tablespaces

--Contains the tablespace name and number information obtained from the control file
SELECT * from V$tablespace;

3. View data files

--Contains descriptive information about the data file and the table space to which it belongs
SELECT * from Dba_data_files

--Contains descriptive information about the temporary data file and the table space to which it belongs
SELECT * from Dba_temp_files

--Contains basic information about the data file obtained from the control file, including the table space name, number, etc. to which it belongs
SELECT * from V$datafile

--Contains basic information for all temporary data files
SELECT * from V$tempfile

4. View the usage of table space

SELECT A.tablespace_name as Tablespace_name,
ROUND (a.bytes/(1024*1024*1024), 2) as "Total (G)",
ROUND (b.bytes/(1024*1024*1024), 2) as "Used (G)",
ROUND (c.bytes/(1024*1024*1024), 2) as "Free (G)",
ROUND ((B.bytes *)/a.bytes,2) as "% used",
ROUND ((C.bytes *)/a.bytes,2) as "% free"
From SYS. Sm$ts_avail A, SYS. Sm$ts_used B, SYS. Sm$ts_free C
WHERE A.tablespace_name = B.tablespace_name
and a.tablespace_name = C.tablespace_name;

5, calculate the use of the table space (considering the automatic growth of data files)

Select UPPER (f.tablespace_name) as "tablespace name",
ROUND (D.availb_bytes, 2) as "Table space size (G)",
ROUND (d.max_bytes,2) as "final tablespace size (G)",
ROUND ((d.availb_bytes-f.used_bytes), 2) as "used Space (G)",
To_char (ROUND (d.availb_bytes-f.used_bytes)/d.availb_bytes * 100,
2), ' 999.99 ') as "use ratio",
ROUND (F.used_bytes, 6) as "free Space (G)",
F.max_bytes as "Max Block (M)"
From (
SELECT Tablespace_name,
ROUND (SUM (BYTES)/(1024x768 * 1024x768), 6) Used_bytes,
ROUND (MAX (BYTES)/(1024x768 * 1024x768), 6) max_bytes
From SYS. Dba_free_space
GROUP by Tablespace_name) F,
(SELECT DD.) Tablespace_name,
ROUND (SUM (DD). BYTES)/(1024x768 * 1024x768), 6) Availb_bytes,
ROUND (SUM (DECODE (DD). MaxBytes, 0, DD. BYTES, DD. MaxBytes)/(1024*1024*1024), 6) max_bytes
From SYS. Dba_data_files DD
GROUP by DD. Tablespace_name) D
WHERE D.tablespace_name = F.tablespace_name
ORDER by 4 DESC

6. Delete Table space

DROP tablespace tablespace name [including CONTENTS [and Datafiles] [CASCADE CONSTRAINTS]
Sql> DROP tablespace URER01 including CONTENTS;
If you include a database object in a tablespace, you must display the specified including CONTENTS in the drop TABLESPACE statement. If you want to delete the table space user and delete the data file for it, you can use the following statement
Sql>drop tablespace USER01 including CONTENTS and datafiles;
Note: When you delete a tablespace, the contents and datafiles options are misaligned and will report the following error:
Sql>drop tablespace tbs_stage_dat including datafiles and CONTENTS
ora-01911:contents keyword expected

7. Add data file to table space

sql> ALTER tablespace Tbs_tr_ind
ADD datafile '/oradata/rtbs_tr_ind_002.dbf '
SIZE 32G
Autoextend OFF;

sql> ALTER tablespace Tbs_eds_dat
ADD datafile ' G:\datafile\TBS_EDS_DAT01. DBF '
SIZE 100M
Autoextend on
NEXT 10M
MAXSIZE 20480M;

sql> ALTER tablespace TEMP01
ADD tmpfile ' D:\ORACLEDATA\temp01_02.dbf ' SIZE 10M reuse;

8. Adjust the data file size

ALTER DATABASE datafile '/database/oracle/oradata/gsp/tbs_dm_data_002.dbf '
RESIZE 500M;

9. Delete data files

ALTER tablespace TEST
DROP datafile '/database/oracle/oradata/gsp/tbs_dm_data_002.dbf '

10. Moving Data files

1.1: Connect to the database
Sql> Conn Sysdba/manage as Sysdba
is connected.

1.2: Take the table space to move the data file offline
sql> ALTER tablespace tbs_eds_dat OFFLINE NORMAL;
Table space has changed.

1.3: Move the physical data file.

1.4: Renaming files
Sql> ALTER DATABASE
RENAME FILE
' D:\ORACLE\PRODUCT\10.2.0\ORADATA\WGODS\TBS_EDS_DAT. DBF '
To
' G:\datafile\TBS_EDS_DAT. DBF ';
The database has changed.

1.5: Table Space Online
sql> ALTER tablespace tbs_dm_dat ONLINE;
Table space has changed.

1.6: If you want to delete the previous data file, you must first close the database, manually delete the file, otherwise it will be an error.
Sql> Shutdow Immediate
The database is closed.
The database has been uninstalled.
The ORACLE routine has been closed.

Sql> Startup
The ORACLE routine has been started.
Total System Global area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 104860548 bytes
Database buffers 499122176 bytes
Redo buffers 7135232 bytes
The database is loaded.
The database is already open.

11, move the table in the physical location of the table space

ALTER TABLE T1 move;

12. Clear Table Space

Purge tablespace user name;

Management of Oracle Tablespace