ORACLE表空间管理维护

xiaoxiao2023-03-24 4

转载至http://www.cnblogs.com/kerrycode/p/3418694.html

1：表空间概念

在ORACLE数据库中，所有数据从逻辑结构上看都是存放在表空间当中，当然表空间下还有段、区、块等逻辑结构。从物理结构上看是放在数据文件中。一个表空间可由多个数据文件组成。

如下图所示，一个数据库由对应一个或多个表空间，表空间逻辑上有一个或多个段（Segment）组成，物理上由一个或多个os file组成。

1.1基本的表空间

系统中默认创建的几个表空间：

SYSTEM

SYSAUX

USERS

UNDOTBS1

EXAMPLE

TEMP

系统中必须的表空间有那几个？

答案： SYSTEM、SYSAUX、TEMP、UNDO，像USERS、EXAMPLE等表空间是可有可无的。

1.2表空间的分类

永久表空间存放永久性数据，如表，索引等。

临时表空间不能存放永久性对象，用于保存数据库排序，分组时产生的临时数据。

UNDO表空间保存数据修改前的镜象。

1.3表空间的管理

表空间的管理方式：

字典管理：全库所有的空间分配都放在数据字典中。容易引起字典争用，而导致性能问题。

本地管理：空间分配不放在数据字典，而在每个数据文件头部的第3到第8个块的位图块，来管理空间分配。

2：创建表空间

1: CREATE TABLESPACE TBS_TR_DATA 2: DATAFILE '/oradata/rTBS_TR_DATA_001.dbf' 3: SIZE 64G 4: EXTENT MANAGEMENT LOCAL 5: SEGMENT SPACE MANAGEMENT AUTO ONLINE; 6: 7: 8: 9: ALTER TABLESPACE TBS_TR_DATA 10: ADD DATAFILE '/oradata/rTBS_TR_DATA_002.dbf' 11: SIZE 64G 12: AUTOEXTEND OFF;

3：表空间管理

3.1 表空间信息

如何查看数据库有哪些表空间？如何查看表空间对应的数据文件？

查看表空间：

查看表空间可以通过下面几个系统视图查看基本信息

--包含数据库中所有表空间的描述信息

SELECT * FROM DBA_TABLESPACES

--包含当前用户的表空间的描叙信息

SELECT * FROM USER_TABLESPACES

--包含从控制文件中获取的表空间名称和编号信息

SELECT * FROM V$TABLESPACE;

查看数据文件

--包含数据文件以及所属的表空间的描述信息

SELECT * FROM DBA_DATA_FILES

--包含临时数据文件以及所属的表空间的描述信息

SELECT * FROM DBA_TEMP_FILES

--包含从控制文件中获取的数据文件的基本信息，包括它所属的表空间名称、编号等

SELECT * FROM V$DATAFILE

--包含所有临时数据文件的基本信息

SELECT * FROM V$TEMPFILE

3.1.1：查看默认的TEMP表空间

数据库级别

1: SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE 2: 3: 2 FROM DATABASE_PROPERTIES 4: 5: 3 WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE' 6: 7: 4 ; 8: 9: PROPERTY_NAME PROPERTY_VALUE 10: 11: ------------------------------ ---------------------------- 12: 13: DEFAULT_TEMP_TABLESPACE TEMP

用户级别

1: SELECT USERNAME, TEMPORARY_TABLESPACE FROM DBA_USERS

3.1.2：查看默认的永久表空间

如果创建用户时，不指定其永久表空间，则会使用默认的表空间。

1: SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE 2: 3: 2 FROM DATABASE_PROPERTIES; 4: 5: PROPERTY_NAME PROPERTY_VALUE 6: 7: ------------------------------ ------------------ 8: 9: DEFAULT_TEMP_TABLESPACE TEMP 10: 11: DEFAULT_PERMANENT_TABLESPACE USERS

3.1.3：查看默认的表空间类型

如果不指定表空间类型，就会默认使用DEFAULT_TBS_TYPE参数指定的表空间类型。

1: SQL> 2: 3: SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE 4: 5: 2 FROM DATABASE_PROPERTIES 6: 7: 3 WHERE PROPERTY_NAME='DEFAULT_TBS_TYPE'; 8: 9: PROPERTY_NAME PROPERTY_VALUE 10: 11: ------------------------------ ------------------ 12: 13: DEFAULT_TBS_TYPE SMALLFILE

3.1.4：查看表空间情况

SELECT * FROM DBA_TABLESPACES

3.1.5：查看表空间的数据文件

永久表空间/UNDO表空间

SELECT * FROM DBA_DATA_FILES;

临时表空间

SELECT * FROM V$TEMPFILE;

3.1.6：查看表空间使用情况

--SQL 1:

1: SELECT A.TABLESPACE_NAME AS TABLESPACE_NAME, 2: ROUND(A.BYTES/(1024*1024*1024),2) AS "TOTAL(G)" , 3: ROUND(B.BYTES/(1024*1024*1024),2) AS "USED(G)" , 4: ROUND(C.BYTES/(1024*1024*1024),2) AS "FREE(G)" , 5: ROUND((B.BYTES * 100) / A.BYTES,2) AS "% USED" , 6: ROUND((C.BYTES * 100) / A.BYTES,2) AS "% FREE" 7: FROM SYS.SM$TS_AVAIL A, SYS.SM$TS_USED B, SYS.SM$TS_FREE C 8: WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME 9: AND A.TABLESPACE_NAME = C.TABLESPACE_NAME;

计算表空间使用情况(考虑了数据文件自动增长情况)

1: SELECT UPPER(F.TABLESPACE_NAME) AS "表空间名称", 2: ROUND(D.AVAILB_BYTES ,2) AS "表空间大小(G)", 3: ROUND(D.MAX_BYTES,2) AS "最终表空间大小(G)", 4: ROUND((D.AVAILB_BYTES - F.USED_BYTES),2) AS "已使用空间(G)", 5: TO_CHAR(ROUND((D.AVAILB_BYTES - F.USED_BYTES) / D.AVAILB_BYTES * 100, 6: 2), '999.99') AS "使用比", 7: ROUND(F.USED_BYTES, 6) AS "空闲空间(G)", 8: F.MAX_BYTES AS "最大块(M)" 9: FROM ( 10: SELECT TABLESPACE_NAME, 11: ROUND(SUM(BYTES) / (1024 * 1024 * 1024), 6) USED_BYTES, 12: ROUND(MAX(BYTES) / (1024 * 1024 * 1024), 6) MAX_BYTES 13: FROM SYS.DBA_FREE_SPACE 14: GROUP BY TABLESPACE_NAME) F, 15: (SELECT DD.TABLESPACE_NAME, 16: ROUND(SUM(DD.BYTES) / (1024 * 1024 * 1024), 6) AVAILB_BYTES, 17: ROUND(SUM(DECODE(DD.MAXBYTES, 0, DD.BYTES, DD.MAXBYTES))/(1024*1024*1024),6) MAX_BYTES 18: FROM SYS.DBA_DATA_FILES DD 19: GROUP BY DD.TABLESPACE_NAME) D 20: WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME 21: ORDER BY 4 DESC

3.2 默认表空间

在ORACLE 9i数据库中，创建数据库用户时，如果没有指定默认的永久性表空间，则系统使用SYSTME表空间分别作为该用户的默认永久表空间，默认的临时表空间为TEMP。在ORACLE 10/11g中，如果不指定默认永久性表空间，则是USERS.默认的临时表空间为TEMP，当然前提是你没有修改过默认永久表空间值或指定用户的默认永久性表空间。ORACLE允许使用自定义的表空间作为默认永久性表空间，你可以用下面SQL查看数据库的默认永久表空间和默认临时表空间

SQL>SELECT * FROM database_properties WHERE PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE';

SQL>SELECT * FROM database_properties WHERE PROPERTY_NAME ='DEFAULT_PERMANENT_TABLESPACE'

你可以使用ALTER DATABASE DEFAULT TABLESPACE语句可以设置数据库的默认永久性表空间，这样建立用户时，默认将使用指定的表空间。

数据库级别：

永久表空间

SQL>ALTER DATABASE DEFAULT TABLESPACE USER;

临时表空间

SQL>ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

用户级别

SQL>ALTER USER USERNAM DEFAULT TABLESPACE NEW_TABLESPACE_NAME

查看用户对应的默认表空间

SELECT USERNAME, DEFAULT_TABLESPACE FROM DBA_USERS

注意事项：

1：如果我们在创建用户时指定了默认表空间为DEFAULT_PERMANENT_TABLESPACE的值，那么在修改默认表空间后，之前用户的默认表空间也会发生改变。

eg:

1: SQL> SELECT * FROM database_properties 2: 2 WHERE PROPERTY_NAME IN( 'DEFAULT_PERMANENT_TABLESPACE','DEFAULT_TEMP_TABLESPACE'); 3: 4: 5: PROPERTY_NAME PROPERTY_VALUE DESCRIPTION 6: ---------------- -------------------------------- ------------------------------------------------------------ 7: DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace 8: DEFAULT_PERMANENT_TABLESPACE USERS Name of default permanent tablespace 9: 10: 11: SQL> CREATE USER U1 IDENTIFIED BY U1 DEFAULT TABLESPACE USERS; 12: 13: 14: SQL> SELECT USERNAME, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE FROM DBA_USERS WHERE USERNAME ='U1'; 15: 16: USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE 17: ------------------------------ ------------------------------ ------------------------------ 18: U1 19: 20: 21: SQL> ALTER DATABASE DEFAULT TABLESPACE TEST1; 22: 23: Database altered 24: 25: 修改了默认永久性表空间后，此时查看用户U1的DEFAULT_TABLESPACE值，发现其值也改为了TEST1，证实了结论1的正确性。 26: 27: SQL> SELECT USERNAME, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE FROM DBA_USERS WHERE USERNAME ='U1'; 28: 29: USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE 30: ------------------------------ ------------------------------ ------------------------------ 31: U1 TEST1 TEMP 32:

2：如果我们在创建用户时没有指定用户表空间，那么默认也会使用DB的默认表空间，这时候如果我们修改了DB的默认表空间，用户的表空间也会发生改变。

将数据库的默认表空间切换为USERS

1: SQL> ALTER DATABASE DEFAULT TABLESPACE USERS; 2: 3: Database altered 4: 5: SQL> CREATE USER U2 IDENTIFIED BY U2; 6: 7: User created 8: 9: SQL> SELECT USERNAME, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE FROM DBA_USERS WHERE USERNAME ='U1'; 10: 11: USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE 12: ------------------------------ ------------------------------ ------------------------------ 13: U1 USERS TEMP 14: 15: SQL> ALTER DATABASE DEFAULT TABLESPACE TEST1; 16: 17: Database altered 18: 19: SQL> SELECT USERNAME, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE FROM DBA_USERS WHERE USERNAME ='U1'; 20: 21: USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE 22: ------------------------------ ------------------------------ ------------------------------ 23: U1 TEST1 TEMP

3: 如果我们在创建用户指定用户的表空间是其他的表空间，那么我们修改DB的默认表空间不会影响用户的表空间。

eg:

1: SQL> CREATE USER U3 IDENTIFIED BY U3 DEFAULT TABLESPACE TEST2; 2: 3: User created 4: 5: SQL> SELECT USERNAME, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE FROM DBA_USERS WHERE USERNAME='U3'; 6: 7: USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE 8: ------------------------------ ------------------------------ ------------------------------ 9: U3 TEST2 TEMP 10: 11: SQL> ALTER DATABASE DEFAULT TABLESPACE USERS; 12: 13: Database altered 14: 15: SQL> SELECT USERNAME, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE FROM DBA_USERS WHERE USERNAME='U3'; 16: 17: USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE 18: ------------------------------ ------------------------------ ------------------------------ 19: U3 TEST2 TEMP 20:

4: DB的默认表空间不能删除，除非将默认表空间指向其他表空间之后才可以删除。

SQL> DROP TABLESPACE USERS;

DROP TABLESPACE USERS

ORA-12919: 不能删除默认永久表空间

5: 如果用户的默认表空间指向其他的表空间，当这个表空间被drop 之后，用户的默认表空间会自动指向DB的默认表空间。

SQL> DROP TABLESPACE TEST2;

3.3 删除表空间

除了SYSTEM表空间外，数据库中的任何表空间可以删除。删除表空间时，ORACLE仅仅是在控制文件和数据字典中删除与表空间和数据文件相关的信息。默认情况下，ORACLE并不会在操作操作系统中删除相应的数据文件，因此在成功执行删除表空间的操作后，需要手动删除该表空间在操作系统中对应的数据文件。如果在删除表空间的同时要删除对应的数据文件，则必须显示的指定INCLUDING CONTENTS AND DATAFILES子句。注意:当前的数据库级的默认表空间不能删除，用户级的可以删除.否则会报错：ORA-12919: Can not drop the default permanent tablespace

DROP TABLESPACE 表空间名 [INCLUDING CONTENTS [AND DATAFILES] [CASCADE CONSTRAINTS]]

SQL> DROP TABLESPACE URER01 INCLUDING CONTENTS;

如果在表空间中包含数据库对象，则必须在DROP TABLESPACE语句中显示的指定INCLUDING CONTENTS. 如果要再删除表空间USER的同时删除它所对应的数据文件，则可以使用下面的语句

SQL>DROP TABLESPACE USER01 INCLUDING CONTENTS AND DATAFILES;

注意：删除表空间时，CONTENTS与DATAFILES选项错位，会报如下错误：

SQL>DROP TABLESPACE TBS_STAGE_DAT INCLUDING DATAFILES AND CONTENTS

ORA-01911：contents keyword expected

SQL>DROP TABLESPACE TBS_STAGE_DAT INCLUDING CONTENTS AND DATAFILES

3.4 调整表空间

3.4.1 增加数据文件

如果发现某个表空间存储空间不足时，可以为表空间添加新的数据文件，扩展表空间大小。但是一般建议预先估计表空间所需的存储空间大小，然后为它建立若干适当大小的数据文件。

例子：

1: ALTER TABLESPACE TBS_TR_IND 2: 3: ADD DATAFILE '/oradata/rTBS_TR_IND_002.dbf' 4: 5: SIZE 32G 6: 7: AUTOEXTEND OFF; 8: 9: SQL> ALTER TABLESPACE TBS_EDS_DAT 10: 11: 2 ADD DATAFILE 'G:\datafile\TBS_EDS_DAT01.DBF' 12: 13: 3 SIZE 100M 14: 15: 4 AUTOEXTEND ON 16: 17: 5 NEXT 10M 18: 19: 6 MAXSIZE 20480M; 20: 21: SQL> ALTER TABLESPACE temp01 22: 23: 2 ADD TMPFILE 'D:\ORACLEDATA\temp01_02.dbf' SIZE 10M REUSE; 24:

在添加新的数据文件时，如果同名的操作系统已经存在，ALTER TABLESPACE语句将失败。如果要覆盖同名的操作系统文件时，则必须在后面显示的指定REUSE子句。

SQL> ALTER TABLESPACE TBS_EDS_DAT

2 ADD DATAFILE 'G:\datafile\TBS_EDS_DAT01.DBF'

3 SIZE 100M

4 AUTOEXTEND ON

5 NEXT 10K

6 MAXSIZE 51200M;

ALTER TABLESPACE TBS_EDS_DAT

第 1 行出现错误:

ORA-03206: AUTOEXTEND 子句中 (6553600) 块的最大文件大小超出范围

ORACLE支持的数据文件大小是由它的db_block_size和db_block的数量决定的。其中db_block(ORACLE块)的数量是一个定值2**22-1(4194303).数据文件大小容量=块数量*块大小。下面列表说明不同数据块数据库所能支持的最大物理文件大小:

数据块的大小物理文件的最大值M 物理文件的最大值G

=============================================================================

2KB 8191M 8G

4KB 16383M 16G

8KB 32767M 32G

16KB 65535M 64G

32KB 131072M 128G

64KB 262144M 256G

3.4.2 调整数据文件大小

重置数据文件的大小

ALTER DATABASE DATAFILE '/database/oracle/oradata/gsp/tbs_dm_data_002.dbf'

RESIZE 500M;

3.4.3 删除数据文件

ALTER TABLESPACE TEST

DROP DATAFILE '/database/oracle/oradata/gsp/tbs_dm_data_002.dbf'

3.4.4 移动数据文件

现在有这样一个案例：以前数据库服务器只有一个容量比较小得磁盘，数据文件全部放在D盘，后来申请了一个1T的磁盘，需要给D盘腾出一些空间(D盘爆满了)，现在想移动一些大的数据文件到1T的磁盘：

1: 1.1：连接数据库 2: 3: SQL> conn sysdba/manage as sysdba 4: 5: 已连接。 6: 7: 1.2：把要移动数据文件的表空间脱机 8: 9: SQL> ALTER TABLESPACE TBS_EDS_DAT OFFLINE NORMAL ; 10: 11: 表空间已更改。 12: 13: 1.3：移动物理数据文件。 14: 15: 1.4：重命名文件 16: 17: SQL> ALTER DATABASE 18: 19: 2 RENAME FILE 20: 21: 3 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\WGODS\TBS_EDS_DAT.DBF' 22: 23: 4 TO 24: 25: 5 'G:\datafile\TBS_EDS_DAT.DBF'; 26: 27: 数据库已更改。 28: 29: 1.5：表空间联机 30: 31: SQL> ALTER TABLESPACE TBS_DM_DAT ONLINE; 32: 33: 表空间已更改。 34: 35: 1.6：如果要删除以前的数据文件，则必须先关闭数据库，手动删除文件，否则会报错。 36: 37: SQL> shutdow immediate 38: 39: 数据库已经关闭。 40: 41: 已经卸载数据库。 42: 43: ORACLE 例程已经关闭。 44: 45: SQL> startup 46: 47: ORACLE 例程已经启动。 48: 49: Total System Global Area 612368384 bytes 50: 51: Fixed Size 1250428 bytes 52: 53: Variable Size 104860548 bytes 54: 55: Database Buffers 499122176 bytes 56: 57: Redo Buffers 7135232 bytes 58: 59: 数据库装载完毕。 60: 61: 数据库已经打开。 62:

Linux/Unix下

1: 1、查看表空间的文件分布 2: 3: SQL> select TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024 "MB" 4: 5: from dba_data_files; 6: 7: 2、将表空间离线 8: 9: SQL> alter tablespace users offline; 10: 11: 3、在操作系统下将数据文件移到另一位置 12: 13: SQL> host mv /u01/app/oracle/oradata/ocp/users01.dbf /u02/ 14: 15: SQL> host ls /u02/ 16: 17: 4、修改控制文件的记录指针 18: 19: SQL> alter database rename file 20: 21: '/u01/app/oracle/oradata/ocp/users01.dbf' to '/u02/users01.dbf'; 22: 23: 或者 24: 25: SQL> alter tablespace users rename datafile 26: 27: '/u01/app/oracle/oradata/ocp/users01.dbf' to '/u02/users01.dbf'; 28: 29: 注：执行此项时，目标文件（TO后面的那一段）一定要存在。 30: 31: 5、将表空间在线 32: 33: SQL> alter tablespace users online; 34: 35: 对于那些不能offline的表空间，只能关闭数据，在mount状态下修改，修改后再OPEN

3.4.5 数据文件脱机

ALTER DATABASE DATAFILE '/database/oracle/oradata/gsp/tbs_dm_data_002.dbf' OFFLINE;

3.4.6 数据文件联机

ALTER DATABASE DATAFILE '/database/oracle/oradata/gsp/tbs_dm_data_002.dbf' ONLINE;

4：维护表空间

4.1 变更表空间状态

表空间状态有下面几种状态：online、offline、read only、read write。

若要查看表空间的状态，可以通过下面SQL语句来查看。

1: SQL> SELECT TABLESPACE_NAME, CONTENTS, STATUS FROM DBA_TABLESPACES; 2: 3: TABLESPACE_NAME CONTENTS STATUS 4: ------------------------------ --------- --------- 5: SYSTEM PERMANENT ONLINE 6: UNDOTBS1 UNDO ONLINE 7: SYSAUX PERMANENT ONLINE 8: TEMP TEMPORARY ONLINE 9: USERS PERMANENT ONLINE 10: EXAMPLE PERMANENT ONLINE 11: TBS_DM_DATA PERMANENT READ ONLY 12: 13: 7 rows selected 14: 15: 16: SQL> SELECT FILE#, STATUS, ENABLED FROM V$DATAFILE; 17: 18: FILE# STATUS ENABLED 19: ---------- ------- ---------- 20: 1 SYSTEM READ WRITE 21: 2 ONLINE READ WRITE 22: 3 ONLINE READ WRITE 23: 4 ONLINE READ WRITE 24: 5 ONLINE READ WRITE 25: 6 OFFLINE READ ONLY 26: 7 OFFLINE READ ONLY

4.1.1 表空间脱机

SQL>ALTER TABLESPACE TBS_DM_DAT OFFLINE IMMEDIATE;

设置脱机状态，可以使用下面4个参数来控制脱机方式

NORMAL 该参数表示将表空间以正常方式切换到脱机状态，在进入脱机状态过程中，ORACLE会执行一次检查点，将SGA区中与该表空间相关的脏缓存块写入数据文件中，然后再关闭表空间的所有数据文件。如果在这过程中没有发生任何错误，则可以使用NORMAL参数，这也是默认的方式。

TEMPORARY 该参数将表空间以临时方式切换到脱机状态。这时ORACLE在执行检查点时并不会检查各个数据文件的状态，即使某些数据文件处于不可用状态，ORACLE也会忽略这些错误。这样将表空间设置为联机状态时，可能需要进行数据恢复。

IMMEDIATE 该参数将表空间以立即方式切换到脱机状态，这时ORACLE不会执行检查点，也不会检查数据文件是否可用。而是直接将属于表空间的数据文件设置为脱机状态。下一次将表空间恢复为联机状态时必须进行数据库恢复。

FOR RECOVER 该参数将表空间以用于恢复方式切换到脱机状态，如果要对表空间进行基于时间的恢复，可以使用这个参数将表空间切换到脱机状态。

如果数据库运行在非归档模式下(NOARCHIVELOG),由于无法保留恢复表空间所需要的重做数据，所以不能将表空间以立即方式切换到脱机状态。如果表空间脱机了，则查询表空间下的表，会报错误：ORA-00376 此时无法读取文件以及 ORA-01110：数据文件x......

注意：脱机（offline）一般用于数据库的联机备份，数据恢复等维护操作。有些表空间不能OFFLINE，如：SYTEM,UNDO等

1. SYTEM 不能offline，也不能read only

2. 当前的UNDO表空空间，不能offline,也不能read only

3. 当前的临时表空间不能offline,也不能read only

4. SYSAUX可以offline 不能read only

SQL> ALTER TABLESPACE SYSTEM OFFLINE;

ALTER TABLESPACE SYSTEM OFFLINE

ORA-01541: system tablespace cannot be brought offline; shut down if necessary

SQL> ALTER TABLESPACE SYSTEM OFFLINE;

ALTER TABLESPACE SYSTEM OFFLINE

ORA-01541: system tablespace cannot be brought offline; shut down if necessary

4.1.2 表空间联机

SQL> ALTER TABLESPACE TBS_DM_DAT ONLINE;

4.1.3 表空间只读

SQL>ALTER TABLESPACE TBS_DM_DAT READY ONLY;

表空间只读时，其中的表，不能进行任何DML操作，否则会报错：ORA-00372: file xxx cannot be modified at this time

ORA-01110: data file xx: ********。但是能删除表。

4.1.4 表空间读写

SQL>ALTER TABLESPACE TBS_DM_DAT READ WRITE;

4.1.5 表空间改名

在ORACLE 10g 之前，表空间的名称是不能被修改的。在ORACLE 11G中，通过ALTER TABLESPACE 语句中使用RENAME子句，数据库管理员可以修改表空间的名称。

1: CREATE TABLESPACE TBS_DM_DAT 2: ATAFILE 'E:\APP\KERRY\ORADATA\ORCL\TBS_DM_DAT.DBF' 3: IZE 50M 4: XTENT MANAGEMENT LOCAL 5: EGMENT SPACE MANAGEMENT AUTO ONLINE 6: 7: 8: SQL>ALTER TABLESPACE TBS_DM_DAT RENAME TO TBS_DM_DATA 9: 10: QL> SELECT TABLESPACE_NAME FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = 'TBS_DM_DATA'; 11: 12: LESPACE_NAME 13: --------------------------- 14: _DM_DATA

4.1.6 开启自动扩展

ALTER DATABASE DATAFILE '/database/oracle/oradata/gsp/tbs_dm_data_002.dbf' AUTOEXTEND ON;

4.1.7 关闭自动扩展

ALTER DATABASE DATAFILE '/database/oracle/oradata/gsp/tbs_dm_data_002.dbf' AUTOEXTEND OFF;

5 表空间配额

表空间不足与用户配额不足是两种不同的概念。表空间的大小是指实际的用户表空间的大小，而配额大小指的是用户指定使用表空间的的大小。两者的解决方式亦不相同

3.5.1 查看用户的表空间配额

1: --查看所有用户表空间的配额情况 2: SELECT * FROM DBA_TS_QUOTAS 3: 4: --查看当前用户表空间的配额情况 5: SELECT * FROM USER_TS_QUOTAS 6: 7: 8: SQL> DESC DBA_TS_QUOTAS 9: Name Type Nullable Default Comments 10: --------------- ------------ -------- ------- ------------------------------------------------ 11: TABLESPACE_NAME VARCHAR2(30) Tablespace name 12: USERNAME VARCHAR2(30) User with resource rights on the tablespace 13: BYTES NUMBER Y Number of bytes charged to the user 14: MAX_BYTES NUMBER Y User's quota in bytes. NULL if no limit 15: BLOCKS NUMBER Y Number of ORACLE blocks charged to the user 16: MAX_BLOCKS NUMBER Y User's quota in ORACLE blocks. NULL if no limit 17: DROPPED VARCHAR2(3) Y Whether the tablespace has been dropped

MAX_BYTES=-1表示没有配额限制，

3.5.2 管理用户表空间配额

用户表空间限额的创建与更改：

1.创建用户时，指定限额

eg：

CREATE USER TEST IDENTIFIED BY TEST

DEFAULT TABLESPACE TS_TEST

TEMPORARY TABLESPACE TEMP

QUOTA 3M ON TS_TEST

PASSWORD EXPIRE;

2.更改用户的表空间限额：

A：不对用户做表空间限额控制:

查看是否没有表空间限额限制

B：取消限额

这种方式是全局性的.

SQL> GRANT UNLIMITED TABLESPACE TO SCOTT;

或者针对特定的表空间的.

SQL>ALTER USER SCOTT QUOTA UNIMITED ON TBS_EDS_DAT;

SELECT * FROM SESSION_PRIVS WHERE PRIVILEGE='UNLIMITED TABLESPACE'

SQL> REVOKE UNLIMITED TABLESPACE FROM SCOTT;

C:制定配额

3. 可以分配自然也可以回收了:

revoke unlimited tablespace from TEST;

或者

alter user skate quota 0 on TB;

表空间大小不足问题的解决：使用“ALTER TABLESPACE tablespace_name ADD DATAFILE filename SIZE size_of_file”命令向指定的数据增加表空间，根据具体的情况可以增加一个或多个表空间。

转载请注明原文地址: https://ju.6miu.com/read-1202288.html

最新回复(0)