多租户，表空间使用

克隆PDB，设置用户使用的表空间

准备创建一个PDB20
CREATE PLUGGABLE DATABASE pdb20 ADMIN USER pdb_adm IDENTIFIED BY Password1
FILE_NAME_CONVERT=(’/u01/app/oracle/oradata/CDB1/pdbseed/’,
‘/u01/app/oracle/oradata/CDB1/pdb20/’);

连接到PDB20，并打开
ALTER SESSION SET CONTAINER=pdb20;
ALTER PLUGGABLE DATABASE pdb20 OPEN;

创建表空间，用户，表
三个用户，三个表空间
创建表空间
CREATE TABLESPACE ts1
DATAFILE ‘/u01/app/oracle/oradata/CDB1/pdb20/ts101.dbf’
SIZE 1M AUTOEXTEND ON NEXT 1M;

创建用户
CREATE USER test1 IDENTIFIED BY test1
DEFAULT TABLESPACE ts1
QUOTA UNLIMITED ON ts1;

创建表
CREATE TABLE test1.t1 (
id NUMBER
);
INSERT INTO test1.t1 VALUES (1);
COMMIT;

每二个
CREATE TABLESPACE ts2
DATAFILE ‘/u01/app/oracle/oradata/CDB1/pdb20/ts201.dbf’
SIZE 1M AUTOEXTEND ON NEXT 1M;

CREATE USER test2 IDENTIFIED BY test2
DEFAULT TABLESPACE ts2
QUOTA UNLIMITED ON ts2;

CREATE TABLE test2.t2 (
id NUMBER
);
INSERT INTO test2.t2 VALUES (1);
COMMIT;

第三个
CREATE TABLESPACE ts3
DATAFILE ‘/u01/app/oracle/oradata/CDB1/pdb20/ts301.dbf’
SIZE 1M AUTOEXTEND ON NEXT 1M;

CREATE USER test3 IDENTIFIED BY test3
DEFAULT TABLESPACE ts3
QUOTA UNLIMITED ON ts3;

CREATE TABLE test3.t3 (
id NUMBER
);
INSERT INTO test3.t3 VALUES (1);
COMMIT;

查看创建的内容
SELECT owner, table_name, tablespace_name
FROM dba_tables
WHERE table_name IN (‘T1’,‘T2’,‘T3’)
ORDER BY owner;

SELECT * FROM test3.t3;

SELECT * FROM test1.t1;

测试一，克隆PDB指定的用户表空间
ALTER SESSION SET CONTAINER=cdb$root;

CREATE PLUGGABLE DATABASE pdb21 FROM pdb20
FILE_NAME_CONVERT=(’/u01/app/oracle/oradata/CDB1/pdb20/’
,’/u01/app/oracle/oradata/CDB1/pdb21/’)
USER_TABLESPACES=(‘ts1’, ‘ts2’);

ALTER PLUGGABLE DATABASE pdb21 OPEN;
ALTER SESSION SET CONTAINER = pdb21;

SELECT tablespace_name from dba_tablespaces;

SELECT * FROM test2.t2;

SELECT * FROM test3.t3;

克隆PDB不克隆表空间

ALTER SESSION SET CONTAINER=cdb$root;
CREATE PLUGGABLE DATABASE pdb22 FROM pdb20
FILE_NAME_CONVERT=(’/u01/app/oracle/oradata/CDB1/pdb20/’
,’/u01/app/oracle/oradata/CDB1/pdb22/’)
USER_TABLESPACES=NONE;

ALTER SESSION SET CONTAINER=pdb22;
ALTER PLUGGABLE DATABASE pdb22 OPEN;
SELECT tablespace_name from dba_tablespaces;

克隆所有用户定义的表空间
CREATE PLUGGABLE DATABASE pdb23 FROM pdb20
FILE_NAME_CONVERT=(’/u01/app/oracle/oradata/CDB1/pdb20/’,
‘/u01/app/oracle/oradata/CDB1/pdb23/’)
USER_TABLESPACES=ALL;

ALTER SESSION SET CONTAINER=pdb23;
ALTER PLUGGABLE DATABASE pdb23 OPEN;

除了指定的表空间不克隆，剩下的都克隆

ALTER SESSION SET CONTAINER=cdb$root;
CREATE PLUGGABLE DATABASE pdb24 FROM pdb20
FILE_NAME_CONVERT=(’/u01/app/oracle/oradata/CDB1/pdb20/’,
‘/u01/app/oracle/oradata/CDB1/pdb24/’)
USER_TABLESPACES=ALL EXCEPT(‘ts2’);

ALTER SESSION SET CONTAINER=pdb24;
ALTER PLUGGABLE DATABASE pdb24 OPEN;
SELECT tablespace_name from dba_tablespaces;

表空间基础

查看所有的容器
SHOW CON_NAME
创建表空间
CREATE TABLESPACE LIHHAO
DATAFILE ‘/u01/app/oracle/oradata/CDB1/pdb2/LH01.dbf’ SIZE 10M
AUTOEXTEND ON NEXT 1M;

增加数据文件
ALTER TABLESPACE LIHHAO
ADD DATAFILE ‘/u01/app/oracle/oradata/CDB1/pdb2/LH02.dbf’ SIZE 10M
AUTOEXTEND ON NEXT 1M;

查看创建的表空间
SELECT tablespace_name FROM dba_tablespaces;


查看数据文件
SELECT name FROM v$datafile;

删除表空间
DROP TABLESPACE lihhao INCLUDING CONTENTS AND DATAFILES;


创建临时表空间
CREATE TEMPORARY TABLESPACE lhtemp
TEMPFILE ‘/u01/app/oracle/oradata/CDB1/pdb2/lhtemp01.dbf’ SIZE 10M
AUTOEXTEND ON NEXT 5M;

删除临时表空间
DROP TABLESPACE lhtemp INCLUDING CONTENTS AND DATAFILES;

设置默认的用户表空间
ALTER PLUGGABLE DATABASE DEFAULT TABLESPACE lihao;

查看设置
SELECT property_value
FROM database_properties
WHERE property_name = ‘DEFAULT_PERMANENT_TABLESPACE’;

创建表空间，直接指定成默认表空间
切换到根

ALTER SESSION SET CONTAINER=cdb$root;
创建表空间
CREATE PLUGGABLE DATABASE pdb3 ADMIN USER pdb_adm IDENTIFIED BY Password1
FILE_NAME_CONVERT=(’/u01/app/oracle/oradata/CDB1/pdbseed/’,
‘/u01/app/oracle/oradata/CDB1/pdb3/’)
DEFAULT TABLESPACE users
DATAFILE ‘/u01/app/oracle/oradata/CDB1/pdb3/users01.dbf’ SIZE 1M AUTOEXTEND ON NEXT 1M;

打开PDB3，并连接到
ALTER PLUGGABLE DATABASE pdb3 OPEN;
ALTER SESSION SET CONTAINER = pdb3;

SELECT property_value
FROM database_properties
WHERE property_name = ‘DEFAULT_PERMANENT_TABLESPACE’;