一、默认情况下
–创建t_orascn测试表
SQL> create table t_orascn(id number);
Table created
–插入两条数据
SQL> insert into t_orascn values(1);
1 row inserted
SQL> insert into t_orascn values(2);
1 row inserted
SQL> commit;
Commit complete
–查询ora_rowscn和相关数据
SQL> select ora_rowscn,id from t_orascn;
ORA_ROWSCN ID
———- ———-
559036 1
559036 2
–更新其中一条数据
SQL> update t_orascn set id=2 where rownum=1;
1 row updated
SQL> commit;
Commit complete
–再查询ora_rowscn和相关数据
SQL> select ora_rowscn,id from t_orascn;
ORA_ROWSCN ID
———- ———-
559669 2
559669 2
–查询更详细信息
SQL> Select versions_xid,versions_startscn,versions_endscn,
2 DECODE(versions_operation,’I’,’Insert’,’U’,’Update’,’D’,’Delete’, ‘Original’) “Operation”, id from t_orascn versions between scn minvalue and maxvalue;
VERSIONS_XID VERSIONS_STARTSCN VERSIONS_ENDSCN Operation ID
—————- —————– ————— ——— ———-
0500180055010000 559669 Update 2
02001C0068010000 559036 Insert 2
02001C0068010000 559036 559669 Insert 1
–查询操作时间
SQL> select to_char(scn_to_timestamp(ora_rowscn),’yyyy-mm-dd hh24:mi:ss’),id from t_orascn;
TO_CHAR(SCN_TO_TIMESTAMP(ORA_R ID
—————————— ———-
2011-04-11 00:01:12 2
2011-04-11 00:01:12 2
–查询数据详细操作时间
SQL> Select versions_xid,to_char(scn_to_timestamp(versions_startscn),’yyyy-mm-dd hh24:mi:ss’),versions_endscn,DECODE(versions_operation,’I’,’Insert’,’U’,’Update’,’D’,’Delete’, ‘Original’) “Operation”, id from t_orascn versions between scn minvalue and maxvalue;
VERSIONS_XID TO_CHAR(SCN_TO_TIMESTAMP(VERSI VERSIONS_ENDSCN Operation ID
—————- —————————— ————— ——— ———-
0500180055010000 2011-04-11 00:01:12 Update 2
02001C0068010000 2011-04-10 23:59:03 Insert 2
02001C0068010000 2011-04-10 23:59:03 559669 Insert 1
–结论：ora_rowscn在没有默认情况下，如果数据库块中的任何一条记录发生改变，该块中的所有记录的ora_rowscn中对应的scn值都改变
二、创建表含有rowdependencies测试
–创建测试表t_orascn_b
SQL> create table t_orascn_b(id number) rowdependencies;
Table created
SQL> insert into t_orascn_b values(1);
1 row inserted
SQL> insert into t_orascn_b values(2);
1 row inserted
SQL> commit;
Commit complete
SQL> select ora_rowscn,id from t_orascn_b;
ORA_ROWSCN ID
———- ———-
560532 1
560532 2
SQL> insert into t_orascn_b values(3);
1 row inserted
SQL> select ora_rowscn,id from t_orascn_b;
ORA_ROWSCN ID
———- ———-
560532 1
560532 2
3
SQL> commit;
Commit complete
SQL> select ora_rowscn,id from t_orascn_b;
ORA_ROWSCN ID
———- ———-
560532 1
560532 2
560555 3
–说明一点：没有提交ora_rowscn不改变(update)或者不存在（insert）
SQL> update t_orascn_b set id=10 where id<2; 1 row updated SQL> commit;
Commit complete
SQL> select to_char(scn_to_timestamp(ora_rowscn),’yyyy-mm-dd hh24:mi:ss’),id from t_orascn_b;
TO_CHAR(SCN_TO_TIMESTAMP(ORA_R ID
—————————— ———-
2011-04-11 00:15:38 10
2011-04-11 00:12:37 2
2011-04-11 00:13:28 3
SQL>
SQL> Select versions_xid,versions_startscn,versions_endscn,
2 DECODE(versions_operation,’I’,’Insert’,’U’,’Update’,’D’,’Delete’, ‘Original’) “Operation”, id from t_orascn_b versions between scn minvalue and maxvalue;
VERSIONS_XID VERSIONS_STARTSCN VERSIONS_ENDSCN Operation ID
—————- —————– ————— ——— ———-
0800290054010000 560614 Update 10
0500130056010000 560555 Insert 3
0A00090001010000 560532 Insert 2
0A00090001010000 560532 560614 Insert 1
SQL> Select versions_xid,to_char(scn_to_timestamp(versions_startscn),’yyyy-mm-dd hh24:mi:ss’),versions_endscn,DECODE(versions_operation,’I’,’Insert’,’U’,’Update’,’D’,’Delete’, ‘Original’) “Operation”, id from t_orascn_b versions between scn minvalue and maxvalue;
VERSIONS_XID TO_CHAR(SCN_TO_TIMESTAMP(VERSI VERSIONS_ENDSCN Operation ID
—————- —————————— ————— ——— ———-
0800290054010000 2011-04-11 00:15:38 Update 10
0500130056010000 2011-04-11 00:13:28 Insert 3
0A00090001010000 2011-04-11 00:12:37 Insert 2
0A00090001010000 2011-04-11 00:12:37 560614 Insert 1
–结论：如果创建表时指定了rowdependencies，则ora_rowscn是以行为单位变化，而不是块

CONTROL_FILE_RECORD_KEEP_TIME
表示控制文件里可重复使用的记录所能保存的最小天数。如果新增加一条记录到控制文件可以重复使用的部分，这时最老的记录尚没有超出最小保留天数，那么记录将控制文件的这一部分将扩展。如果将该参数设置为0，那么控制文件可以重复使用的部分将永远不会扩展。
注意，这个参数只应用于控制文件中可循环利用的部分，如归档日志文件，各种备份记录。不应用于诸如数据文件，表空间，重做线程等，这些内容只有当其从对应的表空间中删除后才能重用。
LOG_ARCHIVE_LOCAL_FIRST
specifies when the archiver processes (ARCn) transmit redo data to remote standby database destinations.（11g中不建议使用）可选值：true/false
true
Directs the ARCn process to transmit redo data after the online redo log file has been completely and successfully archived to at least one local destination. This is the default value.
Because the online redo log files are archived locally first, the LGWR process reuses the online redo log files much earlier than would be possible if the ARCn processes archived to the standby database concurrently with the local destination. This behavior is useful when archiving to remote destinations that use a slow network connection, such as a long-distance wide area network (WAN).
false
Directs the ARCn process to transmit redo data at the same time the online redo log file is archived to the local destinations. This results in redo data being promptly dispatched to the remote standby database destination.
Setting LOG_ARCHIVE_LOCAL_FIRST to false is most useful for faster network connections, such as high-speed local area networks (LAN).

本篇文章以v$parameter和gv$parameter为例做讲解
1、查看v$parameter对象类型，并查看其创建sql语句

SELECT * FROM all_objects a WHERE a.object_name='V$PARAMETER';
select  dbms_metadata.get_ddl(object_type=>'SYNONYM',NAME=>'V$PARAMETER',SCHEMA=>'PUBLIC') from dual;
--V$PARAMETER SYNONYN
CREATE OR REPLACE PUBLIC SYNONYM "V$PARAMETER" FOR "SYS"."V_$PARAMETER"

2、查看V_$PARAMETER对象类型，并查看其创建语句

SELECT *　FROM all_objects a WHERE a.object_name='V_$PARAMETER';
select  dbms_metadata.get_ddl(object_type=>'VIEW',NAME=>'V_$PARAMETER',SCHEMA=>'SYS') from dual;
--V_$PARAMETER VIEW
  CREATE OR REPLACE FORCE VIEW "SYS"."V_$PARAMETER"
  ("NUM", "NAME", "TYPE", "VALUE", "DISPLAY_VALUE", "ISDEFAULT", "ISSES_MODIFIABLE",
  "ISSYS_MODIFIABLE", "ISINSTANCE_MODIFIABLE", "ISMODIFIED", "ISADJUSTED", "ISDEPRECATED",
  "ISBASIC", "DESCRIPTION", "UPDATE_COMMENT", "HASH") AS
  SELECT "NUM",
         "NAME",
         "TYPE",
         "VALUE",
         "DISPLAY_VALUE",
         "ISDEFAULT",
         "ISSES_MODIFIABLE",
         "ISSYS_MODIFIABLE",
         "ISINSTANCE_MODIFIABLE",
         "ISMODIFIED",
         "ISADJUSTED",
         "ISDEPRECATED",
         "ISBASIC",
         "DESCRIPTION",
         "UPDATE_COMMENT",
         "HASH"
    FROM V$PARAMETER;

3、查看V$PARAMETER类型，并查看其创建语句

SELECT * FROM v$fixed_table a WHERE a.name='V$PARAMETER';
SELECT * FROM V$FIXED_VIEW_DEFINITION WHERE VIEW_name='V$PARAMETER';
 --V$PARAMETER VIEW
    SELECT NUM,
           NAME,
           TYPE,
           VALUE,
           DISPLAY_VALUE,
           ISDEFAULT,
           ISSES_MODIFIABLE,
           ISSYS_MODIFIABLE,
           ISINSTANCE_MODIFIABLE,
           ISMODIFIED,
           ISADJUSTED,
           ISDEPRECATED,
           ISBASIC,
           DESCRIPTION,
           UPDATE_COMMENT,
           HASH
      FROM GV$PARAMETER
     WHERE INST_ID = USERENV('Instance')

4、查看GV$PARAMETER类型，及其创建sql语句

SELECT * FROM v$fixed_table a WHERE a.name='GV$PARAMETER';
SELECT * FROM V$FIXED_VIEW_DEFINITION WHERE VIEW_name='GV$PARAMETER';
--GV$PARAMETER  VIEW
     SELECT X.INST_ID,
            X.INDX + 1,
            KSPPINM,
            KSPPITY,
            KSPPSTVL,
            KSPPSTDVL,
            KSPPSTDF,
            DECODE(BITAND(KSPPIFLG / 256, 1), 1, 'TRUE', 'FALSE'),
            DECODE(BITAND(KSPPIFLG / 65536, 3),
                   1,
                   'IMMEDIATE',
                   2,
                   'DEFERRED',
                   3,
                   'IMMEDIATE',
                   'FALSE'),
            DECODE(BITAND(KSPPIFLG, 4),
                   4,
                   'FALSE',
                   DECODE(BITAND(KSPPIFLG / 65536, 3), 0, 'FALSE', 'TRUE')),
            DECODE(BITAND(KSPPSTVF, 7),
                   1,
                   'MODIFIED',
                   4,
                   'SYSTEM_MOD',
                   'FALSE'),
            DECODE(BITAND(KSPPSTVF, 2), 2, 'TRUE', 'FALSE'),
            DECODE(BITAND(KSPPILRMFLG / 64, 1), 1, 'TRUE', 'FALSE'),
            DECODE(BITAND(KSPPILRMFLG / 268435456, 1), 1, 'TRUE', 'FALSE'),
            KSPPDESC,
            KSPPSTCMNT,
            KSPPIHASH
       FROM X$KSPPI X, X$KSPPCV Y
      WHERE (X.INDX = Y.INDX)
        AND BITAND(KSPPIFLG, 268435456) = 0
        AND ((TRANSLATE(KSPPINM, '_', '#') NOT LIKE '##%') AND
            ((TRANSLATE(KSPPINM, '_', '#') NOT LIKE '#%') OR
            (KSPPSTDF = 'FALSE') OR (BITAND(KSPPSTVF, 5) > 0)))

5、查看gv$parameter同义词和gv_$parameter视图

--GV$PARAMETER  SYNONYM
CREATE OR REPLACE PUBLIC SYNONYM "GV$PARAMETER" FOR "SYS"."GV_$PARAMETER"
--GV_$PRAMETER VIEW
CREATE OR REPLACE FORCE VIEW "SYS"."GV_$PARAMETER"
("INST_ID", "NUM", "NAME", "TYPE", "VALUE", "DISPLAY_VALUE", "ISDEFAULT",
"ISSES_MODIFIABLE", "ISSYS_MODIFIABLE", "ISINSTANCE_MODIFIABLE", "ISMODIFIED",
 "ISADJUSTED", "ISDEPRECATED", "ISBASIC", "DESCRIPTION", "UPDATE_COMMENT", "HASH") AS
  SELECT "INST_ID",
         "NUM",
         "NAME",
         "TYPE",
         "VALUE",
         "DISPLAY_VALUE",
         "ISDEFAULT",
         "ISSES_MODIFIABLE",
         "ISSYS_MODIFIABLE",
         "ISINSTANCE_MODIFIABLE",
         "ISMODIFIED",
         "ISADJUSTED",
         "ISDEPRECATED",
         "ISBASIC",
         "DESCRIPTION",
         "UPDATE_COMMENT",
         "HASH"
    FROM GV$PARAMETER

6、总结
x$(table)–>gv$(view)–>v$(view)–>v_$(view)–>v$(SYNONYM)
x$(table)–>gv$(view)–>gv_$(view)–>gv$(synonym)
1）sys的fixed table x$
2）sys的fixed view: GV$
3）sys的fixed view: V$
4）sys的普通view: V_$
5）public的synonym: v$
6) sys的fixed view: gv_$
7) public的synonym: gv$
如果用sys访问v$, 就是3=>2=>1,other user访问v$, 就是5=>4=>3=>2=>1.
如果用sys访问gv$, 就是6=>2=>1,other user访问gv$, 就是7=>6=>2=>1.

1、使用trace查找show parameter执行语句

alter session set events '10046 trace name context forever,level 12';
show parameter process;
 alter session set events '10046 trace name context off';

2、查找trace文件

SELECT    d.VALUE
           || '/'
           || LOWER (RTRIM (i.INSTANCE, CHR (0)))
           || '_ora_'
           || p.spid
          || '.trc' trace_file_name
      FROM (SELECT p.spid
              FROM v$mystat m, v$session s, v$process p
             WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
          (SELECT t.INSTANCE
             FROM v$thread t, v$parameter v
            WHERE v.NAME = 'thread'
              AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
          (SELECT VALUE
             FROM v$parameter
            WHERE NAME = 'user_dump_dest') d

3、格式化trace文件

tkprof D:\oracle\diag\rdbms\xff\xff\trace\xff_ora_4780.trc D:\oracle\diag\rdbms\xff\xff\trace\abc.txt

4、找出对应sql语句

SELECT NAME NAME_COL_PLUS_SHOW_PARAM,
       DECODE(TYPE,
              1,
              'boolean',
              2,
              'string',
              3,
              'integer',
              4,
              'file',
              5,
              'number',
              6,
              'big integer',
              'unknown') TYPE,
       DISPLAY_VALUE VALUE_COL_PLUS_SHOW_PARAM
  FROM V$PARAMETER
 WHERE UPPER(NAME) LIKE UPPER('%process%')
 ORDER BY NAME_COL_PLUS_SHOW_PARAM, ROWNUM;

5、查找上面语句发现使用v$parameter对象
6、查看第一种来源

select owner,object_name,object_type from dba_objects where object_name ='V$PARAMETER';
select  dbms_metadata.get_ddl(object_type=>'SYNONYM',NAME=>'V$PARAMETER',SCHEMA=>'PUBLIC') from dual;

7、无结果，查看第二种来源

select * from v$fixed_table where name ='V%PARAMETER%';
 select * from v$fixed_view_definition where view_name='GV$PARAMETER';

8、得出结果，从中学习到数据库中的对象有两种来源
1）sys的fixed table : x$ksppi x, x$ksppcv y
2）sys的fixed view: GV$PARAMETER
3）sys的fixed view: V$PARAMETER
4） sys的普通view: V_$PARAMETER
5） public的synonym: v$parameter
所以，如果用sys访问v$parameter, 就是3=>2=>1
other user访问v$parameter, 就是5=>4=>3=>2=>1.

需要显示授权
grand select on sys.V_$SESSION to xxx;
说明：不能直接授权给v$session
原因：v$session是公共同义词，不能被授权
(可以通过select * from dba_objects where object_name=’V$SESSION’;查询出来)
note：11gr2版本存在该问题，其他版本暂时未知

1、创建表

create table test_rollup
(
type_name varchar2(10),
type_value number
);

2、插入数据结果

select * from test_rollup;
a	123
a	423
a	523
b	223
b	283
c	103
c	843
c	899
c	100

3、使用rollup函数

select type_name, sum(type_value)
  from test_rollup
 group by rollup(type_name);
a	1069
b	506
c	1945
	3520

4、使用rollup和grouping函数

select type_name, sum(type_value), grouping(type_name)
  from test_rollup
 group by rollup(type_name);
a	1069	0
b	506	0
c	1945	0
	3520	1

5、显示统计信息

select decode(grouping(type_name), 0, type_name, '总计'), sum(type_value)
  from test_rollup
 group by rollup(type_name);
a	1069
b	506
c	1945
总计	3520

1、创建查看隐藏参数视图（show_hidden_v$parameter）

--conn "/ as sysdba"
create or replace view show_hidden_v$parameter
    (INST_ID, NUM , NAME , TYPE , VALUE , DISPLAY_VALUE, ISDEFAULT , ISSES_MODIFIABLE , ISSYS_MODIFIABLE , ISINSTANCE_MODIFIABLE,
    ISMODIFIED , ISADJUSTED , ISDEPRECATED, DESCRIPTION, UPDATE_COMMENT, HASH)
    as
    select x.inst_id,
           x.indx + 1,
           ksppinm,
           ksppity,
           ksppstvl,
          ksppstdvl,
          ksppstdf,
          decode(bitand(ksppiflg / 256, 1), 1, 'TRUE', 'FALSE'),
          decode(bitand(ksppiflg / 65536, 3),
                 1,
                 'IMMEDIATE',
                 2,
                 'DEFERRED',
                 3,
                 'IMMEDIATE',
                 'FALSE'),
          decode(bitand(ksppiflg, 4),
                 4,
                 'FALSE',
                 decode(bitand(ksppiflg / 65536, 3), 0, 'FALSE', 'TRUE')),
          decode(bitand(ksppstvf, 7), 1, 'MODIFIED', 4, 'SYSTEM_MOD', 'FALSE'),
          decode(bitand(ksppstvf, 2), 2, 'TRUE', 'FALSE'),
          decode(bitand(ksppilrmflg / 64, 1), 1, 'TRUE', 'FALSE'),
          ksppdesc,
          ksppstcmnt,
          ksppihash
     from x$ksppi x, x$ksppcv y
    where (x.indx = y.indx);

2、授权用户访问隐藏视图（show_hidden_v$parameter）

--conn "/ as sysdba"
grant select on show_hidden_v$parameter to chf;

3、建立同义词（v$parameter）

--conn chf/xifenfei（sys下面已经有该同义词）
create synonym v$parameter for sys.show_hidden_v$parameter;

4、直接查询隐藏参数

select
x.ksppinm name,
x.ksppdesc description,
y.ksppstvl value,
y.ksppstdf isdefault,
decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod,
decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE') isadjusted from
sys.x$ksppi x,
sys.x$ksppcv y
where
x.inst_id=userenv('Instance') and
y.inst_id=userenv('Instance') and
x.indx=y.indx and
x.ksppinm like '%_&par%'
order by
translate(x.ksppinm,'_','');

因为开发需求需要，存储多个列的结果集，使用table()可以实现
1、创建type

create or replace type t_yqjjrgstj as object
(
  THSCODE   VARCHAR2(96),
  GSJC      VARCHAR2(120) ,
  SSHY      VARCHAR2(180),
  STOCKCODE VARCHAR2(18) ,
  KGLX      VARCHAR2(360),
  GPLX      VARCHAR2(180),
  SSXQ      VARCHAR2(60),
  H_CODE    VARCHAR2(30),
  --额外数据，挑选条件中需要
  JYSDM     VARCHAR2(36) ,--交易所代码
  EJHY      VARCHAR2(180),--证监会二级分类
  JJKGR     VARCHAR2(360)--实际控股人
)

2、实例化type

create or replace type t_jgb_yqjjrgstj as table of t_yqjjrgstj;

3、创建函数

create or replace function f_gjb_yqjjrgstj(in_date in date default sysdate)
  return t_jgb_yqjjrgstj
  PIPELINED as
  v_test_1 t_jgb_yqjjrgstj := t_jgb_yqjjrgstj();
begin
  for t in (select * from test_yqjbzl) loop
    pipe row(t_yqjjrgstj(t.thscode,
                         t.gsjc,
                         t.sshy,
                         t.stockcode,
                         t.kglx,
                         t.gplx,
                         t.ssxq,
                         t.h_code,
                         t.jysdm,
                         t.ejhy,
                         t.jjkgr));
  end loop;
  return;
end f_gjb_yqjjrgstj;

4、测试

select thscode "同花顺" from table(f_gjb_yqjjrgstj()) where SSXQ='北京';

1.增加列
ALTER TABLE table_name ADD( column datatype [DEFAULT EXPR][,column datatype…]);
例如:
SQL>ALTER TABLE emp01 ADD eno NUMBER(4);
2.修改列定义
例如:
SQL>ALTER TABLE emp01 MODIFY job VARCHAR2(15)
2 DEFAULT ‘CLERK’
3.删除列
例如:
SQL> ALTER TABLE emp01 DROP COLUMN dno;
4.修改列名
例如:
SQL>ALTER TABLE emp01 RENAME COLUMN eno TO empno;
5.修改表名
例如:
SQL>RENAME emp01 TO employee;
6.增加注释
例如:
SQL>COMMENT ON TABLE employee IS ‘存放雇员信息’;

一、查询表主键列

select column_name
  from dba_cons_columns
 where constraint_name in (select constraint_name
                             from dba_constraints
                            where table_name = upper('tablename')
                              and constraint_type = 'P');

二、查询表外键列

select column_name
  from dba_cons_columns
 where constraint_name in (select constraint_name
                             from dba_constraints
                            where table_name = upper('tablename')
                              and constraint_type = 'R');

三、查询表中列

select column_name
  from dba_tab_columns
 where table_name = upper('tablename');

四、查询表之间的主外键关系

select b.table_name  主键表名,
       b.column_name 主键列名,
       a.table_name  外键表名,
       a.column_name 外键列名
  from (select a.constraint_name,
               b.table_name,
               b.column_name,
               a.r_constraint_name
          from dba_constraints a, dba_cons_columns b
         WHERE a.constraint_type = 'R'
           and a.constraint_name = b.constraint_name) a,
       (select distinct a.r_constraint_name, b.table_name, b.column_name
          from dba_constraints a, dba_cons_columns b
         WHERE a.constraint_type = 'R'
           and a.r_constraint_name = b.constraint_name) b
 where a.r_constraint_name = b.r_constraint_name;