oracle11g:
-- 1、建立表空间
CREATE TABLESPACE ss_sysright LOGGING DATAFILE 'E:\app\oracleUser\oradata\ss_sysright\ss_sysright.dbf' SIZE 512M AUTOEXTEND ON NEXT 32M MAXSIZE 1024M EXTENT MANAGEMENT LOCAL;
-- 2、建立临时表空间
create temporary tablespace ss_sysright_temp tempfile 'E:\app\oracleUser\oradata\ss_sysright\ss_sysright_temp.dbf' size 512M autoextend on next 32M maxsize 1024M extent management local;
-- 3、建立用户并为用户赋予表空间、临时表空间
CREATE USER c##ss_sysright IDENTIFIED BY "ss_sysright@123" DEFAULT TABLESPACE ss_sysright TEMPORARY TABLESPACE ss_sysright_temp;
-- 3.1 为用户分配表空间配额
alter user ss_sysright quota unlimited on ss_sysright;
-- 4、为用户赋权
grant connect to ss_sysright ;
grant create session to ss_sysright ; -- 创建会话
GRANT EXECUTE ANY PROCEDURE,CREATE ANY PROCEDURE,ALTER ANY PROCEDURE TO ss_sysright ; -- 執行和查看的權限
grant create sequence to ss_sysright; -- 创建序列的权限
grant create any trigger,alter any trigger to ss_sysright; --触发器权限
commit;
-- DBA: 拥有全部特权,是系统最高权限,只有DBA才可以创建数据库结构。
-- RESOURCE:拥有Resource权限的用户只可以创建实体,不可以创建数据库结构。
-- CONNECT:拥有Connect权限的用户只可以登录Oracle,不可以创建实体,不可以创建数据库结构。
-- 5、删除及回收操作
-- 收回授权
-- REVOKE CONNECT,create session,EXECUTE ANY PROCEDURE FROM workuser;
-- 删除表空间
-- DROP TABLESPACE ss_sysright INCLUDING CONTENTS AND DATAFILES;
-- 删除临时表空间
-- DROP TABLESPACE ss_sysright_temp INCLUDING CONTENTS AND DATAFILES;
-- 删除用户
-- drop user workuser cascade;
oracle12C:
注意,oracle12C存在CDB和PDB(容器数据库,可插拔数据库)
-- 1、建立可插拔数据库PDB
CREATE PLUGGABLE DATABASE ss_sysright ADMIN USER ss_sysright IDENTIFIED BY "ss_sysright@123" ROLES = (connect)
FILE_NAME_CONVERT = ('D:\app\test005\oradata\orcl\pdbseed','D:\app\test005\oradata\ss_sysright\pdbseed');
-- 2、启动数据库,并切换至PDB数据库
alter PLUGGABLE database SS_SYSRIGHT open; -- 启动 pdb:SS_SYSRIGHT
alter session set container=SS_SYSRIGHT; -- 链接 pdb:SS_SYSRIGHT
-- 3、建立表空间
CREATE TABLESPACE ss_sysright LOGGING DATAFILE 'D:\app\test005\oradata\ss_sysright\ss_sysright.dbf' SIZE 512M AUTOEXTEND ON NEXT 32M MAXSIZE 1024M EXTENT MANAGEMENT LOCAL;
-- 4、建立临时表空间
create temporary tablespace ss_sysright_temp tempfile 'D:\app\test005\oradata\ss_sysright\ss_sysright_temp.dbf' size 512M autoextend on next 32M maxsize 1024M extent management local;
-- 5、赋予用户表空间、临时表空间
ALTER USER ss_sysright DEFAULT TABLESPACE ss_sysright;
ALTER USER ss_sysright TEMPORARY TABLESPACE ss_sysright_temp;
-- 6、 为用户分配表空间配额
alter user ss_sysright quota unlimited on ss_sysright;
-- 7、更改密码
ALTER USER ss_sysright IDENTIFIED BY "ss_sysright@123";
-- 8、为用户赋权
grant connect to ss_sysright ;
grant create session to ss_sysright ; -- 创建会话
GRANT EXECUTE ANY PROCEDURE,CREATE ANY PROCEDURE,ALTER ANY PROCEDURE TO ss_sysright ; -- 執行和查看的權限
grant create sequence to ss_sysright; -- 创建序列的权限
grant create any trigger,alter any trigger to ss_sysright; --触发器权限
commit;
-- 9、切换回 CDB空间
alter session set container=CDB$ROOT;
-- 10、删除及回收操作
-- 收回授权
-- REVOKE CONNECT,create session,EXECUTE ANY PROCEDURE FROM workuser;
-- 删除表空间
-- DROP TABLESPACE ss_sysright INCLUDING CONTENTS AND DATAFILES;
-- 删除临时表空间
-- DROP TABLESPACE ss_sysright_temp INCLUDING CONTENTS AND DATAFILES;
-- 删除用户
-- drop user workuser cascade;
-- 删除PDB数据库
-- alter pluggable database ss_sysright close immediate;
-- drop pluggable database ss_sysright including datafiles;
-- 11、查看表空间
-- select * from dba_tablespaces;
-- 12、查看PDB
-- select name,open_mode from v$pdbs;
-- 13、查看用户
-- select * from dba_users
另:数据库链建立方式
-- 1、进入PDB(SS_SYSRIGHT)中:
alter session set container=SS_SYSRIGHT;
-- 2、赋予建立数据库链接的权限
grant create public database link to ss_sysright;
-- 3、建立数据库链接
create public database link LinkSafe
connect to ss_safe identified by "ss_safe@123"
using '(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.6.45)(PORT=1521))
)
(CONNECT_DATA=(SERVER=DEDICATED)
(SERVICE_NAME=ss_safe))
)';
-- 4、删除数据库链接
-- drop public database link LinkSafe;