oracle数据库常用命令1—无图片 windows

创建PDB2
CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb_adm IDENTIFIED BY Password1
FILE_NAME_CONVERT=(‘D:\app\Administrator\oradata\CDB\pdbseed’,
‘D:\app\Administrator\oradata\CDB\pdb2’);

创建PDB3
ALTER SESSION SET PDB_FILE_NAME_CONVERT=‘D:\app\Administrator\oradata\CDB\pdbseed’,
‘D:\app\Administrator\oradata\CDB\pdb3’;
CREATE PLUGGABLE DATABASE pdb3 ADMIN USER pdb_adm IDENTIFIED BY Password1;

查看PDB
SELECT pdb_name, status FROM dba_pdbs;
SELECT name, open_mode FROM v$pdbs;
show pdbs

使用新建的PDB
将创建状态为“ NEW”的PDB。必须以READ WRITE模式至少打开一次它们，才能完成PDB到CDB的集成
ALTER PLUGGABLE DATABASE pdb2 OPEN READ WRITE;
ALTER PLUGGABLE DATABASE pdb3 OPEN READ WRITE;

显示数据库中的可用服务
SELECT name, pdb FROM v$services;

显示当前容器的名称
SHOW CON_NAME

显示当前容器的ID
SHOW CON_ID

在容器之间切换
ALTER SESSION SET CONTAINER = pdb1;
ALTER SESSION SET CONTAINER=cdb$root;

连接到可插拔数据库
CONN system/aabb123@pdb3

关闭数据库
SHUTDOWN IMMEDIATE;

启动数据库
STARTUP;

关闭指定的PDB
ALTER PLUGGABLE DATABASE pdb3, pdb2 CLOSE IMMEDIATE;

只读打开指定的PDB
ALTER PLUGGABLE DATABASE pdb3, pdb2 OPEN READ ONLY FORCE;

除了PDB1剩下的都关闭
ALTER PLUGGABLE DATABASE ALL EXCEPT pdb1 CLOSE IMMEDIATE;

除了PDB1剩下的都打开
ALTER PLUGGABLE DATABASE ALL EXCEPT pdb1 OPEN;

关闭所有PDB
ALTER PLUGGABLE DATABASE ALL CLOSE IMMEDIATE;

打开所有PDB
ALTER PLUGGABLE DATABASE ALL OPEN;

设置自动启动
SELECT con_name, instance_name, state FROM dba_pdb_saved_states;
ALTER PLUGGABLE DATABASE pdb1 SAVE STATE;

CDB中的表空间
创建表空间
CREATE TABLESPACE lh
DATAFILE ‘D:\app\Administrator\oradata\CDB\lh01.dbf’ SIZE 1M
AUTOEXTEND ON NEXT 1M;

增加数据文件
ALTER TABLESPACE lh ADD
DATAFILE ‘D:\app\Administrator\oradata\CDB\lh02.dbf’ SIZE 1M
AUTOEXTEND ON NEXT 1M;

管理PDB中的表空间
ALTER SESSION SET CONTAINER = pdb3;
SHOW CON_NAME

创建表空间
CREATE TABLESPACE lh
DATAFILE ‘D:\app\Administrator\oradata\CDB\PDB3\lh01.dbf’ SIZE 1M
AUTOEXTEND ON NEXT 1M;

增加数据文件
ALTER TABLESPACE lh ADD
DATAFILE ‘D:\app\Administrator\oradata\CDB\PDB3\lh02.dbf’ SIZE 1M
AUTOEXTEND ON NEXT 1M;

查看表空间的数据文件
SELECT name FROM v$datafile;

查看临时表空间的数据文件
SELECT name FROM v$tempfile;

创建临时表空间
CREATE TEMPORARY TABLESPACE temp2
TEMPFILE ‘D:\app\Administrator\oradata\CDB\pdb1\temp02.dbf’ SIZE 5M
AUTOEXTEND ON NEXT 1M;

查看表空间
SELECT tablespace_name FROM dba_tablespaces;

设置默认表空间
ALTER PLUGGABLE DATABASE DEFAULT TABLESPACE lh;

设置默认临时表空间
ALTER PLUGGABLE DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

创建PDB4，指定默认表空间
CREATE PLUGGABLE DATABASE pdb4 ADMIN USER pdb_adm IDENTIFIED BY Password1
FILE_NAME_CONVERT=(‘D:\app\Administrator\oradata\CDB\pdbseed’,
‘D:\app\Administrator\oradata\CDB\pdb4’)
DEFAULT TABLESPACE lh DATAFILE ‘D:\app\Administrator\oradata\CDB\pdb4\lh01.dbf’ SIZE 1M AUTOEXTEND ON NEXT 1M;

ALTER PLUGGABLE DATABASE pdb4 OPEN;
切换容器查看表空间
ALTER SESSION SET CONTAINER = pdb4;
查看默认表空间
SELECT property_value
FROM database_properties
WHERE property_name = ‘DEFAULT_PERMANENT_TABLESPACE’;

创建通用用户
当前容器必须是根容器。
普通用户的用户名必须以“ C ##”或“ c ##”为前缀，并且只能包含ASCII或EBCDIC字符。
用户名在所有容器中必须唯一。
您可以指定该CONTAINER=ALL子句，也可以忽略它，因为当当前容器为根时，这是默认设置。
ALTER SESSION SET CONTAINER=cdb$root;
show CON_name
CREATE USER c##test_lh1 IDENTIFIED BY aabb123 CONTAINER=ALL;
GRANT CREATE SESSION TO c##test_lh1 CONTAINER=ALL;

不加CONTAINER=ALL
CREATE USER c##test_lh2 IDENTIFIED BY password1;
GRANT CREATE SESSION TO c##test_lh2;

创建本地用户
本地用户的用户名不得以“ C ##”或“ c ##”作为前缀。
用户名在PDB中必须是唯一的。
您可以指定该CONTAINER=CURRENT子句，也可以忽略它，因为当当前容器是PDB时，这是默认设置。
ALTER SESSION SET CONTAINER=pdb3;
show CON_name
CREATE USER test_lh3 IDENTIFIED BY aabb123 CONTAINER=CURRENT;
GRANT CREATE SESSION TO test_lh3 CONTAINER=CURRENT;

不加CONTAINER=ALL
CREATE USER test_lh4 IDENTIFIED BY aabb123;
GRANT CREATE SESSION TO test_lh4;

创建通用角色
当前容器必须是根容器。
通用角色的角色名称必须以“ C ##”或“ c ##”为前缀，并且仅包含ASCII或EBCDIC字符。
角色名称在所有容器中必须唯一。
使用CONTAINER=ALL子句创建角色
ALTER SESSION SET CONTAINER=cdb$root;
show CON_name
CREATE ROLE c##test_role1;
GRANT CREATE SESSION TO c##test_role1;

角色给用户
GRANT c##test_role1 TO c##test_lh1 CONTAINER=ALL;

角色授权本地用户
ALTER SESSION SET CONTAINER = pdb3;
GRANT c##test_role1 TO test_lh3;

创建本地角色
如果您连接到普通用户，则必须将容器设置为本地PDB。
本地角色的角色名称不得以“ C ##”或“ c ##”为前缀。
角色名称在PDB中必须唯一。
ALTER SESSION SET CONTAINER = pdb3;
show con_name
CREATE ROLE test_role1;
GRANT CREATE SESSION TO test_role1;

角色给用户
GRANT test_role1 TO c##test_lh2;
GRANT test_role1 TO test_lh4;

创建用户，并向其授予所有容器中的DBA角色
CREATE USER c##lihao IDENTIFIED BY aabb123;
GRANT CREATE SESSION, DBA TO c##lihao CONTAINER=ALL;
CONN c##lihao/aabb123@cdb

查看所有PDB对象
ALTER USER c##lihao SET CONTAINER_DATA=ALL CONTAINER=CURRENT;

恢复默认
ALTER USER c##lihao SET CONTAINER_DATA=DEFAULT CONTAINER=CURRENT;

指定查看的对象
ALTER USER c##lihao SET CONTAINER_DATA=(CDB$ROOT,PDB3) CONTAINER=CURRENT;

增加指定的对象
ALTER USER c##lihao ADD CONTAINER_DATA=(PDB$SEED) CONTAINER=CURRENT;

移除指定的对象
ALTER USER c##lihao REMOVE CONTAINER_DATA=(PDB$SEED) CONTAINER=CURRENT;

查看可查看的对象
SELECT username,owner,object_name,all_containers,container_name
FROM cdb_container_data
WHERE username = ‘C##LIHAO’
ORDER BY 1,2,3;

特定的容器数据对象
ALTER USER c##lihao SET CONTAINER_DATA=(CDBKaTeX parse error: Expected group after '_' at position 21: …PDB3) FOR sys.v_̲datafile CONTAINER=CURRENT;

根容器备份
rman target=/
BACKUP DATABASE ROOT;

PDB备份
rman target=/
BACKUP PLUGGABLE DATABASE pdb1, pdb3;

连接到pdb2容器备份
rman target=sys@pdb2
BACKUP DATABASE;

备份表空间
rman target=sys@pdb1
BACKUP TABLESPACE system, sysaux, users;

备份不同容器的表空间
rman target=sys@cdb
BACKUP TABLESPACE pdb1:system, pdb3:lh;

备份日志和数据库
rman target=/
BACKUP DATABASE PLUS ARCHIVELOG;
BACKUP ARCHIVELOG ALL;

数据库恢复 完全
rman target=/
RUN {
SHUTDOWN IMMEDIATE; # use abort if this fails
STARTUP MOUNT;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN;
}

根容器完全恢复
rman target=/
RUN {
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
RESTORE DATABASE ROOT;
RECOVER DATABASE ROOT;
ALTER DATABASE OPEN;
}

创建临时表
ON COMMIT DELETE ROWS; 提交后没有数据
ON COMMIT PRESERVE ROWS; 会画结束没有数据

撤消表空间
本地撤消模式
select property_name, property_value
from database_properties
where property_name = ‘LOCAL_UNDO_ENABLED’;
是否存在撤消表空间
select con_id, tablespace_name
from cdb_tablespaces
where contents = ‘UNDO’
order by con_id;
共享撤消模式
conn / as sysdba
shutdown immediate;
startup upgrade;
alter database local undo off;
shutdown immediate;
startup;
可以在次查看 本地撤消模式和撤消表空间

闪回
查看
SELECT flashback_on FROM v$database;

启用/禁用闪回数据库
SHUTDOWN IMMEDIATE
STARTUP MOUNT
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
开启闪回
ALTER DATABASE FLASHBACK ON;
定义保留时间
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=10080 SCOPE=BOTH;
CDB还原点
正常还原点。
CREATE RESTORE POINT cdb_huanyuan;
保证还原点
CREATE RESTORE POINT cdb_baozheng GUARANTEE FLASHBACK DATABASE;
PDB还原点
ALTER SESSION SET CONTAINER=pdb3;
CREATE RESTORE POINT pdb3_huanyuan;
CREATE RESTORE POINT pdb3_baozheng GUARANTEE FLASHBACK DATABASE;
删除还原点PDB3
DROP RESTORE POINT pdb3_huanyuan;
DROP RESTORE POINT pdb3_baozheng;
从根容器中创建和删除
创建
CREATE RESTORE POINT pdb3_baozheng FOR PLUGGABLE DATABASE pdb3;
删除
DROP RESTORE POINT pdb3_baozheng FOR PLUGGABLE DATABASE pdb3;
CREATE RESTORE POINT pdb3_baozheng FOR PLUGGABLE
创建
DATABASE pdb3 GUARANTEE FLASHBACK DATABASE;
删除
DROP RESTORE POINT pdb3_baozheng FOR PLUGGABLE DATABASE pdb3;
根容器中创建干净的还原点（需要开启本地撤消表空间）
ALTER PLUGGABLE DATABASE pdb3 CLOSE;
创建正常还原点
CREATE CLEAN RESTORE POINT pdb3_huanyuan FOR PLUGGABLE DATABASE pdb3;
保证还原点
CREATE CLEAN RESTORE POINT pdb3_baozheng FOR PLUGGABLE DATABASE pdb3 GUARANTEE FLASHBACK DATABASE;
ALTER PLUGGABLE DATABASE pdb3 OPEN;
查询
select * from V$RESTORE_POINT
表闪回
CONN C##lihao/aabb123@pdb3
CREATE TABLE t1 (id NUMBER);
INSERT INTO t1 VALUES (1);
COMMIT;
SELECT * FROM t1;
闪回了
ALTER PLUGGABLE DATABASE pdb3 CLOSE;
FLASHBACK PLUGGABLE DATABASE pdb3 TO RESTORE POINT pdb3_baozheng;
ALTER PLUGGABLE DATABASE pdb3 OPEN RESETLOGS;
结果就是看不到创建的表了