预备备:打开数据库
- 切换到oracle用户下准备开始
- su oracle
- --运行 oracle 配置文件
- source /home/oracle/.bash_profile
- --启动监听服务
- lsnrctl start
- --运行 sqlplus 命令
- sqlplus /nolog
- --以管理员登录
- conn / as sysdba
- --启动数据库
- Startup
查看连接
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
1、创建的前提是开启了cdb,所以下查看
select name,cdb from v$database;
NAME CDB
--------- ---
ORCL YES
2: 指定创建pdb的目录。 目录为oracle的安装目录
alter system set db_create_file_dest='/home/oracle/app/oracle/oradata';
3: 创建pdb使用的表空间名字是test
create tablespace test datafile '/home/oracle/app/oracle/oradata/orcl/test.dbf' size 2G autoextend on next 1G maxsize unlimited extent management local autoallocate;
4: 创建 pdb, 并指定默认表空间, 默认是system表空间, 创建一个pdb专用的表空间比较好管理
create pluggable database test admin user test identified by test roles=(dba) default tablespace test;
5: 查看是否创建成功
show pdbs
--------观察信息-----MOUNTED代表没开
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
4 TEST MOUNTED
6: 直接开启数据库test
alter pluggable database test open;
6: 切换到某个pdb中比如test
alter session set container=test
7: 启动pdb
startup
8:用户授权
GRANT DBA to test;
grant connect,resource,unlimited tablespace to test;
grant create any directory to test;
grant drop any directory to test;
-------------------------另外一中方式------创建多个space---------------------
1创建pdb
create pluggable database pdbgeo admin user pdbuser identified by pass1234 roles=(dba);
2、开pdb
alter pluggable database pdbgeo open
3:、切换到某个pdb中比如pdbgeo
alter session set container=PDBGEO
-----------创建表空间----------------------
1: 创建可连接用户
创建用户表空间,红色部分是自己的目录,dbf结尾create tablespace ggsj datafile
create tablespace ggsj datafile '/home/oracle/app/oracle/oradata/orcl/20/ggsj.dbf' size 2G autoextend on next 1G maxsize unlimited extent management local autoallocate
2 创建用户指定默认表空间
create user XSDTGGSJ identified by XSDTGGSJ default tablespace ggsj;
赋权gei用户
grant dba,connect,resource,create session,create table,create view,unlimited tablespace to XSDTGGSJ ;
grant create any directory,drop any directory to XSDTGGSJ ;
------repeat-------------------------------
1
create tablespace wdsj datafile '/home/oracle/app/oracle/oradata/orcl/20/wdsj.dbf' size 2G autoextend on next 1G maxsize unlimited extent management local autoallocate
2 创建用户指定默认表空间
create user XSDTWDSJ identified by XSDTWDSJ default tablespace wdsj
赋权gei用户
grant dba,connect,resource,create session,create table,create view,unlimited tablespace to XSDTWDSJ;
grant create any directory,drop any directory to XSDTWDSJ;
------repeat-------------------------------
1
create tablespace xxsj datafile '/home/oracle/app/oracle/oradata/orcl/20/xxsj.dbf' size 2G autoextend on next 1G maxsize unlimited extent management local autoallocate
2 创建用户指定默认表空间
create user XSDTXXSJ identified by XSDTXXSJ default tablespace xxsj
赋权gei用户
grant dba,connect,resource,create session,create table,create view,unlimited tablespace to XSDTXXSJ;
grant create any directory,drop any directory to XSDTXXSJ;
3、10: 使用 tests用户在qlsql远程连接。
11:使用sqlplus连接
sqlplus tests/tests@你的IP/test
#修改密码(可选)
alter user ADMIN identified by "xag123";
#账户锁定后解锁命令(可选) sys or system
alter user ADMIN account unlock;
#设置用户密码无限次尝试登录
alter profile default limit failed_login_attempts unlimited;
#设置用户密码不过期:
alter profile default limit password_life_time unlimited;
#查看配置的参数
select profile,RESOURCE_NAME,resource_type,limit from dba_profiles where
RESOURCE_NAME in('FAILED_LOGIN_ATTEMPTS','PASSWORD_LIFE_TIME') and profile='DEFAULT';
卸载pdb;
1: 关闭 pdb;
alter pluggable database test close immediate;
2:卸载pdb
使用keep datafiles保留PDB8的数据文件。,也可以使用including datafiles彻底删除PDB的数据文件。
drop pluggable database test including datafiles;