[大数据] oracle添加数据文件，扩展表空间

开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> oracle添加数据文件，扩展表空间 -> 正文阅读

[大数据]oracle添加数据文件，扩展表空间

1、查询文件位置

SQL> col file_name for a30
SQL> col tablespace_name for a20
SQL> select file_id,file_name,tablespace_name,sum(bytes)/1024/1024 total_mb,autoextensible from dba_data_files group by file_name,file_id,tablespace_name,autoextensible order by file_id;
————————————————

FILE_ID FILE_NAME                      TABLESPACE_NAME        TOTAL_MB AUT
---------- ------------------------------ -------------------- ---------- ---
     1 /home/oracle/app/oracle/oradat SYSTEM                      780 YES
       a/deydb/system01.dbf

     2 /home/oracle/app/oracle/oradat SYSAUX                      540 YES
       a/deydb/sysaux01.dbf

     3 /home/oracle/app/oracle/oradat UNDOTBS1                    410 YES
       a/deydb/undotbs01.dbf

     4 /home/oracle/app/oracle/oradat USERS                         5 YES
       a/deydb/users01.dbf

  FILE_ID FILE_NAME                      TABLESPACE_NAME        TOTAL_MB AUT
---------- ------------------------------ -------------------- ---------- ---

     5 /home/oracle/app/oracle/oradat UNDOTBS1                   1024 YES
       a/deydb/undotbs02.dbf


SQL>

2、如果自动扩展开启，需先关闭自动扩展

SQL> alter database datafile '/home/oracle/app/oracle/oradata/deydb/undotbs01.dbf' autoextend off;

Database altered.

SQL> select file_id,file_name,tablespace_name,sum(bytes)/1024/1024 total_mb,autoextensible from dba_data_files group by file_name,file_id,tablespace_name,autoextensible order by file_id;

  FILE_ID FILE_NAME                      TABLESPACE_NAME        TOTAL_MB AUT
---------- ------------------------------ -------------------- ---------- ---
     1 /home/oracle/app/oracle/oradat SYSTEM                      780 YES
       a/deydb/system01.dbf

     2 /home/oracle/app/oracle/oradat SYSAUX                      540 YES
       a/deydb/sysaux01.dbf

     3 /home/oracle/app/oracle/oradat UNDOTBS1                    410 NO
       a/deydb/undotbs01.dbf

     4 /home/oracle/app/oracle/oradat USERS                         5 YES
       a/deydb/users01.dbf

 FILE_ID FILE_NAME                      TABLESPACE_NAME        TOTAL_MB AUT
---------- ------------------------------ -------------------- ---------- ---

     5 /home/oracle/app/oracle/oradat UNDOTBS1                   1024 YES
       a/deydb/undotbs02.dbf


SQL>

3、添加数据文件，扩展表空间

SQL> alter tablespace UNDOTBS1 add datafile '/home/oracle/app/oracle/oradata/deydb/undotbs03.dbf' size 512M autoextend on;

Tablespace altered.

4、查询数据文件，添加成功

SQL> select file_id,file_name,tablespace_name,sum(bytes)/1024/1024 total_mb,autoextensible from dba_data_files group by file_name,file_id,tablespace_name,autoextensible order by file_id;

 FILE_ID FILE_NAME                      TABLESPACE_NAME        TOTAL_MB AUT
---------- ------------------------------ -------------------- ---------- ---
     1 /home/oracle/app/oracle/oradat SYSTEM                      780 YES
       a/deydb/system01.dbf

     2 /home/oracle/app/oracle/oradat SYSAUX                      540 YES
       a/deydb/sysaux01.dbf

     3 /home/oracle/app/oracle/oradat UNDOTBS1                    410 NO
       a/deydb/undotbs01.dbf

     4 /home/oracle/app/oracle/oradat USERS                         5 YES
       a/deydb/users01.dbf

 FILE_ID FILE_NAME                      TABLESPACE_NAME        TOTAL_MB AUT
---------- ------------------------------ -------------------- ---------- ---

     5 /home/oracle/app/oracle/oradat UNDOTBS1                   1024 YES
       a/deydb/undotbs02.dbf

     6 /home/oracle/app/oracle/oradat UNDOTBS1                    512 YES
       a/deydb/undotbs03.dbf


6 rows selected.

SQL>