oracle12c表空间管理

最新推荐文章于 2024-04-28 16:15:24 发布

sky@sea

最新推荐文章于 2024-04-28 16:15:24 发布

阅读量2.9k

点赞数

分类专栏： Oracle12c 文章标签： oracle12c 创建表空间数据文件重命名数据文件移动

本文链接：https://blog.csdn.net/u013986802/article/details/91491968

版权

Oracle12c 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

描述：本文包括创建表空间，为表空间添加数据文件，更改数据文件大小，移动和重命名数据文件以及一些oracle12c表空间管理新增的keep和reuse选项

操作如下：

C:\Users\v074>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on 星期三 6月 12 09:14:29 2019

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


连接到:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBSKY                         MOUNTED
         4 PDBCLOUD                       MOUNTED
         5 PDBTEST                        MOUNTED
--开启所有的pdb
SQL> alter pluggable database all open;

插接式数据库已变更。

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBSKY                         READ WRITE NO
         4 PDBCLOUD                       READ WRITE NO
         5 PDBTEST                        READ WRITE NO
--切换容器会话到pdbcloud
SQL> alter session set container=pdbcloud;

会话已更改。

SQL> select file_name ,tablespace_name from dba_data_files;

FILE_NAME                                          TABLESPACE_NAME
-------------------------------------------------- --------------------
E:\APP\ORADATA\SKY\PDBCLOUD\SYSTEM01.DBF           SYSTEM
E:\APP\ORADATA\SKY\PDBCLOUD\SYSAUX01.DBF           SYSAUX
E:\APP\ORADATA\SKY\PDBCLOUD\PDBCLOUD_USERS01.DBF   USERS

-- 为表空间添加数据文件
SQL> alter tablespace users add datafile 'E:\APP\ORADATA\SKY\PDBCLOUD\PDBCLOUD_USERS02.DBF' size 10m;   

表空间已更改。

SQL> select file_name ,tablespace_name,online_status from dba_data_files;

FILE_NAME                                          TABLESPACE_NAME      ONLINE_
-------------------------------------------------- -------------------- -------
E:\APP\ORADATA\SKY\PDBCLOUD\SYSTEM01.DBF           SYSTEM               SYSTEM
E:\APP\ORADATA\SKY\PDBCLOUD\SYSAUX01.DBF           SYSAUX               ONLINE
E:\APP\ORADATA\SKY\PDBCLOUD\PDBCLOUD_USERS02.DBF   USERS                ONLINE
E:\APP\ORADATA\SKY\PDBCLOUD\PDBCLOUD_USERS01.DBF   USERS                ONLINE

--创建cloud表空间
SQL> create tablespace cloud datafile 'E:\APP\ORADATA\SKY\PDBCLOUD\PDBCLOUD01.DBF' size 10m; 	

表空间已创建。

SQL> select file_name ,tablespace_name,online_status from dba_data_files;

FILE_NAME                                          TABLESPACE_NAME      ONLINE_
-------------------------------------------------- -------------------- -------
E:\APP\ORADATA\SKY\PDBCLOUD\SYSTEM01.DBF           SYSTEM               SYSTEM
E:\APP\ORADATA\SKY\PDBCLOUD\SYSAUX01.DBF           SYSAUX               ONLINE
E:\APP\ORADATA\SKY\PDBCLOUD\PDBCLOUD_USERS02.DBF   USERS                ONLINE
E:\APP\ORADATA\SKY\PDBCLOUD\PDBCLOUD_USERS01.DBF   USERS                ONLINE
E:\APP\ORADATA\SKY\PDBCLOUD\PDBCLOUD01.DBF         CLOUD                ONLINE

--删除表空间和其中的数据以及相关约束
SQL> drop tablespace cloud including contents and datafiles cascade constraints;	

表空间已删除。

SQL> select file_name ,tablespace_name,online_status from dba_data_files;

FILE_NAME                                          TABLESPACE_NAME      ONLINE_
-------------------------------------------------- -------------------- -------
E:\APP\ORADATA\SKY\PDBCLOUD\SYSAUX01.DBF           SYSAUX               ONLINE
E:\APP\ORADATA\SKY\PDBCLOUD\SYSTEM01.DBF           SYSTEM               SYSTEM
E:\APP\ORADATA\SKY\PDBCLOUD\PDBCLOUD_USERS01.DBF   USERS                ONLINE
E:\APP\ORADATA\SKY\PDBCLOUD\PDBCLOUD_USERS02.DBF   USERS                ONLINE

--创建cloud表空间，大小为10m
SQL> create tablespace cloud datafile 'E:\APP\ORADATA\SKY\PDBCLOUD\PDBCLOUD01.DBF' size 10m;

表空间已创建。

SQL> select file_name ,tablespace_name,online_status from dba_data_files;

FILE_NAME                                          TABLESPACE_NAME      ONLINE_
-------------------------------------------------- -------------------- -------
E:\APP\ORADATA\SKY\PDBCLOUD\SYSAUX01.DBF           SYSAUX               ONLINE
E:\APP\ORADATA\SKY\PDBCLOUD\SYSTEM01.DBF           SYSTEM               SYSTEM
E:\APP\ORADATA\SKY\PDBCLOUD\PDBCLOUD_USERS01.DBF   USERS                ONLINE
E:\APP\ORADATA\SKY\PDBCLOUD\PDBCLOUD_USERS02.DBF   USERS                ONLINE
E:\APP\ORADATA\SKY\PDBCLOUD\PDBCLOUD01.DBF         CLOUD                ONLINE

SQL> select file_name ,tablespace_name,online_status from dba_data_files;

FILE_NAME                                          TABLESPACE_NAME      ONLINE_
-------------------------------------------------- -------------------- -------
E:\APP\ORADATA\SKY\PDBCLOUD\SYSAUX01.DBF           SYSAUX               ONLINE
E:\APP\ORADATA\SKY\PDBCLOUD\SYSTEM01.DBF           SYSTEM               SYSTEM
E:\APP\ORADATA\SKY\PDBCLOUD\PDBCLOUD_USERS01.DBF   USERS                ONLINE
E:\APP\ORADATA\SKY\PDBCLOUD\PDBCLOUD_USERS02.DBF   USERS                ONLINE
E:\APP\ORADATA\SKY\PDBCLOUD\PDBCLOUD01.DBF         CLOUD                ONLINE

--联机数据文件重命名
SQL> alter database move datafile 'E:\APP\ORADATA\SKY\PDBCLOUD\PDBCLOUD01.DBF' to 'E:\APP\ORADATA\SKY\PDBCLOUD\CLOUD01.DBF';	

数据库已更改。

SQL> select file_id, file_name ,tablespace_name,online_status from dba_data_files;

   FILE_ID FILE_NAME                                          TABLESPACE_NAME      ONLINE_
---------- -------------------------------------------------- -------------------- -------
        11 E:\APP\ORADATA\SKY\PDBCLOUD\SYSTEM01.DBF           SYSTEM               SYSTEM
        12 E:\APP\ORADATA\SKY\PDBCLOUD\SYSAUX01.DBF           SYSAUX               ONLINE
        18 E:\APP\ORADATA\SKY\PDBCLOUD\PDBCLOUD_USERS02.DBF   USERS                ONLINE
        13 E:\APP\ORADATA\SKY\PDBCLOUD\PDBCLOUD_USERS01.DBF   USERS                ONLINE
        20 E:\APP\ORADATA\SKY\PDBCLOUD\CLOUD01.DBF            CLOUD                ONLINE

--使用数据文件编号来重命名和移动数据文件，并使用keep选项来保留原始文件
SQL> alter database move datafile 20 to 'E:\APP\ORADATA\SKY\PDBCLOUD\CLOUD02.DBF' keep;	

数据库已更改。

SQL> select file_id, file_name ,tablespace_name,online_status from dba_data_files;

   FILE_ID FILE_NAME                                          TABLESPACE_NAME      ONLINE_
---------- -------------------------------------------------- -------------------- -------
        12 E:\APP\ORADATA\SKY\PDBCLOUD\SYSAUX01.DBF           SYSAUX               ONLINE
        11 E:\APP\ORADATA\SKY\PDBCLOUD\SYSTEM01.DBF           SYSTEM               SYSTEM
        13 E:\APP\ORADATA\SKY\PDBCLOUD\PDBCLOUD_USERS01.DBF   USERS                ONLINE
        18 E:\APP\ORADATA\SKY\PDBCLOUD\PDBCLOUD_USERS02.DBF   USERS                ONLINE
        20 E:\APP\ORADATA\SKY\PDBCLOUD\CLOUD02.DBF            CLOUD                ONLINE

SQL> alter database move datafile 20 to 'E:\APP\ORADATA\SKY\PDBCLOUD\CLOUD01.DBF' keep;
alter database move datafile 20 to 'E:\APP\ORADATA\SKY\PDBCLOUD\CLOUD01.DBF' keep
*
第 1 行出现错误:
ORA-01119: 创建数据库文件 'E:\APP\ORADATA\SKY\PDBCLOUD\CLOUD01.DBF' 时出错
ORA-27038: 所创建的文件已存在
OSD-04010: 指定了 <create> 选项, 但文件已经存在

--使用reuse选项可以覆盖现存的文件
SQL> alter database move datafile 20 to 'E:\APP\ORADATA\SKY\PDBCLOUD\CLOUD01.DBF' reuse;	

数据库已更改。

SQL> select file_id, file_name ,tablespace_name,online_status from dba_data_files;

   FILE_ID FILE_NAME                                          TABLESPACE_NAME      ONLINE_
---------- -------------------------------------------------- -------------------- -------
        12 E:\APP\ORADATA\SKY\PDBCLOUD\SYSAUX01.DBF           SYSAUX               ONLINE
        11 E:\APP\ORADATA\SKY\PDBCLOUD\SYSTEM01.DBF           SYSTEM               SYSTEM
        13 E:\APP\ORADATA\SKY\PDBCLOUD\PDBCLOUD_USERS01.DBF   USERS                ONLINE
        18 E:\APP\ORADATA\SKY\PDBCLOUD\PDBCLOUD_USERS02.DBF   USERS                ONLINE
        20 E:\APP\ORADATA\SKY\PDBCLOUD\CLOUD01.DBF            CLOUD                ONLINE

SQL> select file_id, file_name ,tablespace_name,(bytes/(1024*1024)) as "bytes(m) ",online_status from dba_data_files;

   FILE_ID FILE_NAME                                          TABLESPACE  bytes(m)  ONLINE_
---------- -------------------------------------------------- ---------- ---------- -------
        12 E:\APP\ORADATA\SKY\PDBCLOUD\SYSAUX01.DBF           SYSAUX            600 ONLINE
        11 E:\APP\ORADATA\SKY\PDBCLOUD\SYSTEM01.DBF           SYSTEM            260 SYSTEM
        13 E:\APP\ORADATA\SKY\PDBCLOUD\PDBCLOUD_USERS01.DBF   USERS          253.75 ONLINE
        18 E:\APP\ORADATA\SKY\PDBCLOUD\PDBCLOUD_USERS02.DBF   USERS              10 ONLINE
        20 E:\APP\ORADATA\SKY\PDBCLOUD\CLOUD01.DBF            CLOUD              10 ONLINE

--更改数据文件大小
SQL> alter database datafile 'E:\APP\ORADATA\SKY\PDBCLOUD\CLOUD01.DBF' resize 50m;	

数据库已更改。

SQL> select file_id, file_name ,tablespace_name,(bytes/(1024*1024)) as "bytes(m) ",online_status from dba_data_files;

   FILE_ID FILE_NAME                                          TABLESPACE  bytes(m)  ONLINE_STA
---------- -------------------------------------------------- ---------- ---------- ----------
        12 E:\APP\ORADATA\SKY\PDBCLOUD\SYSAUX01.DBF           SYSAUX            600 ONLINE
        11 E:\APP\ORADATA\SKY\PDBCLOUD\SYSTEM01.DBF           SYSTEM            260 SYSTEM
        13 E:\APP\ORADATA\SKY\PDBCLOUD\PDBCLOUD_USERS01.DBF   USERS          253.75 ONLINE
        18 E:\APP\ORADATA\SKY\PDBCLOUD\PDBCLOUD_USERS02.DBF   USERS              10 ONLINE
        20 E:\APP\ORADATA\SKY\PDBCLOUD\CLOUD01.DBF            CLOUD              50 ONLINE

sky@sea

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
打赏
0
评论
oracle12c表空间管理

描述：本文包括创建表空间，为表空间添加数据文件，更改数据文件大小，移动和重命名数据文件以及一些oracle12c表空间管理新增的keep和reuse选项操作如下：C:\Users\v074>sqlplus / as sysdbaSQL*Plus: Release 12.1.0.2.0 Production on 星期三 6月 12 09:14:29 2019Copyri...
复制链接

扫一扫