Oracle表管理

1. 表空间类型

永久性表空间：一般保存表、视图、过程和索引等的数据

临时性表空间：只用于保存系统中短期活动的数据

撤销表空间：用来帮助回退未提交的事务数据

1. 操作与运用

创建表空间

【语法】 CREATE TABLESPACE 表空间名    DATAFILE '数据文件路径' SIZE 大小    [AUTOEXTEND ON] [NEXT 大小]    [MAXSIZE 大小]; 【说明】[]里面内容可选项；数据文件路径中若包含目录需要先创建 SIZE为初始表空间大小，单位为K或者M AUTOEXTEND ON 是否自动扩展 NEXT为文件满了后扩展大小 MAXSIZE为文件最大大小，值为数值或UNLIMITED（表示不限大小） 【示例】 CREATE TABLESPACE itcast_ts    DATAFILE 'd:\oracle_data\itcast01.dbf' SIZE 10M    AUTOEXTEND ON;

查询表空间

--管理员角色查看表空间 SELECT file_name,tablespace_name,bytes,autoextensible FROM dba_data_files WHERE tablespace_name='ITCAST_TS';

修改表空间

【语法】 ALTER TABLESPACE 表空间名    ADD DATAFILE '文件路径' SIZE 大小    [AUTOEXTEND ON] [NEXT 大小]    [MAXSIZE 大小]; 【示例】 ALTER TABLESPACE itcast_ts    ADD DATAFILE 'd:\oracle_data\itcast02.DBF' SIZE 5M    AUTOEXTEND ON;

删除表空间

【语法】 DROP TABLESPACE 表空间名; DROP TABLESPACE 表空间名 INCLUDING CONTENTS AND DATAFILES; 【说明】 第一个删除语句只删除表空间；第二个删除语句则删除表空间及数据文件 【示例】 DROP TABLESPACE itcast_ts; DROP TABLESPACE itcast_ts INCLUDING CONTENTS AND DATAFILES;

1. 2.数据库用户
   1. 系统常见用户

用户	说明
sys	超级用户，主要用来维护系统信息和管理实例，以SYSDBA或SYSOPER角色登录。密码为在安装时设置的管理口令，如一般设置为：orcl
system	默认的系统管理员，拥有DBA权限，通常用来管理Oracle数据库的用户、权限和存储，以Normal方式登录。密码为在安装时设置的管理口令，如一般设置为：orcl
scott	示范用户，使用users表空间。一般该用户默认密码为tiger

1. 1. 用户管理

Oracle中有个模式（schema）的概念，它是用户的所有数据库对象的集合；一般在创建用户的同时会自动创建一个这样的模式，名称和用户名称一样。

1. 1. 1. 查询系统用户

select * from all_users; 或 select * from dba_users; --更详细的用户信息

1. 1. 1. 解锁用户

【语法】 ALTER USER 用户名 ACCOUNT UNLOCK; 【示例】解锁hr用户 alter user hr account unlock;

1. 1. 1. 创建用户

【语法】 CREATE USER 用户名 IDENTIFIED BY 密码    DEFAULT TABLESPACE 表空间; 【示例】 CREATE USER itcast IDENTIFIED BY itcast    DEFAULT TABLESPACE itcast_ts    TEMPORARY TABLESPACE temp;

1. 1. 1. 修改用户密码

【语法】 ALTER USER 用户名 identified by 密码 【示例】 ALTER USER itcast identified by it;

1. 1. 1. 删除用户

【语法】 DROP USER 用户名 CASCADE; 【示例】 DROP USER itcast CASCADE;

1. DCL数据控制语言
   1. 授予

【语法1】 GRANT 角色权限（角色）[,角色权限] TO 用户; 【示例1】 --授予CONNECT和RESOURCE两个角色 GRANT connect, resource TO itcast; 【备注】使用如下语句可以查看resource角色下的权限 SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='RESOURCE' 【语法2】 GRANT 操作 ON 模式.对象  TO 用户; 【示例2】 --允许用户查看、更新 EMP 表中的记录 GRANT select,update ON SCOTT.emp TO  itcast; --查看当前用户的系统权限 select * from user_sys_privs; --查看当前用户的对象权限 select * from user_tab_privs; --查看当前用户的所有角色 select * from user_role_privs;

1. 1. 撤销

【语法1】 REVOKE 角色权限（角色）[,角色权限] FROM 用户; 【示例1】 --撤销CONNECT和RESOURCE两个角色 REVOKE connect, resource FROM itcast; 【语法2】 REVOKE 操作 ON 模式.对象  FROM 用户; 【示例2】 --撤销用户查看、更新 EMP 表中的记录的操作 REVOKE select,update ON SCOTT.emp FROM  itcast;

1. DDL数据定义语言
   1. 创建表

【语法】 CREATE TABLE <table_name>( column1 DATATYPE [NOT NULL] [PRIMARY KEY], column2 DATATYPE [NOT NULL], ... [constraint <约束名> 约束类型 (要约束的字段) ... ] ); 【说明】 DATATYPE --是Oracle的数据类型 NUT NULL --可不可以允许资料有空的（尚未有资料填入） PRIMARY KEY --是本表的主键 constraint --是对表里的字段添加约束.(约束类型有             Check,Unique,Primary key,not null,Foreign key); 【示例】 create table t_student( s_id number(8) PRIMARY KEY, s_name varchar2(20) not null, s_sex varchar2(8), clsid number(8), constraint u_1 unique(s_name), constraint c_1 check (s_sex in ('MALE','FEMALE')) ); --从现有的表创建表及复制其数据 【语法】 CREATE TABLE <table_name> as <SELECT 语句> 【示例】 create table emp as select * from scott.emp; create table emp as select empno,ename from scott.emp --表结构只有empno和ename两个字段及该两字段对应的数据 --如果只复制表的结构不复制表的数据则: create table emp as select * from scott.emp where 1=2;

1. 1. 修改表

【语法1】向表中添加新字段 ALTER TABLE <table_name> ADD (字段1 类型 [NOT NULL], 字段2 类型 [NOT NULL] ... ); 【示例1】 alter table t_student add (s_age number(3),s_address varchar2(20)); 【语法2】修改表中字段 ALTER TABLE <table_name> MODIFY(字段1 类型,字段2 类型 ... ); 【示例2】 alter table t_student modify(s_name varchar2(50),s_address varchar2(100)); 【语法3】删除表中字段 ALTER TABLE <table_name> DROP(字段1,字段2... ); 【示例3】 alter table t_student drop(s_age,s_address); 【语法4】修改表字段名称 ALTER TABLE <table_name> RENAME COLUMN 原字段名称 TO 新字段名称; 【示例4】 alter table t_student rename column s_id to s_no;

1. 1. 删除表

【语法1】 --删除表结构及数据（删除后可在回收站查看并恢复） DROP TABLE <table_name>; --删除表结构及数据（删除后不可在回收站查看并恢复） DROP TABLE <table_name> PURGE; 【示例1】 drop table t_student;

1. 1. 回收站
      1. 查看回收站

--查看回收站 show recyclebin; 或 select * from recyclebin;

1. 1. 1. 清空回收站

--清空回收站 purge recyclebin;