ORACLE12c常用操作sql命令

由于浏览器不支持flash,我们无法进入oracle12c的em页面,所以需要sql进行操作

1. 查看当前context

select sys_context('userenv','con_name') from dual;

2. 查看所有db

select con_id,dbid,name,open_mode from v$pdbs;

3. 切换cdb

alter session set container = CDB$ROOT;

4. 更改默认密码过期策略

SELECT username, PROFILE FROM dba_users;

SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

5. 从pdbseed快速创建pdb

CREATE PLUGGABLE DATABASE <pdb>

ADMIN USER <user> IDENTIFIED BY <password>

STORAGE (MAXSIZE 10G)

DEFAULT TABLESPACE <tablespace>

DATAFILE '/opt/oracle/oradata/ORCLCDB/datafile/<filepath>/<filename>.dbf' SIZE 1024M AUTOEXTEND ON NEXT 32M

PATH_PREFIX = '/opt/oracle/oradata/ORCLCDB/datafile/<filepath>/'

FILE_NAME_CONVERT = ('/opt/oracle/oradata/ORCLCDB/pdbseed/', '/opt/oracle/oradata/ORCLCDB/datafile/<filepath>/');

6. 给用户赋权限

grant connect, create table, create sequence, create view, create session, dba to <user>;

alter user <user> quota unlimited on <tablespace>;

7. 创建触发器自动开启pdb

CREATE TRIGGER open_all_pdbs
AFTER STARTUP
ON DATABASE
BEGIN
EXECUTE IMMEDIATE 'alter pluggable database all open';
END open_all_pdbs;
/

8. 用户解锁

alter user <user> account unlock;

9. 更改密码

alter user <user> identified by <password>;