本篇文章以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;