1.最重要的区别,存储的数据的重要性不同
sys用户:所有oracle的数据字典的基表和视图都存放在sys用户中,这些基表和视图对于oracle的运行是至关重要的,由数据库自己维护,任何用户都不能手动更改.sys用户拥有dba,sysdba,sysoper等角色或权限,是oracle权限最高的用户.
system用户 : 用于存放次一级的内部数据,如oracle的一些特性或工具的管理信息.system用户拥有普通dba角色权限.
2.其次的区别,权限的不同.
system用户:只能用normal身份登陆em,除非你对它授予了sysdba的系统权限或者syspoer系统权限.
sys用户:具有“SYSDBA”或者“SYSOPER”系统权限,登陆em也只能用这两个身份,不能用normal.
以sys用户登陆Oracle,执行select * from V_$PWFILE_USERS;可查询到具有sysdba权限的用户,如:SQL> select * from V_$PWFILE_USERS;

dba, sysdba这两个系统角色有什么区别呢 
在说明这一点之前我需要说一下oracle服务的创建过程
创建实例→•启动实例→•创建数据库(system表空间是必须的)
启动过程
实例启动→•装载数据库→•打开数据库
sysdba,是管理oracle实例的,它的存在不依赖于整个数据库完全启动,只要实例启动了,他就已经存在,以sysdba身份登陆,装载数据库、打开数据库.只有数据库打开了,或者说整个数据库完全启动后,dba角色才有了存在的基础.

normal, sysdba, sysoper有什么区别
normal 是普通用户
sysdba拥有最高的系统权限,登陆后是 sys
sysoper主要用来启动、关闭数据库,sysoper 登陆后用户是 public

sysdba和sysoper属于system privilege,也称为administrative
privilege,拥有例如数据库开启关闭之类一些系统管理级别的权限sysdba和sysoper具体的权限可以看下表:

system如果正常登录,它其实就是一个普通的dba用户,但是如果以as sysdba登录,其结果实际上它是作为sys用户登录的,这一点类似Linux里面的sudo的感觉,从登录信息里面我们可以看出来.因此在as sysdba连接数据库后,创建的对象实际上都是生成在sys中的.其他用户也是一样,如果 as sysdba登录,也是作为sys用户登录的,看以下实验:
SQL> create user strong identified by strong;
用户已创建.
SQL> conn strong/strong@magick as sysdba;
已连接.
SQL> show user;
USER 为 “SYS”
SQL> create table test(a int);
表已创建.
SQL> select owner from dba_tables where table_name=’test’;
未选定行 //因为创建表时oracle自动转为大写,所以用小写查的时候是不存在的；
SQL> select owner from dba_tables where table_name=’TEST’;
OWNER
——————————
SYS

1.The RETURNING INTO clause allows us to return column values for rows affected by DML statements. The following test table is used to demonstrate this clause.

DROP TABLE t1;

DROP SEQUENCE t1_seq;

CREATE TABLE t1 (

ID NUMBER(10),

DESCRIPTION VARCHAR2(50),

CONSTRAINT t1_pk PRIMARY KEY (id));

CREATE SEQUENCE t1_seq;

INSERT INTO t1 VALUES (t1_seq.nextval, ‘ONE’);

INSERT INTO t1 VALUES (t1_seq.nextval, ‘TWO’);

INSERT INTO t1 VALUES (t1_seq.nextval, ‘THREE’);

COMMIT;

2.When we insert data using a sequence to generate our primary key value, we can return the primary key value as follows.

SET SERVEROUTPUT ON

DECLARE

v_id t1.id%TYPE;

BEGIN

INSERT INTO t1

VALUES (t1_seq.nextval, ‘FOUR’)

RETURNING id INTO v_id;

COMMIT;

DBMS_OUTPUT.put_line(‘ID=’ || v_id);

END;

/

ID=4 

3.The syntax is also available for update and delete statements.

SET SERVEROUTPUT ON

DECLARE

v_id t1.id%TYPE;

BEGIN

UPDATE t1

SET description = description

WHERE description = ‘FOUR’

RETURNING id INTO v_id;

DBMS_OUTPUT.put_line(‘UPDATE ID=’ || v_id);

DELETE FROM t1

WHERE description = ‘FOUR’

RETURNING id INTO v_id;

DBMS_OUTPUT.put_line(‘DELETE ID=’ || v_id);

COMMIT;

END;

/

UPDATE ID=4

DELETE ID=4

4.When DML affects multiple rows we can still use the RETURNING INTO, but now we must return the values into a collection using the BULK COLLECT clause.

SET SERVEROUTPUT ON

DECLARE

TYPE t_tab IS TABLE OF t1.id%TYPE;

v_tab t_tab;

BEGIN

UPDATE t1

SET description = description

RETURNING id BULK COLLECT INTO v_tab;

FOR i IN v_tab.first .. l_tab.last LOOP

DBMS_OUTPUT.put_line(‘UPDATE ID=’ || l_tab(i));

END LOOP;

COMMIT;

END;

/

UPDATE ID=1

UPDATE ID=2

UPDATE ID=3

5.We can also use the RETURNING INTO clause in combination with bulk binds.

SET SERVEROUTPUT ON

DECLARE

TYPE t_desc_tab IS TABLE OF t1.description%TYPE;

TYPE t_tab IS TABLE OF t1%ROWTYPE;

l_desc_tab t_desc_tab := t_desc_tab(‘FIVE’, ‘SIX’, ‘SEVEN’);

l_tab t_tab;

BEGIN

FORALL i IN l_desc_tab.first .. l_desc_tab.last

INSERT INTO t1

VALUES (t1_seq.nextval, l_desc_tab(i))

RETURNING id, description BULK COLLECT INTO l_tab;

FOR i IN l_tab.first .. l_tab.last LOOP

DBMS_OUTPUT.put_line(‘INSERT ID=’ || l_tab(i).id || ‘ DESC=’ || l_tab(i).description);

END LOOP;

COMMIT;

END;

/

INSERT ID=5 DESC=FIVE

INSERT ID=6 DESC=SIX

INSERT ID=7 DESC=SEVEN

6.This functionality is also available from dymanic SQL.

SET SERVEROUTPUT ON

DECLARE

TYPE t_tab IS TABLE OF t1.id%TYPE;

l_tab t_tab;

BEGIN

EXECUTE IMMEDIATE ‘UPDATE t1

SET description = description

RETURNING id INTO :l_tab’

RETURNING BULK COLLECT INTO l_tab;

FOR i IN l_tab.first .. l_tab.last LOOP

DBMS_OUTPUT.put_line(‘UPDATE ID=’ || l_tab(i));

END LOOP;

COMMIT;

END;

/

UPDATE ID=1

UPDATE ID=2

UPDATE ID=3

准备工作:

OrclDBLink =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.2)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = ORCL.SNDA.COM)

    )

  )

  

  

  

SID 主要用于区分同一台计算机上的同一个数据库的不同实例.SID 与ORACLE_HOM 一起HASH 出来一个UNIQUE KEY NAME 分配给SGA.
ORACLE 数据库服务器主要有两部分组成：物理数据库和数据库管理系统
数据库管理系统是用户和物理数据库之间的一个中间层, 是软件层. 这个软件层具有一定的结构, 这个结构又被称为例程结构.
在启动数据库时, ORACLE 首先要在内存中获取,划分,保留各种用途的区域, 运行各种用途的后台进程, 即创建一个例程( instance ) , 然后由该例程装载, 打开数据库, 最后由这个例程来访问和控制数据库的各种物理结构.
在启动数据库并使用数据库的时候, 实际上是连接到该数据库的例程, 通过例程来连接、使用数据库. 所以例程是用户和数据库之间的一个中间层.
例程是由操作系统的内存结构和一系列进程所组成的, 可以启动和关闭.
一台计算机上可以创建多个ORACLE 数据库, 当同时要使用这些数据库时, 就要创建多个例程. 为了不使这些例程相互混淆, 每个例程都要用称为SID 的符号来区分, 即创建这些数据库时填写的数据库SID.

1. delete/truncate 只删除数据不删除表,索引的结构. drop 将删除表的结构被依赖的index/constrain/trigger,依赖于该表的 procedure/function 将保留,但是变为 invalid 状态

2. delete 是 dml,写 rollback segement,可回滚,速度慢,事务提交之后才生效.在 9i 满足undo_retention 条件下可使用 flashback.一次性大批量数据的 delete 可能导致回滚段急剧扩展从而影响到数据库,慎用.触发 trigger. truncate/drop 是 ddl,隐式提交,不写 rollback segment,不能回滚,速度快.9i不能使用 flashback.不触发 trigger

3. delete 不影响表所占用的 extent, HWM 保持原位置不动,即使删除的是最靠近 HWM 的数据.delete 其实也可以释放空间,但是不降低 HWM, delete 后 block 的空闲空间达到 pct_used,就可以重用. truncate 缺省情况下将空间(表和索引)释放到 minextents 个 extent,除非使用 reuse storage.truncate 会将高水线复位(回到最开始). drop 将表所占用的空间全部释放,segment 不存在,无所谓 HWM 的概念

4. truncate/drop 的对象必须是本模式下的,或者被授予 drop any table 的权限,但 drop any table 权限不能 truncate/drop sys 的表. delete 的对象必须是本模式下的,或者被授予 delete on SCHEMA.table或 delete any table 的权限,但 delete any table 权限不能 delete sys 的表

5. 不能 truncate 一个带有 enable 外键的表,不管表里有没有数据,如果要 truncate,首先要 disable 外键或者删除外键(drop 外键的表肯定是删除了外键).不能 drop 一个带有 enable 外键的表,不管表里有没有数据,如果要 drop,首先要删除外键,或者直接用 drop table TABLE_NAME cascade constraints; 级联删除外键. delete 可以.

PURPOSE:

    To create a sequence. A sequence is a database object from which multiple users may generate unique integers. You can use sequences to automatically generate primary key values.

SYNTAX:

CREATE SEQUENCE [schema.]sequence

    [INCREMENT BY integer]

    [START WITH integer]

    [MAXVALUE integer | NOMAXVALUE]

    [MINVALUE integer | NOMINVALUE]

    [CYCLE | NOCYCLE]

    [CACHE integer | NOCACHE]

    [ORDER | NOORDER]

where:

schema

    is the schema to contain the sequence. If you omit schema, Oracle creates the sequence in your own schema.

sequence

    is the name of the sequence to be created.

INCREMENT BY

    specifies the interval between sequence numbers. This value can be any positive or negative Oracle integer, but it cannot be 0. If this value is negative, then the sequence descends. If the increment is positive, then the sequence ascends. If you omit this clause, the interval defaults to 1.

MINVALUE

    specifies the sequence’s minimum value.

NOMINVALUE

    specifies a minimum value of 1 for an ascending sequence or -10 for a descending sequence.

    The default is NOMINVALUE.

MAXVALUE

    specifies the maximum value the sequence can generate.

NOMAXVALUE

    specifies a maximum value of 10 for a descending sequence.

    The default is NOMAXVALUE.

START WITH

    specifies the first sequence number to be generated. You can use this option to start an ascending sequence at a value greater than its minimum or to start a descending sequence at a value less than its maximum. For ascending sequences, the default value is the sequence’s minimum value. For descending sequences, the default value is the sequence’s maximum value.

CYCLE

    specifies that the sequence continues to generate values after reaching either its maximum or minimum value. After an ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum.

NOCYCLE

    specifies that the sequence cannot generate more values after reaching its maximum or minimum value.

    The default is NOCYCLE.

CACHE

    specifies how many values of the sequence Oracle preallocates and keeps in memory for faster access. The minimum value for this parameter is 2. For sequences that cycle, this value must be less than the number of values in the cycle.

NOCACHE

    specifies that values of the sequence are not preallocated. If you omit both the CACHE parameter and the NOCACHE option, Oracle caches 20 sequence numbers by default. However, if you are using Oracle with the Parallel Server option in parallel mode and you specify the ORDER option, sequence values are never cached, regardless of whether you specify the CACHE parameter or the NOCACHE option.

ORDER

    guarantees that sequence numbers are generated in order of request. You may want to use this option if you are using the sequence numbers as timestamps. Guaranteeing order is usually not important for sequences used to generate primary keys.

NOORDER

    does not guarantee sequence numbers are generated in order of request. If you omit both the ORDER and NOORDER options, Oracle chooses NOORDER by default. Note that the ORDER option is only necessary to guarantee ordered generation if you are using Oracle with the Parallel Server option in parallel mode. If you are using exclusive mode, sequence numbers are always generated in order.

PREREQUISITES:

    To create a sequence in your own schema, you must have CREATE SEQUENCE privilege. To create a sequence in another user’s schema, you must have CREATE ANY SEQUENCE privilege. If you are using Trusted Oracle in DBMS MAC mode, your DBMS label must dominate the creation label of the owner of the schema to contain the sequence.

在Oracle中如果错误地提交了修改操作,然后想查看并修改原来的值,这时候可以使用查询闪回(query flashback).

查询闪回可以根据根据一个时间值或者系统变更号(SCN:System Change Number)进行,数据库使用SCN来跟踪对数据进行的修改,因此可以用它来闪回到数据库中一个特定的SCN时的状态.

一.执行闪回操作,需要使用DBMS_FLASHBACK包,登录用户要具有此包的EXECUTE权限,下面以sys身份登录,并授权给用户EXECUTE权限:

CONNECT SYS/P@ssw0rd AS SYSDBA;

GRANT EXECUTE ON SYS.DBMS_FLASHBACK TO UserName;

二.时间查询闪回

1.执行SQL语句从products中查询前五条记录,如下:

SELECT product_id, name, price, SYSDATE AS TIME

FROM products

WHERE product_id <= 5;

2.更新记录,如下:

SELECT product_id, NAME, price, SYSDATE AS TIME

FROM products

WHERE product_id <= 5;

3.执行DBMS_FLASHBACK.ENABLE_AT_TIME()语句,闪回到一个特定的时间,如下:

DBMS_FLASHBACK.ENABLE_AT_TIME(SYSDATE – 10 / 1440);

闪回到10分钟之前(24h * 60mins = 1440Mins).

4.现在执行查询,结果如下：

5.禁用闪回 EXECUTE DBMS_FLASHBACK.DISABLE(); 在再次启用闪回操作前,必须先将其禁用.这些命令只能在sqlplus中使用,在pl/sql中不能使用.

三.系统变更号查询闪回

根据SCN进行闪回操作比根据时间进行要更精确,因为数据库就是使用SCN来跟踪数据库的变化.

1.获得当前SCN命令：

VARIABLE curr_scn NUMBER;

EXECUTE :curr_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();

print curr_scn

2.插入一条记录.

SELECT * FROM products;

INSERT INTO products(product_id, product_type_id, NAME, DESCRIPTION, price)

VALUES(13, 5, ‘Kobe Bryant’, ‘No.24’, ‘24.00’);

SELECT *

FROM products

WHERE product_id = 13;

3.通过DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER()语句可以闪回到这个SCN的状态,需要一个SCN参数.

EXECUTE DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(:curr_scn);

4.查询刚刚插入的数据,没有任何记录.

SELECT *

FROM products

WHERE product_id = 13;

5.禁用闪回操:EXECUTE DBMS_FLASHBACK.DISABLE();

Oracle从9i开始提供了基于回滚段的闪回查询(Flashback Query)功能,可用于恢复错误的DML操作.在Oracle 10g中对闪回查询做了较大改进,不再局限于闪回查询,还可用于恢复错误的DDL(Drop)操作,闪回表,闪回数据库等.下面介绍两个版本的区别: