12c 表空间的管理

最新推荐文章于 2021-04-09 16:00:11 发布

wluckdog

最新推荐文章于 2021-04-09 16:00:11 发布

阅读量515

点赞数

分类专栏： 12C

本文链接：https://blog.csdn.net/wll_1017/article/details/78534710

版权

12C 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

查看 CDB 中所有容器表空间信息

查看当前默认表空间
SYS@cdb1211>  select PROPERTY_NAME,PROPERTY_VALUE from database_properties where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
PROPERTY_NAME        PROPERTY_VALUE
------------------------------ --------------------------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP


查看当前临时表空间管理方式
SYS@cdb1211>  select TABLESPACE_NAME,CONTENTS,LOGGING, EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces where tablespace_name='TEMP'; 
TABLESPACE CONTENTS LOGGING   EXTENT_MAN ALLOCATIO SEGMEN
---------- --------------------- --------- ---------- --------- ------
TEMP    TEMPORARYNOLOGGING LOCAL      UNIFORM MANUAL


 
 查看当前临时表空间的数据文件
SYS@cdb1211>  select TABLESPACE_NAME,FILE_ID,FILE_NAME,AUTOEXTENSIBLE  from dba_temp_files where tablespace_name='TEMP'; 
TABLESPACE    FILE_ID FILE_NAME      AUT
---------- ---------- ---------------------------------------------------------------------- ---
TEMP     1 /u01/app/oracle/oradata/cdb1211/temp01.dbf     YES


每个CDB或者PDB仅允许一个默认临时表空间或者表空间组
每个PDB具有自己的临时表空间或者表空间组

创建临时表空间组 TEMP_GRP

SYS@cdb1211> create temporary tablespace temp1 tempfile '/u01/app/oracle/oradata/cdb1211/temp101.dbf' size 100m autoextend on tablespace group temp_grp; 

SYS@cdb1211> create temporary tablespace temp2 tempfile '/u01/app/oracle/oradata/cdb1211/temp201.dbf' size 100m autoextend on tablespace group temp_grp;

 查看表空间组 
SYS@cdb1211>  select GROUP_NAME,TABLESPACE_NAME from dba_tablespace_groups; 
GROUP_NAME        TABLESPACE
------------------------------ ----------
TEMP_GRP        TEMP1
TEMP_GRP        TEMP2

SYS@cdb1211>  select TABLESPACE_NAME,CONTENTS,LOGGING, EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces where tablespace_name like 'TEMP_'; 
TABLESPACE CONTENTS LOGGING   EXTENT_MAN ALLOCATIO SEGMEN
---------- --------------------- --------- ---------- --------- ------
TEMP1    TEMPORARYNOLOGGING LOCAL      UNIFORM MANUAL
TEMP2    TEMPORARYNOLOGGING LOCAL      UNIFORM MANUAL

查看对应临时文件 

SYS@cdb1211> select TABLESPACE_NAME,FILE_ID,FILE_NAME,AUTOEXTENSIBLE  from dba_temp_files where tablespace_name like 'TEMP_'; 
TABLESPACE    FILE_ID FILE_NAME      AUT
---------- ---------- ---------------------------------------------------------------------- ---
TEMP1     5 /u01/app/oracle/oradata/cdb1211/temp101.dbf     YES
TEMP2     6 /u01/app/oracle/oradata/cdb1211/temp201.dbf     YES
 

查看当前CDB中的临时表空间
SYS@cdb1211> select con_id,TABLESPACE_NAME,file_name from cdb_temp_files;
    CON_ID TABLESPACE_NAME   FILE_NAME
---------- ------------------------------ --------------------------------------------------------------------------------
3 TEMP   /u01/app/oracle/oradata/cdb1211/prodpdb/temp01.dbf
1 TEMP   /u01/app/oracle/oradata/cdb1211/temp01.dbf
1 TEMP1   /u01/app/oracle/oradata/cdb1211/temp101.dbf
1 TEMP2   /u01/app/oracle/oradata/cdb1211/temp201.dbf
5 TEMP   /u01/app/oracle/oradata/cdb1211/pdb1/temp012017-06-15_01-03-35-033-AM.dbf

修改为默认临时表空间

在cdb中
conn sys/oracle@cdb1


SYS@cdb1211> alter database default temporary tablespace TEMP_GRP;
Database altered.

SYS@cdb1211> select PROPERTY_NAME,PROPERTY_VALUE from database_properties where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
PROPERTY_NAME PROPERTY_VALUE
---------------------------------------- ---------------
DEFAULT_TEMP_TABLESPACE TEMP_GRP

在pdb中设置临时默认表空间

conn sys/oracle@pdb1 as sysdba

查看当前pdb中的临时表空间
SYS@pdb1> select TABLESPACE_NAME,FILE_ID,FILE_NAME,AUTOEXTENSIBLE  from dba_temp_files where tablespace_name like 'TEMP'; 
TABLESPACE    FILE_ID FILE_NAME                                             AUT
---------- ---------- ------------------------------------------------------------      ---
TEMP     4 /u01/app/oracle/oradata/cdb1211/pdb1/temp012017-06-15_01-03-35-033-AM.dbf  YES


创建pdb中的临时表空间
SYS@pdb1> create temporary tablespace pdb_temp tempfile '/u01/app/oracle/oradata/cdb1211/pdb1/pdb1_temp101.dbf' size 100m autoextend on ; 
Tablespace created.


创建完后，查看pdb1中的临时表空间
SYS@pdb1> select TABLESPACE_NAME,FILE_ID,FILE_NAME,AUTOEXTENSIBLE  from dba_temp_files ;
TABLESPACE    FILE_ID FILE_NAME        AUT
---------- ---------- -------------------------------------------------------------------------------- ---
PDB_TEMP     7 /u01/app/oracle/oradata/cdb1211/pdb1/pdb1_temp101.dbf       YES
TEMP     4 /u01/app/oracle/oradata/cdb1211/pdb1/temp012017-06-15_01-03-35-033-AM.dbf        YES


查看pdb中的默认临时表空间
SYS@pdb1> select PROPERTY_NAME,PROPERTY_VALUE from database_properties where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE'; 
PROPERTY_NAME        PROPERTY_VALUE
------------------------------ --------------------------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP


SYS@cdb1211> alter pluggable  database default temporary tablespace pdb_temp;
或者
SYS@cdb1211> alter   database default temporary tablespace pdb_temp;


设置完新的temp表空间后，查看
 SYS@pdb1>  select PROPERTY_NAME,PROPERTY_VALUE from database_properties where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE'; 
PROPERTY_NAME        PROPERTY_VALUE
------------------------------ --------------------------------------------------
DEFAULT_TEMP_TABLESPACE        PDB_TEMP
 
 

 pdb1 创建表空间 CUST_DATA 
 
SYS@pdb1>   create tablespace cust_data datafile '/u01/app/oracle/oradata/cdb1211/pdb1/cust_data01.dbf'  size 10m; 
Tablespace created.


查看表空间对应数据文件 
SYS@pdb1>  select TABLESPACE_NAME,FILE_ID,FILE_NAME, bytes/1024/1024 MB,AUTOEXTENSIBLE from dba_data_files; 
TABLESPACE    FILE_ID FILE_NAME    MB AUT
---------- ---------- ------------------------------------------------------------ ---------- ---
SYSTEM    13 /u01/app/oracle/oradata/cdb1211/pdb1/system01.dbf  250 YES
SYSAUX    14 /u01/app/oracle/oradata/cdb1211/pdb1/sysaux01.dbf  360 YES
UNDOTBS1    15 /u01/app/oracle/oradata/cdb1211/pdb1/undotbs01.dbf  100 YES
CUST_DATA    16 /u01/app/oracle/oradata/cdb1211/pdb1/cust_data01.dbf   10 NO


设置 插拔数据pdb1 默认表空间
设置可插拔数据库 pdb2 的默认表空间 
SYS@pdb1>  alter pluggable database default tablespace cust_data; 
Pluggable database altered.


SYS@pdb1> select PROPERTY_NAME,PROPERTY_VALUE from database_properties where PROPERTY_NAME='DEFAULT_PERMANENT_TABLESPACE'; 
PROPERTY_NAME        PROPERTY_VALUE
------------------------------ --------------------------------------------------
DEFAULT_PERMANENT_TABLESPACE   CUST_DATA


 创建测试用户、测试表查看 
 创建用户damon
SYS@pdb1>  grant connect,resource,unlimited tablespace to damon identified by oracle; 
Grant succeeded.


查看damon用户的默认表空间
SYS@pdb1>  select username,default_tablespace from dba_users where username='DAMON'; 
USERNAME        DEFAULT_TABLESPACE
------------------------------ ------------------------------
DAMON        CUST_DATA


创建测试表 
SYS@pdb1>  create table damon.test as select * from user_tables where 1=2; 
Table created.

查看test表的默认表空间
SYS@pdb1>  select table_name,tablespace_name from dba_tables where table_name='TEST' and owner='DAMON'; 
TABLE_NAME TABLESPACE
---------------------------------------- ----------
TEST CUST_DATA

扩大永久临时表空间
SYS@pdb1>  alter database datafile 16 resize 20m; 
Database altered.

给默认永久表空间添加数据文件
SYS@pdb1>  alter database datafile 16 resize 20m; 
Database altered.

查看表空间里的数据文件
SYS@pdb1> select TABLESPACE_NAME,FILE_ID,FILE_NAME, bytes/1024/1024 MB,AUTOEXTENSIBLE from dba_data_files where tablespace_name='CUST_DATA'; 


TABLESPACE    FILE_ID FILE_NAME    MB AUT
---------- ---------- ------------------------------------------------------------ ---------- ---
CUST_DATA    16 /u01/app/oracle/oradata/cdb1211/pdb1/cust_data01.dbf   20 NO
CUST_DATA    17 /u01/app/oracle/oradata/cdb1211/pdb1/cust_data02.dbf   20 NO

设置永久表空间自动扩展
SYS@pdb1>  alter database datafile 16,17 autoextend on; 
Database altered.

查看数据文件
SYS@pdb1>  select TABLESPACE_NAME,FILE_ID,FILE_NAME, bytes/1024/1024 MB,AUTOEXTENSIBLE from dba_data_files where tablespace_name='CUST_DATA'; 
TABLESPACE    FILE_ID FILE_NAME    MB AUT
---------- ---------- ------------------------------------------------------------ ---------- ---
CUST_DATA    16 /u01/app/oracle/oradata/cdb1211/pdb1/cust_data01.dbf       38.625 YES
CUST_DATA    17 /u01/app/oracle/oradata/cdb1211/pdb1/cust_data02.dbf       29.625 YES

创建大文件表空间

SYS@pdb1> create tablespace old_data datafile '/u01/app/oracle/oradata/cdb1211/pdb1/old_data01.dbf' size 500m autoextend on maxsize 1T;
create tablespace old_data datafile '/u01/app/oracle/oradata/cdb1211/pdb1/old_data01.dbf' size 500m autoextend on maxsize 1T
*
ERROR at line 1:
ORA-03206: maximum file size of (134217728) blocks in AUTOEXTEND clause is out of range

默认创建的是 SMALLFILE 的表空间，8k的 block, 一个表空间可以包含多个数据文件，但单个数据 文件要小于 32G。  BIGFILE 的表空间只能包含一个数据文件，
当前标准块大小是 8K，最大支持 32T。块大 小时 32K 时，最大支持 128T。（ 4G blocks） 


查看默认表空间的类型
SYS@pdb1>   select PROPERTY_NAME,PROPERTY_VALUE from database_properties where PROPERTY_NAME='DEFAULT_TBS_TYPE'; 
PROPERTY_NAME        PROPERTY_VALUE
------------------------------ --------------------------------------------------
DEFAULT_TBS_TYPE        SMALLFILE

SYS@pdb1>  create bigfile tablespace old_data datafile '/u01/app/oracle/oradata/cdb1211/pdb1/old_data01.dbf' size 500m autoextend on maxsize 1T; 
Tablespace created.

SYS@pdb1> select TABLESPACE_NAME,FILE_ID,FILE_NAME, AUTOEXTENSIBLE,bytes/1024/1024 MB,MAXBYTES/1024/1024/1024 GB from dba_data_files; 
TABLESPACE    FILE_ID FILE_NAME    AUT       MB   GB
---------- ---------- ------------------------------------------------------------ --- ---------- ----------
SYSTEM    13 /u01/app/oracle/oradata/cdb1211/pdb1/system01.dbf   YES       250 31.9999847
SYSAUX    14 /u01/app/oracle/oradata/cdb1211/pdb1/sysaux01.dbf   YES       370 31.9999847
UNDOTBS1    15 /u01/app/oracle/oradata/cdb1211/pdb1/undotbs01.dbf   YES       100 31.9999847
CUST_DATA    16 /u01/app/oracle/oradata/cdb1211/pdb1/cust_data01.dbf   YES    38.625 31.9999847
CUST_DATA    17 /u01/app/oracle/oradata/cdb1211/pdb1/cust_data02.dbf   YES    29.625 31.9999847
OLD_DATA    19 /u01/app/oracle/oradata/cdb1211/pdb1/old_data01.dbf   YES       5001024


6 rows selected.

添加数据文件
SYS@pdb1>  alter tablespace old_data add datafile '/u01/app/oracle/oradata/cdb1211/pdb1/old_data02.dbf' size 10m; 
 alter tablespace old_data add datafile '/u01/app/oracle/oradata/cdb1211/pdb1/old_data02.dbf' size 10m
*
ERROR at line 1:
ORA-32771: cannot add file to bigfile tablespace


bigfile 表空间只能包含一个数据文件。

创建非标准块表空间

SYS@pdb1>   create tablespace tbs_16k datafile '/u01/app/oracle/oradata/cdb1211/pdb1/tbs_16k01.dbf' size 50m blocksize 16k; 
  create tablespace tbs_16k datafile '/u01/app/oracle/oradata/cdb1/pdb1/tbs_16k01.dbf' size 50m blocksize 16k
*
ERROR at line 1:
ORA-29339: tablespace block size 16384 does not match configured block sizes

直接创建非标准块大小表空间报错，需要先设置对应的 db_nk_cache_size 参数

 设置参数 db_16k_cache_size  
SYS@pdb1>  alter system set db_16k_cache_size=12m; 
 alter system set db_16k_cache_size=12m
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database

 PDB 中设置此参数报错，只能在实例级别设置（根容器中操作） 
 

 在根容器中设置参数 
 [oracle@enmoedu1 cdb1211]$ sqlplus /nolog
SQL*Plus: Release 12.2.0.1.0 Production on Tue Nov 14 20:09:16 2017
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
@> conn /as sysdba
Connected.
SYS@cdb1211>  alter system set db_16k_cache_size=12m; 
System altered.

SYS@cdb1211>  show parameter 16k 
NAME      TYPEVALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size      big integer 12M


SYS@pdb1>  create tablespace tbs_16k datafile '/u01/app/oracle/oradata/cdb1211/pdb1/tbs_16k01.dbf' size 50m blocksize 16k; 
Tablespace created.

查看16k block大小的表空间
SYS@pdb1>  select TABLESPACE_NAME,CONTENTS,block_size,LOGGING, EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces;
TABLESPACE CONTENTS BLOCK_SIZE LOGGING   EXTENT_MAN ALLOCATIO SEGMEN
---------- --------------------- ---------- --------- ---------- --------- ------
SYSTEM    PERMANENT       8192 LOGGING   LOCAL SYSTEM    MANUAL
SYSAUX    PERMANENT       8192 LOGGING   LOCAL SYSTEM    AUTO
UNDOTBS1   UNDO        8192 LOGGING   LOCALSYSTEM    MANUAL
TEMP    TEMPORARY       8192 NOLOGGING LOCAL UNIFORM   MANUAL
CUST_DATA  PERMANENT        8192 LOGGING   LOCALSYSTEM    AUTO
OLD_DATA   PERMANENT        8192 LOGGING   LOCALSYSTEM    AUTO
TBS_16K    PERMANENT       16384 LOGGING   LOCALSYSTEM    AUTO
7 rows selected.


SYS@pdb1> select TABLESPACE_NAME,FILE_ID,FILE_NAME, AUTOEXTENSIBLE,bytes/1024/1024 MB,MAXBYTES/1024/1024/1024 GB from dba_data_files; 
TABLESPACE    FILE_ID FILE_NAME    AUT       MB   GB
---------- ---------- ------------------------------------------------------------ --- ---------- ----------
SYSTEM    13 /u01/app/oracle/oradata/cdb1211/pdb1/system01.dbf   YES       250 31.9999847
SYSAUX    14 /u01/app/oracle/oradata/cdb1211/pdb1/sysaux01.dbf   YES       370 31.9999847
UNDOTBS1    15 /u01/app/oracle/oradata/cdb1211/pdb1/undotbs01.dbf   YES       100 31.9999847
CUST_DATA    16 /u01/app/oracle/oradata/cdb1211/pdb1/cust_data01.dbf   YES    38.625 31.9999847
CUST_DATA    17 /u01/app/oracle/oradata/cdb1211/pdb1/cust_data02.dbf   YES    29.625 31.9999847
OLD_DATA    19 /u01/app/oracle/oradata/cdb1211/pdb1/old_data01.dbf   YES       5001024
TBS_16K    20 /u01/app/oracle/oradata/cdb1211/pdb1/tbs_16k01.dbf   NO        50   0

wluckdog

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
12c 表空间的管理

查看 CDB 中所有容器表空间信息查看当前默认表空间SYS@cdb1211> select PROPERTY_NAME,PROPERTY_VALUE from database_properties where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';PROPERTY_NAME PROPERTY_VALUE-----
复制链接

扫一扫

专栏目录