ALL_TABLESPACES
📄字数 1.3K
👁️阅读量 加载中...
功能描述
ALL_TABLESPACES系统视图用于管理数据库当前节点数据表空间信息。
字段说明
| 序号 | 字段名 | 类型 | 说明 | V11 | V12 |
|---|---|---|---|---|---|
| 0 | NODE_ID | INTEGER | 节点ID | √ | √ |
| 1 | SPACE_ID | INTEGER | 表空间ID | √ | √ |
| 2 | SPACE_NAME | VARCHAR | 表空间名 | √ | √ |
| 3 | DATAFILE_NUM | INTEGER | 文件个数 | √ | √ |
| 4 | SPACE_TYPE | VARCHAR | 表空间类型 | √ | √ |
| 5 | MEDIA_ERROR | BOOLEAN | 介质错误 | √ | √ |
| 6 | TOTAL_CHUNK_NUM | BIGINT | 所有存贮单元数量 | √ | √ |
| 7 | FREE_CHUNK_NUM | BIGINT | 空闲存贮单元数量 | √ | √ |
SPACE_TYPE
| 数值 | 说明 |
|---|---|
| GSYS_SPACE | 全局系统表空间 |
| LSYS_SPACE | 局部系统表空间 |
| DATA_SPACE | 数据表空间 |
| UNDO_SPACE | 回滚表空间 |
| TEMP_SPACE | 临时表空间 |
| USYS_SPACE | 回滚系统表空间 |
应用举例
- 查询内置系统表空间
sql
SQL> SELECT * FROM ALL_TABLESPACES;
+--------+----------+------------+--------------+------------+-------------+-----------------+----------------+
| NODEID | SPACE_ID | SPACE_NAME | DATAFILE_NUM | SPACE_TYPE | MEDIA_ERROR | TOTAL_CHUNK_NUM | FREE_CHUNK_NUM |
+--------+----------+------------+--------------+------------+-------------+-----------------+----------------+
| 1 | 1 | GSYS1 | 1 | GSYS_SPACE | F | 1 | 0 |
| 1 | 257 | UNDO_SYS | 1 | USYS_SPACE | F | 1 | 0 |
| 1 | 258 | LSYS1 | 1 | LSYS_SPACE | F | 1 | 0 |
| 1 | 259 | DATA1 | 1 | DATA_SPACE | F | 64 | 41 |
| 1 | 260 | DATA2 | 1 | DATA_SPACE | F | 64 | 41 |
| 1 | 261 | DATA3 | 1 | DATA_SPACE | F | 64 | 41 |
| 1 | 262 | DATA4 | 1 | DATA_SPACE | F | 64 | 41 |
| 1 | 263 | TEMP1 | 1 | TEMP_SPACE | F | 32 | 30 |
| 1 | 264 | TEMP2 | 1 | TEMP_SPACE | F | 32 | 30 |
| 1 | 265 | UNDO1 | 1 | UNDO_SPACE | F | 64 | 0 |
| 1 | 266 | UNDO2 | 1 | UNDO_SPACE | F | 64 | 0 |
+--------+----------+------------+--------------+------------+-------------+-----------------+----------------+- 查询自定义临时表空间
sql
-- 创建临时表空间，初始8MB，每次扩展8MB，最大32MB
SQL> CREATE TEMP TABLESPACE tab_temp_file DATAFILE '/TEMP/temp_file.DBF' SIZE 8m NEXT 8M MAXSIZE 32M;
SQL> SELECT * FROM ALL_TABLESPACES WHERE SPACE_NAME='tab_temp_file';
+--------+----------+------------+--------------+------------+-------------+-----------------+----------------+
| NODEID | SPACE_ID | SPACE_NAME | DATAFILE_NUM | SPACE_TYPE | MEDIA_ERROR | TOTAL_CHUNK_NUM | FREE_CHUNK_NUM |
+--------+----------+------------+--------------+------------+-------------+-----------------+----------------+
| 1 | 267 | TEMP_FILE | 1 | TEMP_SPACE | F | 1 | 0 |
+--------+----------+------------+--------------+------------+-------------+-----------------+----------------+- 查询自定义数据表空间
sql
-- 创建非临时表空间，初始8MB，每次扩展8MB，最大32MB
SQL> CREATE TABLESPACE tab_data_file DATAFILE '/DATA/DATA_FILE.DBF' SIZE 8M NEXT 8M MAXSIZE 32M;
SQL> SELECT * FROM ALL_TABLESPACES WHERE SPACE_NAME='tab_data_file';
+--------+----------+------------+--------------+------------+-------------+-----------------+----------------+
| NODEID | SPACE_ID | SPACE_NAME | DATAFILE_NUM | SPACE_TYPE | MEDIA_ERROR | TOTAL_CHUNK_NUM | FREE_CHUNK_NUM |
+--------+----------+------------+--------------+------------+-------------+-----------------+----------------+
| 1 | 268 | DATA_FILE | 1 | DATA_SPACE | F | 1 | 0 |
+--------+----------+------------+--------------+------------+-------------+-----------------+----------------+