Oracle daily operation command manual (table space management) – from zero to none

Time:2020-11-16

–Time: August 24, 2020

–Author: Flying piggy

Manual directory:

#############################################################

2、 Manage table spaces
2.1 create data table space
2.1.1 create a table space without specifying a path
2.1.2 create table space by specified path
2.1.3 creating self growing table spaces
2.1.4 view the usage of get table space
2.1.5 expand table space
2.2 managing temporary table spaces
2.2.1 create a new temporary table space
2.2.2 expand temporary table space
2.2.3 view temporary table space status
2.3 deleting a table space
2.3.1 check whether the table space is bound to users
2.3.2 check whether there are tables in the table space
2.3.3 deleting a table space

#############################################################

2.1 create data table space

When creating a new table space, you need to specify the name of the table space, the file path of the table space, the size of the table space, and whether to automatically grow.

2.1.1 create a table space without specifying a path

When creating a table space, you can not specify the file path. The system will create a new file in the default format.

SQL> show parameter db_create_file_dest;     #View table space auto population catalog

SQL> alter system set db_create_file_dest=’/app/oracle/oradata/orcl/’ scope= both; ා set table space auto fill directory

SQL> show parameter db_create_file_dest;     #View table space auto population catalog

SQL> create  tablespace test_lvan datafile size 2G;                    #Create a new one called test_ Table space of Lvan

SQL> set linesize 200;                          #Set display line length 200 characters

SQL> col file_name for a60;               #Set file_ The display length of the name field is A60

SQL> col TABLESPACE_NAME  for a15;            #Set tablespace_ Name field display length A15

SQL> select file_ name,file_ id,tablespace_ name,round(bytes/1024/1024) total_ M  from dba_ data_ Files; ා view the name and size of the table space

 

2.1.2 create table space by specified path

When creating a table space with a specified path, you must first make sure that Oracle users have read and write permissions on the path folder. Otherwise, it will not work normally.

SQL> set linesize 200;                          #Set display line length 200 characters

SQL> col file_name for a60;               #Set file_ The display length of the name field is A60

SQL> col TABLESPACE_NAME  for a15;            #Set tablespace_ Name field display length ah 5

SQL> select file_ name,file_ id,tablespace_ name,round(bytes/1024/1024) total_ M  from dba_ data_ Files; ා view the name and size of the table space

From the command, we know that the files of all table spaces are in the directory / APP / Oracle / oradata / wanwan / datafile /.

 

Manually specify a path to create a table space

SQL> create  tablespace test_wan datafile ‘/app/oracle/oradata/WANWAN/datafile/test_wan.dbf’size 2G;                       #Create a new one called test_ Wan’s table space, with a size of 2G.

SQL> select file_ name,file_ id,tablespace_ name,round(bytes/1024/1024) total_ M  from dba_ data_ Files; ා view the name and size of the table space

 

2.1.3 create self growing table spaces

SQL> set linesize 220;                #Set display line length 220 characters

SQL> col tablespace_name for a15;    #Set tablespace_ The length of the name field is A15

SQL> col file_name for a75;    #Set file_ The name A75 field displays the length

SQL> select tablespace_ name,file_ name,AUTOEXTENSIBLE from dba_ data_ Files; ා check whether the table space is self growing

 

SQL> create tablespace test_auto_add datafile  size 2G  autoextend on next 100M maxsize 16G;          #Create a new table space with an initial size of 2G and enable automatic growth. Each increase is 100m, with a maximum of 16g.

SQL> set linesize 220;       #Set display line length 220 characters

SQL> col tablespace_name for a15;           #Set tablespace_ The length of the name field is A15

SQL> col file_name for a75;               #Set file_ The name A75 field displays the length

SQL> select tablespace_ name,file_ name,AUTOEXTENSIBLE,round(bytes/1024/1024)  total_ MB from dba_ data_ Files; ා check whether the table space is self growing

 

2.1.4 view the usage of the obtained table space

SQL> SELECT D.TABLESPACE_NAME,SPACE “SUM_SPACE(M)”,SPACE-NVL(FREE_SPACE,0) “USED_SPACE(M)”,

ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) “USED_RATE(%)”,FREE_SPACE “FREE_SPACE(M)”

FROM 

(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE

FROM DBA_DATA_FILES

GROUP BY TABLESPACE_NAME) D,

(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE

FROM DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME) F

WHERE  D.TABLESPACE_NAME = F.TABLESPACE_NAME(+);

 

2.1.5 expand table space

Using reset file size to expand table space

Format:

alter database datafile ‘Table space location ‘reset new size

SQL> alter database datafile ‘/u01/oracle/oradata/wyzc11g/sysaux01.dbf’  resize 780M;

 

Expanding table space by adding files

Format:

alter tablespace Table space name add datafile ‘new data file address’ size data file size

SQL> alter tablespace sysaux add datafile ‘/u01/oracle/oradata/wyzc11g/sysaux04.dbf’ size 100M;

 

Set automatic expansion for table spaces

Format:

alter database datafile ‘Data file location ‘AUTOEXTEND on next auto extend size maxsize

SQL> alter database datafile ‘/u01/oracle/oradata/wyzc11g/sysaux04.dbf’ autoextend on next 10M maxsize 1000M;

 

2.2.1 create a new temporary table space

All users share the same temporary table space by default. For business, it is generally necessary to create an independent temporary table space.

SQL> create temporary tablespace tmpadd  tempfile size 2G;

SQL> SELECT D.TABLESPACE_NAME,SPACE “SUM_SPACE(M)”,BLOCKS SUM_BLOCKS, 

USED_SPACE “USED_SPACE(M)”,ROUND(NVL(USED_SPACE,0)/SPACE*100,2) “USED_RATE(%)”,

NVL(FREE_SPACE,0) “FREE_SPACE(M)”

FROM 

(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS

FROM DBA_TEMP_FILES

GROUP BY TABLESPACE_NAME) D,

(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,

ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE

FROM V$TEMP_SPACE_HEADER

GROUP BY TABLESPACE_NAME) F

WHERE  D.TABLESPACE_NAME = F.TABLESPACE_NAME(+);

 

2.2.2 expand temporary table space

Expand the temp table space by adding files

Expand the temp table space and turn on automatic growth. Use alter tablespace temp add tempfile ‘file path’ size size size AUTOEXTEND on next to increase the size of maxsize each time;

SQL>alter tablespace temp add tempfile ‘/u01/oracle/oradata/wyzc11g/temp02.dbf’ size 1G autoextend on next 128M maxsize 3G;

 

Add file to expand temp without auto growth

Expand the temp table space without turning on auto growth. Use alter tablespace temp add tempfile ‘file path’ size size AUTOEXTEND off (auto expand off);

SQL>alter tablespace temp add tempfile ‘/u01/oracle/oradata/wyzc11g/temp02.dbf’ size 1G autoextend off;

 

Expanding temp table space by resetting file size

It is not recommended to use the reset file size method to expand the table space. In this way, the large table space cannot be expanded with the reset file method. Command: alter database tempfile ‘tablespace file path’ reset to adjust the size;

SQL> alter database tempfile ‘/u01/oracle/oradata/wyzc11g/temp03.dbf’ resize 3G ;

 

Set the existing temp file to auto extend

Set the currently used temp table space file to set automatic extension

SQL>alter database tempfile ‘/u01/oracle/oradata/wyzc11g/temp03.dbf’ autoextend on next 100M maxsize 6G;

 

2.2.3 view temporary table space status

View the file path name and table space name of the system temp table space

SQL>set linesize 200;

SQL> col file for a50;

SQL> select f.file#,t.ts#,f.name “File”,t.name “Tablespace” from v$tempfile f,V$tablespace t where f.ts# = t.ts#;

 

View the temp status of each user

See which users are using the temp table space

SQL>SELECT USERNAME, TEMPORARY_TABLESPACE FROM DBA_USERS;

 

View temporary table space usage

SQL> SELECT D.TABLESPACE_NAME,SPACE “SUM_SPACE(M)”,BLOCKS SUM_BLOCKS, 

USED_SPACE “USED_SPACE(M)”,ROUND(NVL(USED_SPACE,0)/SPACE*100,2) “USED_RATE(%)”,

NVL(FREE_SPACE,0) “FREE_SPACE(M)”

FROM 

(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS

FROM DBA_TEMP_FILES

GROUP BY TABLESPACE_NAME) D,

(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,

ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE

FROM V$TEMP_SPACE_HEADER

GROUP BY TABLESPACE_NAME) F

WHERE  D.TABLESPACE_NAME = F.TABLESPACE_NAME(+);

 

2.3 delete table space

When deleting a table space, you need to confirm whether the table space is really unnecessary, whether it is the user’s default storage table space, and whether there are undeletable data tables. If it is determined that the table space is not needed, the delete operation is performed.

2.3.1 check whether the table space is bound to users

Continuous update

2.3.2 check whether there are tables in the table space

Continuous update

2.3.3 delete table space

Continuous update