oracle学习.txt

关于oracle的详细资料!! 一定对你学习oracle有用的

	using '主机字符串';

[*]数据库链路名必须与远程数据库的全局数据库名(数据库名.域名，若没有数据库名，就是数据库名)相同
[*]用户名及口令为远程数据库的用户名及口令
[*]主机字符串为本机tnsnames.ora中网络连接串。

SQL> Create database link ora31 connect to user30 identified by user30 using 'ora31';

2. 使用数据库链路：
SQL> select * from product@ora31;
SQL> insert into product@ora31 values(...);
SQL> Create table product as select * from product@ora31;

3. 删除数据库链路：
SQL> Drop database link ora31;

(一） 创建数据库触发器实现两个数据库之间实时数据传输。
DB1(UNIX) <------- DB2(NT)
若要将DB2中的数据复制到DB1，则需要在DB1中创建数据库链路指向DB2数据库。

[*]在对方的数据库(DB2)上建立触发器，即数据发送方的数据库上建立触发器。
[*]在数据发送方建立指向DB1的数据库链路。

[操作步骤（假设db1为ora31, db2为ora8i)：]
(1) 在DB1上建立用来复制远程数据的表：
SQL> Create table product 
	as select * from product@ora31;

(2) 在DB2上建立到DB1的数据库链路：
SQL> Create database link ora31 connect to user30 identified by user30 using 'ora31';

(3) 在DB2上建立触发器：
SQL> Create or Replace trigger insert_product before insert on product 
	for each row
	Begin
		Insert into product@ora8i
		values(:new.p_id, :new.p_name);
	End insert_product;

// 上述创建数据库触发器的语句，请以.号结束，然后以/执行。

/* 查看SQL语句执行的错误信息：
SQL> show errors
*/

(4) 在DB2上测试数据的自动复制是否成功：
SQL> Insert into product values(1005, '测试商品');		// 在db2的表中插入一条数据
SQL> select * from product;							// 检查数据是否正确插入本地表
SQL> select * from product@ora31;						// 检查数据是否复制到了db1的表中

查询数据库链路信息：
SQL> select username, password from user_db_links;


数据更新：
SQL> Create or Replace trigger update_product
	before update on product
	for each row
	Begin
		update product@ora8i
		set p_id = :new.p_id, p_name = new.p_name where p_id = :old.p_id
	End update_product;

数据删除：
SQL> Create or Replace trigger delete_product
	before delete on product
	for each row
	Begin
		delete from product@ora8i where p_id = :old.pid
	End delete_product


（二）. 创建快照（实体化视图）实现两个数据库之间定时数据库传输：
快照：要求主副站点数据库的用户名相同

(1) 在主节点创建快照日志
语法：SQL> Create snapshot log on 主节点表名; 		//主节点基表必须含有主键

(2) 在副节点创建快照
语法：
SQL> Create snapshot 快照名
	refresh 刷新方式
	next 时间间隔
	with primary key
	for update
	as select * from 主节点表名@数据库链路名;
	
[*]刷新方式：
Compelete:完全刷新
Force:强制刷新(建议使用，强制刷新自动先fast刷新，然后再force刷新)
Fast:快速刷新
[*] 时间间隔：以天为单位。
sysdate + 1/4		// 六个小时刷新一次
sysdate + 1/1440	// 一分钟刷新一次

/******************************* 教师的部分操作语句 *****************************************************

以下SQL语句为在副节点上的操作语句，主节点对应的用户为user30，例中的photo为要创建快照的表名
create user user30 identified by user30;

grant connect, resource to user30;

grant create snapshot to user30;

connect user30/user30

create database link ora31 connect to user30 identified user30 using 'ora31';

select * from photo@ora31;

create snapshot photo
	refresh force
	next sysdate + 1/1440
	with primary key
	for update 
	as select * from photo@ora31; 

********************************************************************************************************/
	
/**************************　我的操作步骤 ******************************
[1] 主节点：ora8i
SQL> show user
USER 为"STUD29"
SQL> create snapshot log on dept;

实体化视图日志已创建。

SQL> show user
USER 为"STUD29"
SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 COMPUTER       BEIJING

SQL> insert into dept values(60, 'test snap', 'snapshot');

已创建 1 行。

提交完成。

[2]副节点

SQL> connect system/ab
已连接。
SQL> create user stud29 identified by stud29;

用户已创建

SQL> grant connect, resource to stud29;

授权成功。

SQL> grant create snapshot to stud29;

授权成功。

SQL> connect stud29/stud29;
已连接。
SQL> connect system/ab
已连接。
SQL> grant create database link to stud29;

授权成功。

SQL> connect stud29/stud29
已连接。
SQL> create database link ora8i connect to stud29 identified by stud29 using 'tea';

数据库链接已创建。

SQL> create snapshot dept
  2  refresh force
  3  next sysdate+1/2880
  4  with primary key
  5  for update
  6  as select * from dept@ora8i;

实体化视图已创建。
*******************************************************************************************/


[Oracle8i 图形界面管理工具]

Oracle 程序组->Enterprise Manager-> 

(1)先运行该组中的Configuration Assistant，创建一个新的档案资料库（其作用请查询相应界面上的帮助信息）。

(2) 然后运行该组中的Console程序：
登录用户：sysman	密码：oem_temp

然后搜索要管理的节点（使用主机名或者IP地址都可以），节点要想被搜索到，需要先在节点上启动OracleOraHome81Agent服务，要想在节点上使用图形管理工具，要求先启动OracleOraHome81ManagementServer服务。


四、创建索引(indexes):

语法：SQL> create index 索引名 on 表名(列名);

例：SQL> create index index_dept_dname on dept(dname);

索引数据字典：
SQL>select index_name, table_owner, table_name, from user_indexes;

五、创建序列(Sequences):

语法：SQL> Create sequence 序列名
		start with 起始编码
		increment by 步长
		maxvalue 终止编码;

SQL> create sequence id_code
	start with 2
	increment by 2
	maxvalue 999;
	
序列使用方法：
id_code.nextval		// 下一个值
id_code.currval		// 当前值

第一次要使用nextval，然后以后每次使用currval。

insert into student values(id_code.nextval, '姓名');


[*] ||(双竖线)在oracle中是连接符号，将两个字符串连成一个，如：'A' || 'B' = 'AB'


[第六章　数据库分区技术]

一、什么是数据分区？
　　数据分区是指把一个表划分成若干小块。在创建表的结构时应考虑好分区方案，选择表中某一列或多列数据作为分区关键字，该关键字决定哪些数据分到哪些区。Oracle对分区进行管理，新插入数据自动存储到相应的分区。

二、创建分区表：

SQL> Create table employee (
	id number(7),
	name varchar2(20),
	sal number(7,2))
	Partition by range(sal)
	(Partition p1 values less than(500) tablespace users,
	Partition p2 values less than(800) tablespace tools,
	partition p3 values less than(1000) tablespace system);

// p1, p2, p3是三个分区的名字，users, tools, system是三个表空间的名字。less than是小于（不包含）。
几点说明：
[.] 所插数据不得大于LESS THAN中的最大值
[.] 可以使用MAXVALUE（如上面的语句中，要求工资不能大于1000，如果出现这种情况，则应该改成下面的语句：
SQL> Create table employee (
	id number(7),
	name varchar2(20),
	sal number(7,2))
	Partition by range(sal)
	(Partition p1 values less than(500) tablespace users,
	Partition p2 values less than(800) tablespace tools,
	partition p3 values less than(1000) tablespace system,
	partition p4 values less then(maxvale) tablespace users);
 
[.] 不指定表空间时，则该区使用该用户的缺省表空间。
* 查询每个用户的用户缺省表空间：
SQL> select username, default_tablespace from dba_users; // 使用dba用户查询

[*]查询可以使用的表空间名字：
SQL> select tablespace_name, file_name from dba_data_files; // 使用dba（sys或system）来执行

SQL> select dba_users.username, dba_users.default_tablespace, dba_data_files.file_name 
	from dba_users, dba_data_files
 	where dba_users.default_tablespace = dba_data_files.tablespace_name;

[temp表空间不能用于数据分区。]

三、分区表的查询方法：
SQL> select * from employee;			// 按没有分区的方法查询

SQL> select * from employee partition(p1); 		// 只查询p1分区的数据。
SQL> create table part3 as select * from employee partition(p3);

四、分区表的修改：
1、增加分区：
SQL> Alter table employee ADD 
	partition p4 values less than(1500) tablespace users;

[*]分区数据字典：
SQL> select partition_name, high_value, tablespace_name 
	from user_tab_partitions
	where table_name = 'EMPLOYEE';
	
2、删除分区:
SQL> Alter table employee DROP partition p4;			// 结构数据全部删除（相应分区及数据全部被删除)
SQL> Alter table employee TRUNCATE partition p4;		// 保留结构(即区还存在)，数据删除

3、修改区名：
SQL> Alter table employee RENAME partition p4 to p5;

4、分区数据移动：将分区数据从一个表空间移动到另一个表空间
SQL>Alter table employee MOVE partition p4 tablespace system;

5、分区的拆分：

SQL> Alter table employee SPLIT
	partition p3 at(900)
	into(partition p31, partition p32);
	
/* 关于数据字典的几点说明：
v$打头的数据字典，后面不会以s结尾，例如：V$database, v$session;
user打头的，后面都会以s结尾(复数), 如：user_tab_partitions, user_tables
dba打头的，有的以s结尾，有的不。
***************************************************************************/

6、分区的合并：
SQL> Alter table employee MERGE partitions p31, p32 into partition p3;


[第七章　SQL*Plus 报表功能]

/**********************  插入内容：数据字典的一些说明 *****************************
(1) user_XXX：用户，例如：user_tables
(2) dba_XXX: DBA专用
(3) all_XXX: 本用户建的，或者其它用户创建本用户可以查询的（需要其它用户的授权）
(4) v$XXX: 动态数据字典，如：v$database, v$instance, v$session，这些数据字典在oracle不启动时也能查询
***********************************************************************************/
一、定义表头与表尾
SQL> ttitle '表头'

SQL> btitile '表尾'

失效：SQL> ttitle off
	SQL> btittle off

二、定义列名
语法：SQL> column 列名 heading 别名 		// 别名不区分大小写

三、定义列格式：
SQL> column 列名 Format 格式
常用列格式：An :  A为字符，n为最大字符宽度。

$99.9999.99
9.99eeee

例：SQL> Column sal format $99.9999.99
SQL> Column comm like Sal

四、分组命令：
语法：SQL> break on 列名　skip n 
例：SQL> break on deptno skip 2
SQL> select * from emp order by deptno;

五、统计计算：
语法：SQL> compute 函数 of 统计列 on skip n		//可以使用的函数有：sum, max, min, avg,count, var(斜方差), std(标准差)
例：SQL> compute sum of sal on deptno 
清除命令：SQL> clear compute
		SQL> clear break
		SQL> clear column


增加报表级统计：
SQL> break on deptno on REPORT 
SQL> compute sum of sal on report
// 整个报表出一个结果，上面两行都要运行，那么整个报表会根据你的设置出一个sum of sal的总计结果。


[第八章　函数]

一、日期格式转换函数：to_char(日期变量,'格式')		// 格式要用单引号括起来

(1) 日期格式构成方法:

		年			月			日			时				分		秒
		yy 			mm			dd			hh(12小时制）		mi		ss
		yyyy			mon			dy(星期)		hh24（24小时制）
					month		day			
	
A. yy.mm.dd, yy/mm/dd, yy-mm-dd, yyyy.mm.dd, ... 加中文也可以，中文要用又引号括起来

SQL>select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;		// dual是一个虚拟表，任何用户都可以使用。

二、聚组函数：从一组中返回汇总信息
聚组函数有：Sum, count, count distinct, max, min, avg, stddev(标准差)

例：SQL>select min(sal), max(sal), avg(sal), sum(sal) form emp;
SQL> select ename, job, sal from emp where sal=(slect max(sal) from emp);
SQL> select count(*) from emp;
.....[请参考PowerPiont教程：SQL讲稿.ppt]