Oracle 常用初始化命令

共计 1850 个字符，预计需要花费 5 分钟才能阅读完成。

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

更多 Oracle 相关信息见 Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2017-05/144310.htm