Oracle大杂烩

//第1步：创建临时表空间
create temporary tablespace user_temp
tempfile '/home/oracle/app/oracle/oradata/helowin/user_temp.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;

//第2步：创建数据表空间
create tablespace user_data
logging
datafile 'D:\oracle\oradata\Oracle9i\user_data.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;

//第3步：创建用户并指定表空间
create user username identified by password
default tablespace user_data
temporary tablespace user_temp;

//第4步：给用户授予权限
grant connect,resource to username;

撤权： revoke 权限... from 用户名;
删除用户命令：drop user user_name cascade;
建立表空间：CREATE TABLESPACE data01 DATAFILE '/oracle/oradata/db/DATA01.dbf' SIZE 500M UNIFORM SIZE 128k; #指定区尺寸为128k,如不指定，区尺寸默认为64k
删除表空间：DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES;
建立UNDO表空间：CREATE UNDO TABLESPACE UNDOTBS02 DATAFILE '/oracle/oradata/db/UNDOTBS02.dbf' SIZE 50M #注意：在OPEN状态下某些时刻只能用一个UNDO表空间，如果要用新建的表空间，必须切换到该表空间:ALTER SYSTEM SET undo_tablespace=UNDOTBS02;
建立临时表空间：CREATE TEMPORARY TABLESPACE temp_data TEMPFILE '/oracle/oradata/db/TEMP_DATA.dbf' SIZE 50M
改变表空间状态
ALTER TABLESPACE game OFFLINE;
ALTER TABLESPACE game OFFLINE FOR RECOVER;
ALTER TABLESPACE game ONLINE;
ALTER DATABASE DATAFILE 3 OFFLINE;
ALTER DATABASE DATAFILE 3 ONLINE;
ALTER TABLESPACE game READ ONLY;
ALTER TABLESPACE game READ WRITE;
DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES;
扩展表空间：select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name;

增加数据文件：ALTER TABLESPACE game ADD DATAFILE '/oracle/oradata/db/GAME02.dbf' SIZE 1000M;
手动增加数据文件尺寸：ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf' RESIZE 4000M;
设定数据文件自动扩展：ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf AUTOEXTEND ON NEXT 100M MAXSIZE 10000M;
设定后查看表空间信息：SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,
(B.BYTES100)/A.BYTES "% USED",(C.BYTES100)/A.BYTES "% FREE"
FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE

重命名：rename dept to dt;
重命名列：alter table dept rename column loc to location
新增列: alter table employee_info add hiredate date default sysdate not null;
删除列： alter table employee_info drop column hiredate;
修改列：alter table dept modify loc varchar2(50);
增加约束： alter table dept add constraint constraint_name [primary key,foreign key,unique, check,not null] [references xx(ID)]
约束的禁用/开启： alter table employee_info [disable,enable] constraint uq_emp_info;
删除约束：alter table employee_info drop constraint fk_emp_info;

查看约束: select constraint_name,constraint_type,status,deferrable, deferred from user_constraints where table_name='EMPLOYEE_INFO';
查看约束详细：select owner,constraint_name,table_name,column_name from user_cons_columns where table_name='EMPLOYEE_INFO';
查看约束详细2：select ucc.column_name,ucc.constraint_name,uc.constraint_type,uc.status from user_constraints uc,user_cons_columns ucc where uc.table_name=ucc.table_name and uc.constraint_name=ucc.constraint_name and ucc.table_name='EMPLOYEE_INFO';
清除表中数据： truncate table employee_info(清除重建); (DELETE FROM table_name或DELETE * FROM table_name)
删除表：drop table employee_info;

向表中添加注释:comment on table employee_info is 'information of employees';
向列添加注释: comment on column employee_info.ename is 'the name of employees';
获取表的注释: select * from user_tab_comments where table_name='EMPLOYEE_INFO';
获取列的注释：select * from user_col_comments where table_name='EMPLOYEE_INFO';

查询oracle服务器端字符的名称：select userenv('language') from dual;

导出全部用户：exp hlsoa/hlsoa@orcl file=E:\test\file log=E:\test\log full=y 这是导出本地数据库/exp hlsoa/hlsoa@192.168.1.227/orcl file=E:\test\file log=E:\test\log full=y
导出数据库结构而不导出数据：exp hlsoa/hlsoa@orcl file=E:\test\file log=E:\test\log full=y rows=n
导出一个或者多个指定表：
exp 登录名称/用户密码@服务命名 file=文件存储的路径以及名称 log=日志存储的路径以及名称 tables=表名字/
exp 登录名称/用户密码@服务命名 file=文件存储的路径以及名称 log=日志存储的路径以及名称 tables=(表1,表2,表3,表N)
导出某个用户所拥有的数据库表：exp hlsoa/hlsoa@orcl file=E:\test\file log=E:\test\log owner=(hlsoa)
用多个文件分割一个导出文件:exp system/manager file=(paycheck_1,paycheck_2,paycheck_3,paycheck_4) log=paycheck, filesize=1G tables=hr.paycheck

导入：imp system/manager file=bible_db log=dible_db full=y ignore=y
导入一个或一组指定用户所属的全部表、索引和其他对象：imp system/manager file=seapark log=seapark fromuser=seapark/imp system/manager file=seapark log=seapark fromuser=(seapark,amy,amyc,harold)
将一个用户所属的数据导入另一个用户：mp system/manager file=tank log=tank fromuser=seapark touser=seapark_copy
imp system/manager file=tank log=tank fromuser=(seapark,amy) touser=(seapark1, amy1)
导入一个或者多个表:imp system/manager file=tank log=tank fromuser=seapark TABLES=(a,b)
从多个文件导入:imp system/manager file=(paycheck_1,paycheck_2,paycheck_3,paycheck_4) log=paycheck, filesize=1G full=y

增量导出数据:
--“完全”增量导出(complete)，即备份整个数据库
exp system/manager@服务命名 inctype=complete file=990702.dmp
--“增量型”增量导出(incremental)，即备份上一次备份后改变的数据
exp system/manager@服务命名 inctype=incremental file=990702.dmp
--“累计型”增量导出(cumulative)，即备份上一次“完全”导出之后改变的数据
exp system/manager@服务命名 inctype=cumulative file=990702.dmp
导出某个用户所拥有的数据库表：
exp 用户名/密码@服务命名 file=存放位置\存放文件名.dmp log=存放位置\存放文件名.log owner=拥有者用户名

估计导出文件的大小
--整个数据库全部表总字节数：
SELECT sum(bytes)/1024/1024/1024 "占用空间:单位GB"
FROM dba_segments
WHERE segment_type = 'TABLE';

--指定用户所属表的总字节数：
SELECT sum(bytes)
FROM dba_segments
WHERE owner = 'SEAPARK'
AND segment_type = 'TABLE';

seapark用户下的aquatic_animal表的字节数：
SELECT sum(bytes)
FROM dba_segments
WHERE owner = 'SEAPARK'
AND segment_type = 'TABLE'
AND segment_name = 'AQUATIC_ANIMAL'

创建序列：
select sequence tse increment by 1 startwith 1 maxvalue 1 nocycle nocache;
查看序列：
select * from user_sequences;
修改序列：
alter sequence tse maxvalue 20;

查看索引：
user_indexes 系统视图存放是索引的名称以及该索引是否是唯一索引等信息，
user_ind_columns 统视图存放的是索引名称，对应的表和列等

select* from all_indexes where table_name='ACM_NETWORK_OPERATION';
select * from user_ind_columns where table_name='ACM_NETWORK_OPERATION';
创建索引：
create [unique] index 索引名 on 表名(列1，列2...)
建1个列上称为单列索引 否则称复合索引
删除索引:
Drop index 索引名

创建函数：
create function qiuhe(n number) return number
is
begin
if n>1 then
return n + qiuhe(n-1);
else
return 1;
end if;
end;
删除函数：
drop function get_empname;

创建存储过程/函数：
CREATE PROCEDURE proc[(name [IN|OUT|INOUT] type)]
AS|IS
declare statement;
BEGIN
statement;
EXCEPTION
exception process;
END
在存储过程(PROCEDURE)和函数(FUNCTION)中没有区别；
在视图(VIEW)中只能用AS不能用IS；
在游标(CURSOR)中只能用IS不能用AS。
函数必须通过ruturn 关键字返回一个值
存储过程不需要return 返回一个值

create or replace procedure 存储过程名 as/is
create or replace function 函数名 return 返回类型 as/is
什么时候用存储过程，什么时候用函数：
一般来说，当只有一个返回值的时候用函数，
当没有返回值或需要多个返回值的时候用存储函数

1.嵌套循环连接(Nested Loops)适用范围
两个表, 一个叫外部表, 一个叫内部表.
如果外部输入非常小，而内部输入非常大并且已预先建立索引，那么嵌套循环联接将特别有效率。
关于连接时哪个表为outer表，哪个为inner表，我发现sql server会自动给你安排，和你写的位置无关，它自动选择数据量小的表为outer表， 数据量大的表为inner表
2.合并联接(Merge)
指两个表在on的过滤条件上都有索引, 都是有序的, 这样, join时, sql server就会使用Merge join, 这样性能更好.
如果一个有索引,一个没索引,则会选择Nested Loops join.
3.哈希联接(Hash)
如果两个表在on的过滤条件上都没有索引, 则就会使用Hash join.
也就是说, 使用Hash join算法是由于缺少现成的索引.