Oracle 常用初始化命令
–创建一个表空间
CREATE TABLESPACE MYSPACE DATAFILE ‘D:/MYSPACE.DBF’ SIZE 10M AUTOEXTEND ON
–指定某个用户的默认的表空间是MYSPACE
ALTER USER SYSTEM IDENTIFIED BY NIIT DEFAULT TABLESPACE MYSPACE QUOTA UNLIMITED ON MYSPACE
COMMIT
–删除表空间
DROP TABLESPACE MYSPACE
–删除表空间，带内容一起删除
DROP TABLESPACE MYSPACE INCLUDING CONTENTS
–删除表空间及其内容和物理文件
DROP TABLESPACE MYSPACE INCLUDING CONTENTS AND DATAFILES
COMMIT
–创建临时表空间
CREATE TEMPORARY TABLESPACE MYTEMP TEMPFILE ‘D:/MYTEMP.DBF’ SIZE 30M AUTOEXTEND ON
DROP TABLESPACE MYTEMP INCLUDING CONTENTS AND DATAFILES
–指定默认的表空间和临时表空间
ALTER USER TOM IDENTIFIED BY TOM123 DEFAULT TABLESPACE MYSPACE TEMPORARY TABLESPACE MYTEMP QUOTA UNLIMITED ON MYSPACE
–创建一个用户，指定密码，密码必须用字母开头
CREATE USER TOM IDENTIFIED BY TOM123
–删除用户
DROP USER JACK1
–删除用户及所有对象
DROP USER TOM CASCADE
–改变用户的表空间
ALTER USER TOM IDENTIFIED BY TOM1234 DEFAULT TABLESPACE MYSPACE QUOTA UNLIMITED ON MYSPACE
–tom没有权限，系统管理员sys,system赋予权限
–赋予系统权限,登陆，连接权限
GRANT CREATE SESSION TO TOM WITH ADMIN OPTION
–赋予创建表的权限
GRANT CREATE TABLE,CREATE USER,CREATE SEQUENCE TO TOM
–收回权限
REVOKE CREATE SESSION,CREATE USER,CREATE TABLE,CREATE SEQUENCE FROM TOM
–赋予TOM系统权限并将权限传递
GRANT CREATE SESSION,CREATE USER TO TOM WITH ADMIN OPTION
–收回权限
REVOKE CREATE SESSION,CREATE USER FROM TOM
–赋予TOM对象权限
GRANT SELECT ON SCOTT.EMP TO TOM
–收回对象权限
REVOKE SELECT ON SCOTT.EMP FROM TOM
–将预定义角色赋给用户
GRANT CONNECT TO TOM
–收回角色
REVOKE CONNECT FROM TOM
REVOKE RESOURCE FROM TOM
DROP USER TOM CASCADE
CREATE USER TOM IDENTIFIED BY TOM123 DEFAULT TABLESPACE MYSPACE QUOTA UNLIMITED ON MYSPACE
–RESOURCE角色给TOM
GRANT RESOURCE TO TOM
COMMIT
—自定义角色
–1、创建角色
CREATE ROLE ADS
–2、赋予角色相应权限
GRANT CREATE SESSION,CREATE TABLE,CREATE USER TO ADS
–3、将角色赋给相应用户
GRANT ADS TO TOM
–表空间迁移
–1、表空间下线
ALTER TABLESPACE MYSPACE OFFLINE
–2、修改更改后的物理文件地址
ALTER DATABASE RENAME FILE ‘C:/MYSPACE.DBF’ TO ‘E:/MYSPACE.DBF’
–3、表空间上线
ALTER TABLESPACE MYSPACE ONLINE
–改变表空间后创建表所处的表空间
CREATE TABLE MTABLE
(
TNAME VARCHAR2(20)
)
COMMIT
：http://www.linuxidc.com/Linux/2017-05/144310.htm
