–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