ALL_DATAFILES
📄字数 1.6K
👁️阅读量 加载中...
功能描述
ALL_DATAFILES系统视图用于管理数据库当前节点表空间数据文件信息。
字段说明
| 序号 | 字段名 | 类型 | 说明 | V11 | V12 |
|---|---|---|---|---|---|
| 0 | NODEID | INTEGER | 节点ID | √ | √ |
| 1 | SPACE_ID | INTEGER | 表空间ID | √ | √ |
| 2 | PATH | VARCHAR | 文件路径（虚拟路径） | √ | √ |
| 3 | FILE_NO | INTEGER | 序号（即在表空间中的第几个文件） | √ | √ |
| 4 | MAX_SIZE | BIGINT | 最大尺度 | √ | √ |
| 5 | STEP_SIZE | INTEGER | 增长步长 | √ | √ |
| 6 | CURR_SIZE | BIGINT | 当前尺度 | √ | √ |
| 7 | RESERVED1 | VARCHAR | 保留字段 | √ | √ |
应用举例
- 查询表空间数据文件所在的表空间名称
- 查询数据文件的当前使用大小、最大值、增长步长等
sql
SQL> SELECT * FROM ALL_DATAFILES;
+--------+----------+-----------------+---------+----------+-----------+-----------+-----------+
| NODEID | SPACE_ID | PATH | FILE_NO | MAX_SIZE | STEP_SIZE | CURR_SIZE | RESERVED1 |
+--------+----------+-----------------+---------+----------+-----------+-----------+-----------+
| 1 | 1 | /CATA/GSYS1.SYS | 1 | -1 | 8 | 8 | <NULL> |
| 1 | 257 | /CATA/UNDO.SYS | 1 | -1 | 512 | 8 | <NULL> |
| 1 | 258 | /CATA/LSYS1.SYS | 1 | -1 | 8 | 8 | <NULL> |
| 1 | 259 | /DATA/DATA1.DBF | 1 | -1 | 512 | 512 | <NULL> |
| 1 | 260 | /DATA/DATA2.DBF | 1 | -1 | 512 | 512 | <NULL> |
| 1 | 261 | /DATA/DATA3.DBF | 1 | -1 | 512 | 512 | <NULL> |
| 1 | 262 | /DATA/DATA4.DBF | 1 | -1 | 512 | 512 | <NULL> |
| 1 | 263 | /TEMP/TEMP1.DBF | 1 | -1 | 512 | 256 | <NULL> |
| 1 | 264 | /TEMP/TEMP2.DBF | 1 | -1 | 512 | 256 | <NULL> |
| 1 | 265 | /UNDO/UNDO1.DBF | 1 | -1 | 512 | 512 | <NULL> |
| 1 | 266 | /UNDO/UNDO2.DBF | 1 | -1 | 512 | 512 | <NULL> |
+--------+----------+-----------------+---------+----------+-----------+-----------+-----------+- 创建临时表空间
sql
SQL> CREATE TEMP TABLESPACE temp_file DATAFILE '/TEMP/temp_file.DBF' SIZE 8m NEXT 8M MAXSIZE 32M;
SQL> SELECT * FROM ALL_DATAFILES;
+--------+----------+---------------------+---------+----------+-----------+-----------+-----------+
| NODEID | SPACE_ID | PATH | FILE_NO | MAX_SIZE | STEP_SIZE | CURR_SIZE | RESERVED1 |
+--------+----------+---------------------+---------+----------+-----------+-----------+-----------+
| 1 | 1 | /CATA/GSYS1.SYS | 1 | -1 | 8 | 8 | <NULL> |
| 1 | 257 | /CATA/UNDO.SYS | 1 | -1 | 512 | 8 | <NULL> |
| 1 | 258 | /CATA/LSYS1.SYS | 1 | -1 | 8 | 8 | <NULL> |
| 1 | 259 | /DATA/DATA1.DBF | 1 | -1 | 512 | 512 | <NULL> |
| 1 | 260 | /DATA/DATA2.DBF | 1 | -1 | 512 | 512 | <NULL> |
| 1 | 261 | /DATA/DATA3.DBF | 1 | -1 | 512 | 512 | <NULL> |
| 1 | 262 | /DATA/DATA4.DBF | 1 | -1 | 512 | 512 | <NULL> |
| 1 | 263 | /TEMP/TEMP1.DBF | 1 | -1 | 512 | 256 | <NULL> |
| 1 | 264 | /TEMP/TEMP2.DBF | 1 | -1 | 512 | 256 | <NULL> |
| 1 | 265 | /UNDO/UNDO1.DBF | 1 | -1 | 512 | 512 | <NULL> |
| 1 | 266 | /UNDO/UNDO2.DBF | 1 | -1 | 512 | 512 | <NULL> |
| 1 | 267 | /TEMP/temp_file.DBF | 1 | 32 | 8 | 8 | <NULL> |
+--------+----------+---------------------+---------+----------+-----------+-----------+-----------+- 创建数据表空间
sql
-- 跨节点创建数据表空间，初始8MB，每次扩展8MB，最大32MB
SQL> CREATE TABLESPACE data_file ON NODE 2 DATAFILE '/DATA/DATA_FILE.DBF' SIZE 8M NEXT 8M MAXSIZE 32M;
-- 2号节点查询
SQL> SELECT * FROM ALL_DATAFILES;
+--------+----------+---------------------+---------+----------+-----------+-----------+-----------+
| NODEID | SPACE_ID | PATH | FILE_NO | MAX_SIZE | STEP_SIZE | CURR_SIZE | RESERVED1 |
+--------+----------+---------------------+---------+----------+-----------+-----------+-----------+
| 2 | 1 | /CATA/GSYS1.SYS | 1 | -1 | 8 | 8 | <NULL> |
| 2 | 257 | /CATA/UNDO.SYS | 1 | -1 | 512 | 8 | <NULL> |
| 2 | 258 | /CATA/LSYS1.SYS | 1 | -1 | 8 | 8 | <NULL> |
| 2 | 259 | /DATA/DATA1.DBF | 1 | -1 | 512 | 512 | <NULL> |
| 2 | 260 | /DATA/DATA2.DBF | 1 | -1 | 512 | 512 | <NULL> |
| 2 | 261 | /DATA/DATA3.DBF | 1 | -1 | 512 | 512 | <NULL> |
| 2 | 262 | /DATA/DATA4.DBF | 1 | -1 | 512 | 512 | <NULL> |
| 2 | 263 | /TEMP/TEMP1.DBF | 1 | -1 | 512 | 256 | <NULL> |
| 2 | 264 | /TEMP/TEMP2.DBF | 1 | -1 | 512 | 256 | <NULL> |
| 2 | 265 | /UNDO/UNDO1.DBF | 1 | -1 | 512 | 512 | <NULL> |
| 2 | 266 | /UNDO/UNDO2.DBF | 1 | -1 | 512 | 512 | <NULL> |
| 2 | 267 | /DATA/DATA_FILE.DBF | 1 | 32 | 8 | 8 | <NULL> |
+--------+----------+---------------------+---------+----------+-----------+-----------+-----------+