oracle表空间相关常用命令小结：
1、ALTER DATABASE SET DEFAULT BIGFILE TABLESPACE; //修改表空间数据文件类型
2、ALTER TABLESPACE 旧表空间名称 TO 新表空间名称; //修改表空间名称
3、select * from v$database;
show parameter db //查看数据库相关信息
4、select * from v$instance;
show parameter instance 集装箱运费
//查询数据库实例名
5、select * from v$parameter t where t.NAME='db_domain';
show parameter domain //查询数据库域名
6、select * from v$parameter t where t.NAME='service_name';
show parameter service_name //查询数据库服务名
7、grant connect to sys identified by 111111;
alter user sys identified by 111111;
password sys 注：这个命令不适用于sys用户 //修改用户的密码
8、orapwd file=%ORACLE_HOME%\database\pwd
password=XXXX //设置的新密码
entries=30 //同时连接到sys用户的最大数
9、show parameter nls //查询数据库中字符集的参数
10、select sysdate from dual //查询数据库日期
11、select * from nls_database_parameters;
select * from v$nls_parameters;
select * from props$; //查询数据库安装时确定的参数值
12、export NLS_LANG="simplified chinese_china".zhs16gbk或zhs16cgb231280
export ORA_NLS33=$ORACLE_HOME/nls/admin/data //安装oracle时设置中文字符集的环境变量
13、update props$ set value$='ZHS16GBK' where name = 'NLS_CHARACTERSET'; //修改oracle数据库核心字符集
14、exp giapsys/giap file=/home/giapsys.dmp log=/home/giapsys.log direct=y //导出giapsys用户的所有数据
15、create pfile from spfile; //使用默认的服务器参数文件在默认路径中创建默认的文本参数文件
16、create spfile from pfile; //使用默认的文本参数文件在默认路径中创建默认的服务器参数文件
iphone5
17、select t.tablespace_name,t.bytes,t.blocks,t.autoextensible,t.maxbytes,t.file_name from dba_data_files t;
select * from v$datafile //查询数据库数据文件与表空间的信息
18、select t.NAME,t.VALUE from v$parameter t where t.NAME='db_block_size'
show parameter db_block_size //查询数据块db_block_size的大小
19、select t.tablespace_name,t.file_id,t.block_id,t.bytes,t.blocks from dba_free_space t;
//查询表空间的使用情况，bytes表示该表空间空闲表空间大小，blocks标示剩余块数
20、alter database datafile 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\GIAPSYS' resize 100m;
//重新为数据文件划分尺寸，在原有的基础上进行增加，前提是AUTOEXTENSIBLE的状态为YES或ON，否则无法扩充
21 create tablespace AAA //创建表空间名称为AAA
datafile 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\AAA01' size 1m //数据文件所在目录，大小为1M
autoextend on //自动扩展状态为ON（可扩展）
next 1m //下一次将要扩展的文件大小
maxsize 100m //数据文件允许扩展到最大的尺寸，如果maxsize unlimited时则表示数据文件自动扩展，不受限制，它的限制条件为操作系统磁盘可用空间
22、alter tablespace AAA //增加表空间的数据文件，AAA为空间名
add datafile 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\AAA02' size 1m //增加数据文件所在的目录，大小为1M
autoextend on //自动扩展状态为ON（可扩展）
next 1m //下一次将要扩展的文件大小
maxsize 100m //数据文件允许扩展到最大的尺寸，如果maxsize unlimited时则表示数据文件自动扩展，不受限制，它的限制条件为操作系统磁盘可用空间
23、alter database datafile 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\AAA02' autoextend off
//修改数据文件的自动扩展属性,状态为off
24、alter database datafile 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\AAA02' autoextend on next 1m maxsize 100m
//修改数据文件的自动扩展属性,状态为on ,同时指出下次扩展文件的大小，和扩展最大的尺寸
25、select * from dba_tablespaces //查询表空间存储参数
26、select t.table_name,t.initial_extent,t.next_extent,t.min_extents,t.max_extents,t.pct_increase from user_tables t
select b.segment_name,b.tablespace_name,b.extent_id,b.extent_id,b.bytes,b.blocks from user_extents b
ORACLE 表空间扩展方法
第一步：查看表空间的名字及文件所在位置：
select tablespace_name,
file_id,
file_name,
round(bytes / (1024 * 1024), 0) total_space
from sys.dba_data_files
order by tablespace_name
第二步：增大所需表空间大小：
alter database datafile '表空间位置' resize 新的尺寸
例如：
alter database datafile '\oracle\oradata\anita_2008.dbf' resize 4000m
对于oracle数据库的表空间，除了用手动增加大小外，还可以增加数据文件等方式扩展表空间大小。
方法一：增加数据文件个数
alter tablespace 表空间名称 add datafile '新的数据文件地址' size 数据文件大小
例如：
alter tablespace ESPS_2008 add datafile '\oracle\oradata\anita_2010.dbf' size 1000m
方法二：设置表空间自动扩展。
alter database datafile '数据文件位置' autoextend on next 自动扩展大小 maxsize 最大扩展大小
例如：
alter database datafile '\oracle\oradata\anita_2008.dbf' autoextend on next 100m maxsize 10000m
方法三：查询表空间使用情况：
select a.tablespace_name,
a.bytes / 1024 / 1024 "sum MB",
(a.bytes - b.bytes) / 1024 / 1024 "used MB",
b.bytes / 1024 / 1024 "free MB",
round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "used%"
from (select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes, max(bytes) largest
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by ((a.bytes - b.bytes) / a.bytes) desc;
一.数据文件的扩展
1.
alter database datafile '$home/data/df1.dbf' antoextend on;
alter database datafile '$home/data/df1.dbf' autoextend off;
2.创建时启用
create tablespace testuser datafile '$home/data/df1.dbf'
autoExtend on next 10M maxsix 500M
3.DBA_DATA_FILES ,AUTO_EXTEND
select name,auto_extend from dba_data_files;
4.自动扩展属性-- 浪费性能 一般关掉 -----》手动扩展
--》1.alter database datafile '/u03/oradata/userdata02.dbf' resize 200M
--> 2. 增加新文件，alter tablespace userdata add datafile '$home/' size 500M
二.移动非SYSTEM 数据文件
--》使表空间脱机
alter tablespace userdata offline;
--》OS copy 文件
--》重命名
alter tablespace userdata rename datafile '11' to '22';
--》使表空间连机
--》使OS命令删除文件
三.移动SYSTEM数据文件
1.关闭数据库
2.使用OS 移动
3.startup mount
4.alter database rename file 'system01.dbf' to '/user/system01.dbf';
四.删除表空间 --system 表和有激活undo的段无法，删除
1.including contents 删掉所有的段。
2.including contents and datafiles 删除掉对应的操作系统文件。
3.cascade constraints;
五.得到表空间的信息
得到表空间的信息
dba_tablespaces
v$tablespace
得到 数据文件的信息
dba_data_files;
v$datafile
得到临时文件的信息
dba_temp_files;
v$tempfile;
六.临时表空间temp（只有一个临时段） --先 sort memory,后 sort disk ---v$sysstat
select * from v$sysstat where name like 'sortx';
临时表的工作方式:一次分配，循环，共享使用。
1.创建
create temporary tablespace temp
tempfile '/u01/oradata' size 10M;
2.默认临时表空间（只能有一个）
创建用户时，如果没有指定临时表空间，会使用数据库级别的临时表空间。
3.修改数据库临时表空间
alter database default temporary tablespace temp2;
4.默认表空间不能被删除，不能offline.
Oracle 10g表空间创建的完整步骤
当在数据库中创建用户时，基于应用性能和管理方面的考虑，最好为不同的用户创建独立的表空间。
1.创建表空间
不论是Lnux环境，还是Wndows环境，都要首先创建好表空间的存放路径，根据自己的情况修改。如：
/opt/oracle/oradata/cocis 或 D:\oracle\oradata\cocis
若事先不创建该目录路径，则在创建表空间的时候会出错。
通过pl/sql登录到Oracle数据库上，然后执行菜单:文件/新建/命令窗口 ，打开一个命令窗口然后在该命令窗口中执行脚本创建和删除表空间
引用
创建表空间
Sql>create tablespace histdb datafile 'D:\oracle\product\10.2.0\oradata\orcl\histdb.dbf' size 200m autoextend on next 10m maxsize unlimited;
Sql>alter database datafile 'D:\oracle\product\10.2.0\oradata\orcl\histdb.dbf' autoextend on;
- DATAFILE: 表空间数据文件存放路径
- SIZE: 起初设置为200M
- UNIFORM: 指定区尺寸为128k,如不指定，区尺寸默认为64k
- 空间名称histdb 与 数据文件名称 histdb.dbf 不要求相同,可随意命名.
- AUTOEXTEND ON/OFF 表示启动/停止自动扩展表空间
- alter database datafile ' D:\oracle\product\10.2.0\oradata\orcl\histdb.dbf ' resize 500m; //手动修改数据文件大小为500M
删除表空间
DROP TABLESPACE histdb INCLUDING CONTENTS AND DATAFILES;
表空间已创建。
2.为应用创建用户
创建用户的同时，为用户指定缺省的永久表空间和临时表空间。
SQL> create user cocis identified by cocis
2 default tablespace cocis
3 temporary tablespace temp;
用户已创建。
SQL> select username,default_tablespace,temporary_tablespace
2 from dba_users
3 where username='COCIS';
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
COCIS COCIS TEMP
SQL> select username,user_id,password,default_tablespace,temporary_tablespace
2 from dba_users
3 where username='COCIS';
USERNAME USER_ID PASSWORD TEMPORARY_TABLESPACE
COCIS 61 E031F623C0F15D34 COCIS
3.权限的授予
SQL> grant connect,resource to cocis;
授权成功。
注释：当用户创建之后，一般只需要授予CONNECT和RESOURCE这两个角色即可。若要单独进行授权，则需执行单独的授权命令，如grant create table to cocis;等。
如果要授予SYSDBA的权限给用户则用如下命令
SQL> grant SYSDBA to cocis;
SQL> revoke unlimited tablespace from cocis;
撤销成功。
SQL> alter user cocis quota unlimited on cocis;
用户已更改。
注释：为了更严谨的管理，可以回收用户的UNLIMITED TABLESPACE权限，然后对用户的空间限额进行单独授权。
Oracle管理命令集锦
创建表空间：
CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K BLOCKSIZE 8K;
CREATE TEMPORARY TABLESPACE lmtemp3 TEMPFILE '/u02/oracle/data/lmtemp301.dbf' SIZE 25M TABLESPACE GROUP group1;
CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
修改表空间：
ALTER TABLESPACE lmtbsb ADD DATAFILE '/u02/oracle/data/lmtbsb02.dbf' SIZE 1M;
ALTER TABLESPACE bigtbs AUTOEXTEND ON NEXT 20G;
ALTER TABLESPACE bigtbs RESIZE 80G;
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' OFFLINE;
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' ONLINE;
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' DROP INCLUDING DATAFILES;
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' RESIZE 18M;
ALTER TABLESPACE flights READ ONLY;
ALTER TABLESPACE flights READ WRITE;
表空间数据文件管理：
ALTER TABLESPACE users ADD DATAFILE '/u02/oracle/rbdb1/users03.dbf' SIZE 10M AUTOEXTEND ON NEXT 512K MAXSIZE 250M;
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf' AUTOEXTEND OFF;
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' RESIZE 100M;
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' ONLINE;
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' OFFLINE;
ALTER TABLESPACE users OFFLINE NORMAL;
ALTER TABLESPACE users RENAME DATAFILE '/u02/oracle/rbdb1/user1.dbf','/u02/oracle/rbdb1/user2.dbf' TO '/u02/oracle/rbdb1/users01.dbf', '/u02/oracle/rbdb1/users02.dbf';
ALTER TABLESPACE example DROP DATAFILE '+DGROUP1/example_df3.f';
ALTER TABLESPACE lmtemp DROP TEMPFILE '/u02/oracle/data/lmtemp02.dbf';
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' DROP INCLUDING DATAFILES;
归档路径管理：
show parameter archive;
alter system set log_archive_dest_1='location=d:\oracle\archivelog' scope=both;
create pfile from spfile;
ALTER SYSTEM SWITCH LOGFILE;
删除表空间：
DROP TABLESPACE users INCLUDING CONTENTS;
DROP TABLESPACE users INCLUDING CONTENTS AND DATAFILES;
开启归档模式：
SHUTDOWN
STARTUP MOUNT
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE ARCHIVELOG MANUAL;
ALTER DATABASE OPEN;
SHUTDOWN IMMEDIATE
系统进行一次归档：
ALTER SYSTEM ARCHIVE LOG ALL;
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=3;
导出部分用户的数据：
exp system/password@orcl owner=(user1,user2,user3) file=c:\test01.dmp
导入部分用户的数据：
imp system/password@orcl fromuser=(user1,user2,user3) touser=(eia,eia_hn,eia_test) file=c:\test01.dmp
通过pl/sql登录到Oracle数据库上，然后执行菜单:文件/新建/命令窗口 ，打开一个命令窗口然后在该命令窗口中执行脚本创建和删除表空间
创建表空间
Sql>create tablespace histdb datafile 'D:\oracle\product\10.2.0\oradata\orcl\histdb.dbf' size 200m autoextend on next 10m maxsize unlimited;
Sql>alter database datafile 'D:\oracle\product\10.2.0\oradata\orcl\histdb.dbf' autoextend on;
- DATAFILE: 表空间数据文件存放路径
- SIZE: 起初设置为200M
- UNIFORM: 指定区尺寸为128k,如不指定，区尺寸默认为64k
- 空间名称histdb 与 数据文件名称 histdb.dbf 不要求相同,可随意命名.
- AUTOEXTEND ON/OFF 表示启动/停止自动扩展表空间
- alter database datafile ' D:\oracle\product\10.2.0\oradata\orcl\histdb.dbf ' resize 500m; //手动修改数据文件大小为500M
删除表空间
DROP TABLESPACE histdb INCLUDING CONTENTS AND DATAFILES;
oracle学习总结
1.创建表：
create table IT_EMPLOYEES(
EMPLOYEE_ID NUMERIC(6) NOT NULL UNIQUE,
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(25) NOT NULL,
EMAIL VARCHAR2(25),
PHONE_NUMBER VARCHAR2(10),
JOB_ID VARCHAR2(10),
SALARY NUMBER(8,2),
MANAGER_ID NUMBER(6)
);
2.创建视图：
(1)
create view prog_employees_1
as
select employee_id,first_name,last_name,email,
phone_number,salary,manager_id from it_employees
where job_id='IT_PROG';
(2)
create view prog_employees_1
as
select employee_id,first_name,last_name,email,
phone_number,salary,manager_id from it_employees
where job_id='IT_PROG';
with check option;
（3）删除视图
drop view prog_employees;
3.创建索引
（1）Create 【unique】【cluster】INDEX<索引名> ON<表名>(<列名> <次序>)
例：
create index IT_LASTNAME ON IT_EMPLOYEES(LAST_NAME);
用户可以在查询频率最高的列上建立聚簇索引，由于聚簇索引是将索引和表记录放在一起存储，所以在一个基表上只能建立一个聚簇索引。在建立聚簇索引之后，由于更新索引列数据时会导致表中记录的物理顺序的变更，系统代价较高，因此对于经常更新的列不适宜建立聚簇索引。
（2）删除索引
Drop index <索引名>;
4.Alter
(1) 增加一列
alter table It_Employees add BIRTH_DATE DATE;
（2）修改字段
alter table It_Employees modify manager_id number(8);
（3）删除约束条件
alter table It_Employees drop unique(employee_id);
4.select / group by /having
注意：select子句后面只有两类表达式，统计函数和进行分组的列明。
Having 子句对分组的结果进一步筛选，having子句与分组有关，而where子句与单个行有关。
select deptno,avg(sal),sum(sal),max(sal),min(sal),count(sal) from emp group by deptno;
select deptno,avg(sal),sum(sal),max(sal),min(sal),count(sal),count(*) from emp group by deptno having avg(sal) > 2000;
5.内连接inner join /外连接（左left outer join/右外连接 right outer join/全外连接 full
区别：内连接进行多表查询时，返回的查询结果集中仅仅包含符合查询条件（where 条件和having条件）和连接条件的行。内连接消除了与另一个表中的任何行不匹配的行，而外连接扩展了内连接的结果集，除返回所有匹配的行外，还返回不匹配的行。
select empno,ename,dname from emp inner join dept on emp.deptno = dept.deptno where job='SALESMAN';
1 7499 ALLEN SALES
2 7654 MARTIN SALES
3 7844 TURNER SALES
4 7521 WARD SALES
5 5555 lipeng OPERATIONS
测试数据：deptno为null的
insert into
emp values(6666,'lipeng','SALESMAN',6666,to_date('2013-04-02','yyyy/mm/dd'),5000.00,1000.00,null);
select empno,ename,dname from emp left outer join dept on emp.deptno = dept.deptno where job='SALESMAN'
1 5555 lipeng OPERATIONS
2 6666 lipeng
3 7499 ALLEN SALES
4 7521 WARD SALES
5 7654 MARTIN SALES
6 7844 TURNER SALES
从结果可以看出：做外连接不仅包含连接相匹配的行，而且还包含左表emp中所有满足where限制的行，而不论是否与右表相匹配。
5.union /union all/intersect/minus
union 将集合中的重复记录滤除，而union all包含两个子结果集重复的行。
select ename,SAL from emp where ename like 'S%' OR ename like 'J%'
union all
select ename,SAL from emp where ename like 'J%' OR ename like 'C%';
6.子查询in/exists/比较运算符
（1）select empno,ename,deptno
from emp where deptno in (select deptno from dept where loc='CHICAGO');
执行顺序：先执行括号内的子查询，然后将查询到的deptno结果跟emp中的deptno进行比较，若列值存在于这些返回值中，则外层查询结果会在结果集中显示该行。
（2）select empno,ename from emp where exists (
select * from dept where emp.deptno = dept.deptno and loc='CHICAGO'
);
（3）查询emp表，将将薪资大于本职位平均薪资的雇员信息显示出来。
select empno,ename,sal,job from emp
where job='SALESMAN' and sal >(
select avg(sal) from emp where job='SALESMAN'
);
7.数据操纵 INSERT/UPDATE/DELETE/TRUNCATE
update emp
set sal =
(select avg(sal) from emp where job='SALESMAN')
where empno=6666;
commit;
delete from emp where empno=5555;
DELETE/TRUNCATE区别：
如果确定要删除表中所有记录时，建议使用TRUNCATE，因为TRUNCATE删除数据时要比DELETE快的多。但是TRUNCATE删除数据后，不能用rollback来恢复数据，但是delete可以用。
Reuse storage/drop storage
Truncate table emp reuse storage;
Reuse storage表示删除记录后保存记录占用的空间。
drop storage 表示删除记录后立刻回收记录占用的空间。
8.授权grant/回收revoke
grant select on emp to sup2db;
revoke select on emp from sup2db;
9.字符类函数
select ASCII('A') big_A,ASCII('a') small_a from dual;
select CHR(65),CHR(97) from dual;
select concat('oracle','11g') oraclename from dual;
select initcap('hello world') name from dual;
select replace('feelblue','blue','yellow') from dual;
日期函数：
select add_months(hiredate,1) from emp;
第七章 oracle数据库管理操作
一．增加安全性
解决的问题：例如，销售经理需要访问数据库中有关该部门员工的信息。但是该经理没有理由访问有关其它部门员工的信息。
(1)以系统管理员的身份进行登录：
conn sys/change_in_stall as sysdba;
（2）激活人力资源示例数据库账户，连接到HR示例数据库
Alter user hr identified by hr account unlock;
Conn hr/hr;
查看hr用户下面的所有表：
（1）连接到hr用户
（2）Select table_name from user_tables;
或者使用 Select * from tab;
(3)为销售经理创建用户ID，用户名salesmanger，密码sales，并将connect权限授予销售经理。
(4) Conn sys/change_in_stall;
grant connect to salesmanger identified by sales;
（4）定义一个查看员工编号和姓名的视图，将数据库中员工等信息隐藏起来。
create view emp_sales
as
select employee_id,first_name,last_name from hr.employees;
}
(5)授予salesmanger查看emp_sales视图的权限
grant select on emp_sales to salesmanger;
（6）让销售经理以用户salesmanger登录数据库后，销售经理可以查看员工的编号和姓名，但是不允许查看员工的工资信息，这样在一定程度保证了数据的安全性。
conn salesmanger/sales
select * from sys.emp_sales;
select * from sys.emp_sales;
二．隐藏数据的复杂性
现有一机场数据库，拥有pilotSkills表和hanger表，
其中表pilotSkills描述了飞行员和他们能够驾驶的飞机信息，表hanger描述了停在飞机棚中的飞机信息。
现在要求查询能够驾驶飞机棚中每一架飞机的飞行员的姓名。
--创建pilotSkills表
create table pilotSkills(
pilot char(15) not null,
plane char(15) not null,
primary key(pilot,plane)
);
create table hanger(
plane char(15) primary key
);
创建视图，实现“找出能够驾驶飞机棚中每一架飞机的飞行员的姓名”
Create view QualifiedPilots(pilot)
As
Select ps.pilot from pilotSkills ps,hanger h
Where ps.plane = h.plane
Group by ps.pilot
Having count(ps.plane) = (select count(plane) from hanger);
select pilot from QualifiedPilots;
三、实现记录的唯一性
--增加主键
Alter table stu add constrait c1 primary key(sno);
--删除主键约束
Alter table stu drop constrait c1;
--创建一个名为ind1的唯一索引
Create unique index ind1 on emp(empno);
当插入一条重复数据的时候会报错。
四．实现数据的完整性
Alter table student modify sname not null;
第八章 数据库用户管理
1.创建一个用户使其具有登陆，连接的系统权限
create user stu identified by stu;
grant create session to stu;
2.用户HR将Employees表的查询、查询、更改表的对象的权限授予stu，那么stu具有了对HR的employees表的select对象权限，但不具备其它对象权限。
conn hr/hr;
grant select,insert,update on employees to stu;
select first_name,last_name,job_id,salary from hr.employees where salary>15000;
3.授权角色
数据字典dba_roles可以了解数据库中全部的角色信息。
select * from dba_roles;
角色connect、resource和DBA主要用于数据库管理。对于数据库管理员分别授予Connnect、resources和DBA角色。
创建角色:
create role access_database;
Create role access_database identified by 123;
(2) 授权
grant create session,create table,create view to access_database;
（3）可将角色授予用户，使用户获得该角色所拥有的所有权限。
grant access_database to scott;
4.修改用户的默认角色
（1）设置用户的角色失效
Alter user scott default role none;
用户角色失效后，该用户中的权限将全部丢失。用户连接数据库权限create session存储于ACCESS_DATEBASE中，当该角色失效后，用户scott就不能登录到数据库中。
（2）设置用户角色生效
Alter user scott default role all;
（3）查看session_roles视图，确认会话所用的角色
connect stu/stu;
select * from session_roles;
结果：
ROLE
CONNECT
RESOURCE
(4)为当前用户启用ACCESS_DATABASE角色。
Set role access_database;
5.回收权限
*逐一回收
Connect sys/change_in_stall as sysdba;
（1）系统权限的回收
收回scott用户的select any dictionary系统权限。
Revoke select any dictionary from scott;
（2）对象权限的回收
Hr用户回收scott对employees表的select对象权限。
Connect hr/hr
Revoke select on employees from scott;
用户HR将基表Employees的所有权限从public用户回收。
Revoke all on employees from public;
6.删除角色
Drop role access_database;
删除用户：
Drop user stu;
7.使用数据库连接
是为了访问远程数据库而创建的数据库通信链路。
链接到指定的用户：
CREATE DATABASE LINK link_name CONNECT TO USER IDENTIFIED BY password USING server_name;
link_name:表示要链接到远程数据库名。
Server_name：表示远程数据库的服务名。
数据库空间管理
1.设置其它表空间初值
例如学生信息库，要把有关学生信息的一些表放到一个表空间中，如果有2000多名学生，则存储学生个人信息最多也不超过100MB的空间，但是为了保险，我们可以设置两个数据文件，每个数据文件的大小均设置为100MB
create Tablespace student_info
datafile 'C:\oracle\product\10.2.0\oradata\orcl\student01.dbf'size 100M, 'C:\oracle\product\10.2.0\oradata\orcl\student02.dbf'size 100M
default storage(
initial 10M
next 10M
minextents 1
maxextents 10
pctincrease 20
)
online;
说明：
Storage 指定表空间的存储参数，这些参数对于数据库的性能影响很大，选着时要慎重。
initial 10M：--表空间student_information初始空间大小为10MB
next 10M： -- 当初始区间填满后，分配第二个区间的大小为10MB
pctincrease 20：--当在填满时，按照20%的增长速率分配区间大小。
minextents 1：-- 初始为该表空间分配1个区间
maxextents 10： --最多为该表空间分配10个区间
2.空间充足的管理
查看表空间使用情况，使用以下方法避免空间的不足：
（1）使用数据字典动态监视
这里使用的数据字典是dba_free_space和user_free_space,可以查看其内容来得到有关表空间的空间信息。
以system的身份登录：
select * from dba_free_space;
（2）向表空间增加数据文件
alter tablespace student_info
add datafile
'C:\oracle\product\10.2.0\oradata\orcl\student03.dbf'
size 2M;
注意：通过上述方法给表空间分配太多的空间并不好，因为这样做无疑造成较大空间的浪费。因此建议做好空间估计，并合理利用空间，无论是对减少资源浪费还是提高系统性能都有好处。
3.解决空间不足的方法
扩充数据库存储空间常用的方法有如下三种：
（1）增加SYSTEM表空间中数据文件的大小
（2）创建新的表空间
（3）创建新的数据文件
（1）增加SYSTEM表空间中数据文件的大小
数据库中的数据其实都是存储在数据文件中的，SYSTEM表空间的数据文件是在创建数据库的时候给定的，并且给其大小给定了一个初值。那么System表空间不够时就可以Alter DataBase命令动态的增加SYSTEM表空间数据文件的大小。
查11g
Alter DATABASE orcl
Datafile 'C:\oracle\product\10.2.0\oradata\orcl\system01.dbf'
resize 750M;
(2)创建新的表空间
表空间其实是一个逻辑概念，它所有数据和结构信息都存储在一个或者多个数据文件当中，当需要扩充数据库存储空间时，可以创建新的表空间并指定它的数据文件，系统就会划出一块磁盘空间给这个表空间.
注意：创建数据库时最好能创建几个私用的表空间，因为SYSTEM表空间是系统表空间，其中存储数据字典和数据库结构等重要信息，他是数据库运行的基础，若是把所有信息都存放在这个表空间里，一方面会迅速占满它的空间，另一方面也加大了出错的可能性。
- Create tablespace 命令简介
Create tablespace 表空间名
Datafile{
文件名[autoextend {off | on next数值 maxsize数值}]，
}
Mininum extend 数值
Logging | nologging
Default storage{...}
Online | offline
Permanent | temporary;
2.用create tablespace创建表空间
create tablespace test
datafile 'C:\oracle\product\10.2.0\oradata\orcl\test01.dbf' size 2M
default storage(initial 2M
next 2M
minextents 2
maxextents 10
pctincrease 20
)
online;
（3）动态的增加表空间
1.Alter tablespace 命令简介
向表空间中增加数据文件使用的命令：
Alter tablespace 表空间名
Loggin | nologging
Add datafile{数据库文件名 [autoextend],...}
Rename datafile 原文件名 to 新文件名
Coalesce
Default storage
Mininum extend 数值
Online | offline [normal | temporary | immediate | for recover]
[begin | end] backup
Read only | write
Permanent | temporary
说明：
Add datafile：用于增加数据文件，可在联机或者是脱机下增加，但所增加的数据文件不能是其它表空间或者数据库已经使用的，它同样可带autoextend参数选项。
Coalesce：用于所有相连的空间范围合并到相邻较大的范围当中去，这一项不能被其它命令所指定。
[begin | end] backup:用于开始或者结束联机备份表空间的数据文件，在备份过程中用户可以继续访问该表空间，但备份过程中不能将表空间脱机，也不能关闭数据库。
Read only | write：其中Read only表示此表空间的内容是只读的，不能像其中写入任何数据，而read write则可以对此表空间的数据进行读写操作。
向表空间test中增加两个大小为10MB的数据文件
Alter tablespace test
Add datafile
'C:\oracle\product\10.2.0\oradata\orcl\test02.dbf size 10M' ;
oracle SQL学习总结
1.候选键、主键和外键约束：
create table Employee(
id int primary key,
name char(20),
birthday Date,
address varchar(30),
city varchar(10),
sex char(2),
salary numeric(10,2),
dno int,
pno int
unique(name,birthday)
);
create table Employee(
id int,
name char(20),
birthday Date,
address varchar(30),
city varchar(10),
sex char(2),
salary numeric(10,2),
dno int Reference Department(deNumber),//外键约束
pno int
unique(name,birthday)//候选键
primary key(id)//员工表的主键
);
外部键约束增加了关系数据库表的关联完整性，可以较好的保证数据库表之间的关联完整性。主键所在的表成为父表，为主控方，对应的外部键所在的表为子表，为被控方。
dno int Reference Department(deNumber)工作人员所属的部门号(dno)是相对于Department部门表的外部键。这样定义以后若想添加一个工作人员的记录，添加的记录中dno列的值必须在Department记录中存在与其相同的值，否则就无法添加记录。
也可以用另一种方式添加外键，若对应的主键是多个列的组合，那么外部键的定义只能放在所有列之后。只能是这种定义：
create table Employee(
id int,
name char(20),
birthday Date,
address varchar(30),
city varchar(10),
sex char(2),
salary numeric(10,2),
dno int，
pno int，
unique(name,birthday)//候选键
primary key(id)//员工表的主键
foreign key(dno) references Department(deNumber)
);
在对子表中的外部键或对父表的主键进行更新时（可能会产生的关联完整性的问题的四种情况）总结：
1.在子表中添加记录：
在子表Employee中添加一个记录，其dno列的值必须与Department表中dNumber列中的一个值相同。或者添加dno列值为NULL，否则将破坏数据库的完整性。但是向父表Department中添加新记录不会产生这个问题。
2.更新子表外部键的值：用update更新后的值必须与父表中的一个主键相匹配，或者更新为NULL，否则将产生错误。
3.删除父表中的记录：从父表中删除一个记录，子表中对应该值的外部键的记录不在与父表中的任何一个主键值相匹配。例如从父表Department表中删除dNumber列值为3的记录，那么子表Employee表中所有dno列值为3的记录（即员工所在部门为3号）的记录都将产生没有匹配主键值的问题。然而从子表中删除记录并不会产生这个问题。
4.更新父表中主键的值：父表Department中更新一个主键的值后，子表Employee中与原来值相对应的外部键的值也将不再与父表中任一主键的值相匹配。
下面是解决上面问题的方法：
1.使用Restrict关键字：父表中的主键值在子表中有许多具有该值的外部键的记录时，若在约束中指定Restrict关键字，则企图删除父表中的语句将被拒绝，企图更新父表中记录的主键值也将被拒绝。
2.CASCADE关键字：当父表中一个记录被删除时，cascade可以使其子表中所有与该记录的主键值对应的记录的（例如父表Department中的dNumber为3的值被删除，在子表中外键dno为3的记录都将被删除）都将从子表中删除。而更新父表中一个主键值时，CASCADE可以使其子表中对应外部键的值也自动被修改。
3.SET NULL：当父表中一个记录被删除时，SET NULL可以使其子表中所有与该记录的主键值对应的外部键的值将自动被赋值为NULL。更新同理。
4.SET DEFAULT： 当父表中一个记录被删除时，SET DEFAULT可以使其子表中的所有与该记录的主键值对应的外部键的值都将被赋值为缺省值。
- NULL 和NOT NULL约束：
NULL值并不表示0值或空值，而是表示一个丢失、不知道，不可用的值。
举例子说明该约束：例如，Employee表中，如果没有name就无法知道工作人员的名字，一般来说公司都要记录工作人员的名字，因此name的值可以规定为NOT NULL,而工作人员的生日，地址，等并不是一定要有的，也允许其丢失，这些列值允许为NULL。
作为区分每个表的主键的值，不可以为空值，而对于外部键来说，NULL值是允许存在的。
举例来说明：在Company数据库中，Employee表中pno列是相对于Project的外部键，并不是公司的一个员工都参与一个工程项目的工作，如总裁等。因此Employee表中的pno允许有NULL值存在，（员工表Employee的总裁的外部键pno可以为NULL）。
- 校验约束
举例来说明：在Employee增加校验约束条件
Salary NUMERIC（10,2）CHECK（Salary >1000.00 ）后Employee表中拒绝小于等于1000的所有记录。
用UPDATE Employee Set Salary=900.00 where id = 2002，该语句产生错误。
在员工表中sex列一般限制男和女两个值，可以增加一个检验约束来实现对sex列的限制。
create table Employee(
id int,
name char(20),
birthday Date,
address varchar(30),
city varchar(10),
sex char(2) check(sex in('男'，'女')),
salary numeric(10,2),
dno int Reference Department(deNumber),//外键约束
pno int
unique(name,birthday)//候选键
primary key(id)//员工表的主键
Constraint sex_check CHECK(sex in('男','女'))，
Constraint salary_check CHECK(salary > 1000.00)，
);
使用Constraint关键字进行检验约束的定义。这样我们可以在以后必要的时候对定义的约束进行激活和撤销。
5.默认值（default）
举例：salary numeric(10,2) default 3000.00，当对表添加记录时，若对设有默认值的列没有指定明确的值，系统自动会进行默认的添加。
Insert into Employee values ('peter','2012-02-20','','北京','男',1004,1,3);
6.Company数据库表的创建
1.Employee 表：
create table Employee(
id int primary key,
name char(15) NOT NULL, /员工名字/
birthday Date, /员工生日/
address varchar(30), /员工住址/
city char(10), /员工所在城市/
sex char(2) check(sex in('男'，'女')) default '男',
salary numeric(10,2) , /员工工资/
dno int Reference Department(dnumber), /外键约束 部门号/
pno int,
foreign key(dno) references Department(pnumber), /外键约束 项目号/
--unique(name,birthday)//候选键
--primary key(id)//员工表的主键
Constraint sex_check CHECK(sex in('男','女'))，
Constraint salary_check CHECK(salary > 1000.00),
);
2.Department部门表
Create table Department(
dnumber int primary key, /主键约束 部门号/
dname char(20) unique, /公司中每个部门的名称应该是不同的/
mgrid int, /部门负责人的id号/
mgrdate DATE /负责人上任日期/
);
3.Project项目表的
Create table Project(
pnumber int primary key, /项目号/
pname varchar(20) unique, /项目名称/
pmgrid int,
budget NUMRIC(10,2), /项目预算/
Gross NUMRIC(10,2), /预计总收入/
pstartime Date, /项目开始时间/
pendtime Date /项目结束时间/
);
4.Paccpeter项目接收方表：
说明：由于不同项目的接收方可以相同，因此以pnumber列作为表的主键。同时pnumber列是Paccpeter表相对于Project表的外部键。若父表Project不存在的项目号在子表Paccpeter也不能存在。
Create table Paccpeter(
pnumber int primary key, /项目号/
accepter char(20) NOT NULL, /项目接收方不允许为空/
city char(10),
acceptdate Date, /接收日期/
pnumber reference Project(pnumber) /外键pnumber/
);
5.Dep_Pro部门和项目连接表的创建：
说明：表中只包含两列：记录部门号的dnumber和记录项目号的pnumber，该表中以二者的组合作为主键。同时dnumber是相对于Department表的外部键，而pnumber列是相对于Project表的外部键。
CREATE TABLE Dep_Pro(
dnumber INT ,
pnumber INT ,
PRIMARY KEY(dnumber,dnumber),
dnumber REFERENCES Department(dnumber),
pnumber REFERENCES Department(pnumber),
);
7创建索引：
1.在单列上创建索引：
在员工表id列上创建索引：
CREATE INDEX id_index ON Employee(id);
Select name,sex,id,salary from Employee;
查询的结果会按照索引的顺序进行排列。
2.在多列上创建索引：
CREATE INDEX name_salary_index ON Employee(name,salary);
在多列上创建索引时将按照列出的顺序进行排列。先将按照name进行索引，然后在按照salary进行索引。
3.创建唯一索引：
唯一索引可以在单列也可以在多列上创建，唯一索引可以避免相同值的多个记录的出现，唯一索引是指两个记录中没有同一个索引值。
CREATE UNIQUE INDEX id_index ON Employee(id);
8.修改数据库：
1.向表中添加列：
ALTER TABLE Tablename
ADD columnname datatype
[DEFAULT expression]
[REFERENCES Tablename(columnname)]
[CHECK constraint]
例如：如果要在 Department 表中添加city 列,默认部门所在城市为北京。
Alter table Department add city char（10） default '北京';
也可以添加新列的约束：例如，公司新规定每个项目的预计总收入不得少于10000.00元，
则可以为Project表中添加一个约束：
ALTER TABLE Project CONSTRAINT gross_check (gross > 10000.00);
2.修改已有列
ALTER TABLE Tablename
MODIFY columnname [datatype]
[DEFAULT expression]
[REFERENCES Tablename(columnname)]
[CHECK constraint]
例如，将Employee表中的name列的长度变成30个字符长：
ALTER TABLE Employee MODIFY NAME CHAR(30) ;
不能修改数据类型，即不能从int改到 char，可以减少字符串的长度，然而，
不能长度少于已有数据中长度最大的值。
也可以修改列所具有的约束：
例如，将 Employee表中sex列的默认值从“男”修改成“女”：
ALTER TABLE Employee MODIFY sex DEFAULT '女';
在SQL 中，也可以使用 ALTER TABLE 语句删除表中已有的列。
例如从Employee表中删除birthday：
ALTER TABLE Employee DROP birthday;
9.删除索引：
Drop index indexname;
使用索引的原因总结：
使用索引是为了提高检索数据的速度，创建索引后，我们就可以通过索引来查找数据，而不必扫描每个记录。这好比书的目录一样，使我们很快就会定位到我们要找的那一页。但是我们不能对每一列进行索引。因为，创建和维护索引需要占用数据库的时间和空间的。
1.适合使用索引的情况：
（1）大量值：表中的记录在该列上含有大量不同的值时，为表建立索引可以起到很好的作用。
（2）经常在查询中使用：某列在查询中使用的越多，在该列上创建的索引所能起到的加快速度的作用越明显。
（3）查询返回记录相对较少时：当返回的记录大大少于表中的记录总量的时候，索引能很好的加快查询速度。若总是返回大量的记录，由于索引本身的开销，索引并不能很好的提高检索的速度。
（4）用于两个表连接的操作：由于索引使每个表中的记录按顺序排列，所以使用被索引的列进行连接操作，可以提高连接速度。
2.不适和建立索引的情况：
(1)小型表：对于小型表，由于表中的记录不多，而索引自身也会增加开销，通过使用索引并不能提高性能，所以小型表不使用索引。
(2)只有很少值的列不适合建立索引。
(3)用户查询方式经常变化的表不适合建立索引。
(4) 在具有较多的NULL值的列不适合创建索引。
(5) 定期更新或者修改的列不断的更新或者修改导致索引页需要进行不断的更新，额外的消耗过多。
10.多表连接查询：
1.什么是连接？
定义：连接是将多个表的数据结合到一起的查询，即连接操作可以在一个select语句中完成从多个表中查找和处理数据。使用连接时可以使用相同名字的列，例如可以使用pnumber连接Project表与Paccpter表；也可以使用不同名字的列进行连接，例如使用Employee表中的id列与Department的mgrid列进行连接。进行连接时用于连接的必须是可连接的列，即要求它们具有相同的数据类型。
一般来说，连接的语法可以分为二种：
（1） 传统的连接语法：select from/where包含多个表的连接查询
传统的FROM/WHERE 连接语法基本格式如下：
SELECT select_list
FROM Table1name, [ Table2name, ...]
WHERE [Table1name.]column operator [Table2name.]column
用上面的表：
例如：查询部门负责人的负责的部门号、负责人名字、id 号、性别及工资。
分析：需要连接连个表：Employee表和Department表
原因：在Employee表中并没有存储谁是什么部门的负责人，负责人的id 号存储在Department 表中。（参照上面的表）
Select dnumber,name,id,sex,salary from Employee, Department where id=mgrid;
（2） SQL连接语法：join关键字来实现连接的操作JOIN CROSS JOIN及NATURAL
SELECT select_list
FROM Table1name [CROSS | NATURAL] JOIN Table2name
[ON [Table1name.]column operator [Table2name.]column]
[WHERE conditions]
● 第一行中的select_list仍然是选择的列的名称，但是需要注意的是，如果使用两
个表中有相同名称的列，则必须限定是哪个表中的列；
● 第二行中关键字CROSS与NATURAL 是可选项，可以使用也可以不使用。如果
选择了CROSS或NATURAL 关键字，那么就不能使用ON关键字；
● 单独使用JOIN 关键字时，需要使用ON关键字来设定连接的条件；
● 使用CROSS JOIN 时，不能使用关键字ON，因此必须使用WHERE子句设定连
接的条件。因为如果不设定连接条件，将返回表之间的每一种可能组合(即笛卡
尔积)，连接结果将非常庞大；
● NATURAL JOIN 只 有在两个表有相同名称的列时才能使用，将在同名列上进行连
接。因此，不必使用关键字 ON 或 WHERE 子句设定连接条件。当然，使用 WHERE
子句限制查找的行仍然是可以的。
例如查询：部门负责人的负责的部门号、负责人名字、id 号、性别及工资。
用join连接：
Select dnumber,name,sex,id,salary from Employee join Department on mgrid = id;
Results
dnumber name sex id salary
———— ————— ——— —— ————
6 魏成 男 6001 5000.00
1 林志千 男 1001 6000.00
2 陈广海 男 2001 5000.00
3 张宇 男 3001 5000.00
4 张峰 男 4001 7000.00
5 李志深 男 5001 5000.00
用cross join连接：
SELECT dnumber, name, sex, id, salary FROM Employee CROSS JOIN Department
WHERE id = mgrid
结果是与上面相同的。
但是这里不能用NATURAL JOIN连接。虽然Employee的id列和Department表mgrid列的数据类型和存储的内容相同，但是名称不相同，所以不能用自然连接。
Project的表和Paccpter表中有两个相同的列pnumber，则可以对这两个表进行NATURAL JOIN连接。
Select pnumber,accepter,budget from Project NATURAL JOIN Paccpter;
Results
pnumber accepter budget
———— ———————— —————
101 蓝科通讯公司 110000.00
102 华夏技术有限公司 140000.00
103 汉升咨询公司 150000.00
104 神州发展有限公司 120000.00
105 四海贸易有限公司 100000.00
201 化天通讯公司 200000.00
202 日胜公司 220000.00
203 华田汽修公司 250000.00
204 东胜有限公司 240000.00
301 科华贸易公司 300000.00
302 新安有限公司 320000.00
303 华田汽修公司 360000.00
也可以用传统的from/where循环代替：
Select pnumber,accepter,budget from Project，Paccpter where Project.pnumber = Paccpter.pnumber;
用join on代替：
Select pnumber,accepter,budget from Project join Paccpter on Project.pnumber = Paccpter.pnumber;
2处理连接：
两个表有意义的连接到一起，必须有公共数据。
Project表：
SELECT pnumber, budget FROM Project;
Results
pnumber budget
———— —————
101 110000.00
102 140000.00
103 150000.00
104 120000.00
105 100000.00
201 200000.00
202 220000.00
203 250000.00
204 240000.00
301 300000.00
302 320000.00
303 360000.00
Paccepter 表:
SELECT pnumber, accepter FROM Paccepter;
Results
pnumber accepter
———— ———————
101 蓝科 通讯公司
102 华夏 技术有限公司
103 汉升 咨询公司
104 神州 发展有限公司
105 四海 贸易有限公司
201 化天通 讯公司
202 日胜公司
203 华田汽 修公司
204 东胜有 限公司
301 科华贸 易公司
302 新安有 限公司
303 华田汽 修公司
在理想的情况下，对数据库中的表进行连接时使用表的主键与相应外部键。因为，设计数据库时，主键与外部键在逻辑上就是互相联系的，并且主键与外部键之间具有一致性，会保持主键列与外部键列在值上的一致。主键与外部键可以很好的保持数据库完整性。
较好的连接应该符合以下要求：
● FROM 子句中必须包含用于连接的所有表，不能仅包含一部分用于连接的表；
● 进行连接的列名称可以不同，除非要使用NATURAL JOIN，如果需要使用相同
名称的列必须为其加上表名作为限制，不然会产生混淆；
● 用于连接的列必须有相同的数据类型，或可以自动进行类型转换。不同类型的数
据是不能进行连接的。例如，不能使用Employee表中的birthday 列与Department
表中的dname 列进行连接；
● 用于连接的列必须具有相同的意义，这是最重要的一点，没有相同意义的列的连
接是毫无用处的。例如，虽然Employee表中的name列与Department 表中的dname
列数据类型相同，但是这两个列毫不相干，连接是没有意义的。
查询的时候必须指定查询条件：
例如：查找正工作在与项目接收方所在城市相同的工作人员。
方法一：Select id,name,sex,city from Employee,Paccepter where Employee.city = Paccepter.city;
方法二： select id,name,sex,city from Employee join Paccepter where Employee.city = Paccepter.city;
如果没有连接条件，查询的结果将是两个表的笛卡尔积。结果行数将大的惊人。
3．笛卡尔积
表一：
Results
A B C
—— —— ——
a b c
d e f
b c d
表二：
Results
D E
—— ——
d e
e f
则表一和表二的笛卡尔积为：
Results
A B C D E
—— —— —— —— ——
a b c d e
a b c e f
d e f d e
d e f e f
b c d d e
b c d e f
最后可以有：3*2=6条记录。
用于指定连接的表越多，其笛卡尔积的结果就越惊人，越需要很好的指定连接条件。
由于连接过程中使用了笛卡尔积，因此连接操作会导致性能的下降。
使用连接时应该遵循一定的原则：
● 用于连接的列已经创建了索引。因为索引会单独保存在磁盘上，且将数据按照一
定顺序进行了排列，索引的使用可以加快访问的速度；
● 用于连接的列具有相同的数据类型，包括是否允许空值。如果需要系统自动进行
类型转换是需要花费较多时间的，特别是在表中记录很多时，类型转换所花费的
时间将会很多.
4.连接中运算符号的使用：
例如：项目Project 表中的项目完成时间penddate 列的值大于Paccepter 表中的项目
预计接收时间acceptdate列的值。
Select pnumber, accepter, penddate, acceptdate from Project,Paccepter where penddate > acceptdate AND Project.pnumber = Paccepter.pnumber;
Results:
pnumber accepter penddate accedate
———— —————— —————— ——————
201 化天通讯公司 2001-12-20 2001-12-15
303 华田汽修公司 2002-04-25 2002-04-15
[2rows]
例如：Project 表中的项目完成时间penddate 列的值不等于Paccepter 表中的项目预
计接收时间accedate 列的值。
Select pnumber, accepter, penddate, acceptdate from Project,Paccepter where penddate <> acceptdate AND Project.pnumber = Paccepter.pnumber;
Results
pnumber accepter penddate accedate
———— —————— —————— ——————
201 化天通讯公司 2001-12-20 2001-12-15
204 东胜有限公司 2002-08-01 2002-08-06
303 华田汽修公司 2002-04-25 2002-04-15
[3rows]
在进行连接的时候还可以使用where子句限制查找到的行。
例如，只需要查看项目号小于201 的所有项目的项目编号、接收方及项目成本。
方法一:
Select pnumber, accepter, budget from Project, Paccpeter where Project.pnumber = Paccpeter.pnumber AND Project.pnumber < 201;
方法二：
Select pnumber, accepter, budget from Project join Paccpeter on Project.pnumber = Paccpeter.pnumber where Project.pnumber < 201;
Results
pnumber accepter budget
———— —————— —————
101 科华贸易公司 110000.00
102 华夏技术有限公司 140000.00
103 科华贸易公司 150000.00
104 神州发展有限公司 120000.00
105 四海贸易有限公司 100000.00
[5rows]
5．多表的连接：
需要知道工作人员本身以及其所属的部门和所参与的项目的详细信息，
Employee表、Department 表、Project 表这三个表以得到所需的信息。
但是多个表连接或者多个表连接时引起的性能下降更加严重。
例如：有三个表，每个表都包含100行记录，则三个表连接将产生100100100行的组合表。处理这样的组合表要花费的时间将会长的多。
例如：将返回工作人员本身以及其所属的部门和所参与的项目的详细信息：
分析：需要连接三个表：Employee表，Department表，Project表。
Select id,name,dnumber,dname,mgrid,pnumber,pname,gross from Employee, Department, Project where dno = dnumber AND pno = pnumber;
数据库设计时介绍了三种关系和数据库连接的关系：
（1）一对一的关系：
例如：项目Project表和项目接收Paccepter表时一对一的关系：
可以通过项目号pnumber进行连接：
方法一：Select pnumber,accepter,budget from Project join Paccepter on Project.pnumber = Paccepter.pnumber where Project.pnumber = 102;
方法二：Select pnumber,accepter,budget from Project,Paccepter where Project.pnumber = Paccepter.pnumber AND Project.pnumber = 102;
Result：
pnumber accepter budget
———— —————— ————
102 华夏技术有限公司 140000.
[1row]
（2）一对多的关系：
一个表的一条记录可以对应另一个表的若干条记录：
查询的时也可以对两个表进行连接：例如：部门和工作人员之间就是一对多的关系。
希望由工作人员记录得出其所属的部门的信息就需要进行连接：
Select id，name，dnumber, mgrid from Employee,Department where dno = dnumber AND name = ‘lipeng’;
Select id,name,dnumber,mgrid from Employee join Department on dno = dnumber where name = ‘lipeng’;
Results
name id dnumber mgrid
————— —— ———— ———
李明 1002 1 1001
[1row]
(3) 多对多的关系：
无法对多对多的两个表进行连接，则必须使用创建数据库时专门为多对多的关系创建的连接表来实现。因此需要包含三个表的组合来连接数据。
例如：一个部门可能同时进行一个或者多个项目，一个项目可能由一个或者多个部门来配合才能很好的完成。
部门与项目间存在着多对多的关系。可以通过连接表来实现部门与项目的连接。
SELECT dnumber, mgrid, pnumber FROM Department JOIN Dep_pro
ON Department.dnumber = Dep_pro.dnumber Project JOIN Dep_pro ON Project.pnumber = Dep_pro.pnumber WHERE Project.pnumber = 301
SELECT dnumber, mgrid, pnumber FROM Department,Project,Dep_pro
WHERE Department.dnumber = Dep_pro.dnumber AND
Project.pnumber = Dep_pro.pnumber AND
Project.pnumber = 301
oracle导入导出，如何创建一个账户，并在这个账户中导入导出表
oracle导入导出，如何创建一个账户，并在这个账户中导入导出表
1.如何在数据库中创建一个账户：
第一,启动sql*puls
第二，以system/manager登陆
第三，create user 用户名 IDENTIFIED BY 密码 （例如：bione1/bione1）
第四，GRANT CREATE USER,DROP USER,ALTER USER ,CREATE ANY VIEW ,
DROP ANY VIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE,
DBA,CONNECT,RESOURCE,CREATE SESSION TO 用户名字
2.导入，导出
导入导出命令：
Oracle数据导入导出imp/exp就相当于oracle数据还原与备份。exp命令可以把数据从远程数据库服务器导出到本地的dmp文件， imp命令可以把dmp文件从本地导入到远处的数据库服务器中。 利用这个功能可以构建两个相同的数据库，一个用来测试，一个用来正式使用。
执行环境：可以在SQLPLUS.EXE或者DOS（命令行）中执行，
DOS中可以执行时由于 在oracle 10i 中 安装目录ora101BIN被设置为全局路径，
该目录下有EXP.EXE与IMP.EXE文件被用来执行导入导出。
oracle用java编写，SQLPLUS.EXE、EXP.EXE、IMP.EXE这两个文件有可能是被包
将D:\bione.dmp表中的数据导入到orcl数据库中。
（1）当创建完用户后，找到安装oracle的bin目录，例如C:\oracle\product\10.2.0\db_1\bin。
（2）打开cmd窗口，输入cd C:\oracle\product\10.2.0\db_1\bin，然后再输入：imp bione1/bione1@orcl file= D:\bione.dmp ignore=y;
（3）导入成功。
导出：：
（1） 将数据库TEST完全导出,用户名system 密码manager 导出到D:daochu.dmp中
exp system/manager@TEST file=d:daochu.dmp full=y
（2） 将数据库中system用户与sys用户的表导出
exp system/manager@TEST file=d:daochu.dmp owner=(system,sys)
（3） 将数据库中的表inner_notify、notify_staff_relat导出
exp aichannel/aichannel@TESTDB2 file= d:datanewsmgnt.dmp tables=(inner_notify,notify_staff_relat)
（4 )将数据库中的表table1中的字段filed1以"00"打头的数据导出
exp system/manager@TEST file=d:daochu.dmp tables=(table1) query=" where filed1 like '00%'"
上面是常用的导出，对于压缩，既用winzip把dmp文件可以很好的压缩。
也可以在上面命令后面 加上 compress=y 来实现。
Oracle sqlplus命令：
1.ed a
@a
通过记事本的方式编译：
Set linesize 300;
Set pagesize 30;
@a
可以通过@找到磁盘上的文件，现在d盘上有一个demo.txt文件，文件中也是查询指令
(select * from emp)。执行的时候要指定路径。“@d;\demo.txt”，执行这条命令后则找到文件，执行文件里面的语句。默认的后缀名为“*.sql”
3.在sqlplus中也可以使用其他用户连接，例如之前了解过的sys和system用户；
使用如下命令：conn 用户名/密码[AS SYSDBA/SYSOPER]
如果是超级管理员的话（sys），则在连接的最后必须写上AS SYSDBA。以系统管理员的身份进行登录。
例如：conn system/manager; 连接system用户
Conn sys/change_on_install as sysdba; 连接sys用户
4.如果在不同用户（scott用户/sys用户）下面想访问emp表的话，在必须加上用户名。即：表的完整名称：“scott.emp”
5.如果想知道当前连接的是哪个用户怎么办呢？
可以通过 show user ；显示当前正在连接的用户是哪一个。
使用select * from tab；查找数据库中有多少个表
用desc+表名的形式查看表的结构。
6.你可以输入一个“/”,表示重复执行上一条语句的操作。
7.上面的是在scott/tiger模式下面的操作，如果想进入hr模式，因为默认的hr模式已经被锁定，就需要对该用户模式解锁。
对用户模式解锁的具体步骤如下：
（1）以system的身份连接到数据库
Connect system/password;
(2)解锁用户账号，并修改其登陆密码。
Alter user hr account unlock;
Alter user hr identified by hr;
(3)连接到HR模式并查看该模式下面所包含的表
Connect hr/hr;
（4）查看表
Select * from tab;
（5）以DBA的身份进入。
/ as sysdba;
ORACLE 创建表空间2011-11-29 22:27 by shungdawei, 6531
复制代码
复制代码
1 --创建临时表空间
2 create temporary tablespace data_temp
3 tempfile 'D:\oracle\oraudata\data_temp.dbf'
4 size 2000m
5 autoextend on
6 next 500m
7 extent management local;
8
9 --创建数据包空间
10 create tablespace data_data
11 logging
12 datafile 'D:\oracle\oraudata\data_data.dbf'
13 size 2000m
14 autoextend on
15 next 500m
16 extent management local;
17
18
19 --创建用户并指定表空间
20
21 create user dbadmin identified by dbpass
22 default tablespace data_data
23 temporary tablespace data_temp;
24
25 --删除用户命令
26 --drop user dbadmin cascade
27
28 --给用户授予权限
29 grant connect,resource to dbadmin
30
31 --撤权
32 --revoke connect,resource from dbadmin
Oracle查看所有表空间使用情况2011-11-29 22:41 by shungdawei, 40 阅读, 0 评论, 收藏, 编辑
复制代码
复制代码
1 select
2 b.file_id FILEID,
3 b.tablespace_name TBSNAME,
4 b.bytes/1024/1024||'M' SIZES,
5 (b.bytes-sum(nvl(a.bytes,0)))/1024/1024||'M' USED,
6 sum(nvl(a.bytes,0))/1024/1024||'M' FREE,
7 100 - sum(nvl(a.bytes,0))/(b.bytes)*100||'%' PERCENTS
8 from dba_free_space a,
9 dba_data_files b
10 where a.file_id = b.file_id
11 group by b.tablespace_name, b.file_id, b.bytes
12 order by b.file_id;
复制代码
SELECT a.tablespace_name,
ROUND (a.total_size, 1) "total(M)",
ROUND (a.total_size) - ROUND (NVL (b.free_size, 0), 1) "used(M)",
ROUND (NVL (b.free_size, 0), 1) "free(M)",
ROUND (NVL (b.free_size, 0) / total_size * 100, 1) "free rate(%)"
FROM ( SELECT tablespace_name, SUM (bytes) / 1024 / 1024 total_size
FROM dba_data_files
GROUP BY tablespace_name) a,
( SELECT tablespace_name, SUM (bytes) / 1024 / 1024 free_size
FROM dba_free_space
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+)
ORDER BY "free rate(%)";