[PHP] selenium rc 与 phpunit

-- Preparing Firefox profile...
http://www.qaautomation.net/?p=15

-- manual
http://www.phpunit.de/manual/3.0/ja/selenium.html

[高可用] RAC 基本维护命令

1. crs_* ()

1-1. 资源查询

1-1-1. 查看各节点上资源运行状态和属性
# crs_stat -t -v
# crs_stat -v ora.racdb.service1.racdb1.srv
# crs_stat -p ora.racdb.service1.racdb1.srv

1-1-2. 查看资源权限
# crs_stat -ls


1-2. 资源的启动与停止

1-2-1. 全局资源的启动与停止
# crs_start -all
# crs_stop -all

1-2-2. 单个资源的启动与停止
# crs_start ora.rac1.vip
# crs_stop ora.rac1.vip


1-3. 集群资源配置
# crs_register
# crs_unregister
# crs_profil
# crs_setperm


2. crsctl (管理 crs 集群软件)

2-1. 检查 crs 健康状况
# crsctl check crs

2-2. 控制 crs 服务

2-2-1. 启动停止 crs
# crsctl start crs
# crsctl stop crs

2-2-2. crs 是否随系统一起启动
# crsctl enable crs
# crsctl disable crs

2-3. 启动停止资源
# crsctl start resources -- 只在一个可用节点可以同时启动其他节点资源
# crsctl stop resources -- 只停止本节点的资源


3. srvctl (crs 与 rdbms 的维护管理工具)

3-1. 添加删除资源
# srvctl add
# srvctl delete

3-2. 资源状态监控
# srvctl status database -d racdb

3-3. 资源启动与停止
# srvctl start
# srvctl stop

3-4. 重定义资源属性
# srvctl modify

3-5. 数据库

3-5-1. 配置数据库随 CRS 的启动而自动启动或不启动
# srvctl enable database -d racdb
# srvctl disable database -d racdb

3-5-2. 添加数据库
# srvctl add database -d racdb -o $ORACLE_HOME

3-5-3. 删除数据库
# srvctl remove database -d racdb

3-5-4. 启动数据库 (默认到 OPEN 阶段)
# srvctl start database -d racdb
# srvctl start database -d racdb -o mount
# srvctl start database -d racdb -o nomount

3-5-5. 关闭数据库
# srvctl stop database -d racdb

3-5-6. 查看某个数据库的配置信息
# srvctl config database -d racdb -a


3-6. 实例

3-6-1. 关闭实例的自动启动
# srvctl disable instance -d racdb -i racdb1

3-6-2. 添加实例
# srvctl add instance -d racdb -n rac1 -i racdb1

3-6-3. 删除实例
# srvctl remove instance -d racdb -i racdb1

3-6-4. 关闭实例
# srvctl stop instance -d racdb -i racdb1 -o immediate
# srvctl stop instance -d racdb -i racdb1 -o abort


3-7. 服务

3-7-1. 查看服务运行
# srvctl config service -d racdb -a

3-7-2. 关闭服务在某个实例上运行
# srvctl disable service -d racdb -s service1 -i racdb1

3-7-3. 在指定实例上启动服务
# srvctl start service -d racdb -s service1 -i racdb1

3-7-4.  关闭指定实例上启动服务
# srvctl stop service -d racdb -s service1 -i racdb1

3-7-5.  查看指定实例上的服务
# srvctl satus service -d racdb -v

3-7-6. 查看服务
# srvctl config service -d racdb -a
# srvctl config service -d racdb -a -s service1

3-7-7. 删除服务
# srvctl remove service -d racdb -s service1

3-7-8. 添加服务
-P TAF 策略 可选值为 none，basic，preconnect
# srvctl add service -d racdb -s service1 -r rac1 -a rac2 -P basic
确认添加成功
# srvctl config service -d racdb -s service1 -a


-- 使用 config 查看配置

-- 查看 OCR 中注册的所有数据库
srvctl config database



-- 查看 Node Application 的配置

-- 查看节点名实例 $ORACLE_HOME
srvctl config nodeapps -n rac1

-- 查看 VIP
srvctl config nodeapps -n rac1 -a

-- 查看 GSD
srvctl config nodeapps -n rac1 -g

-- 查看 ONS
srvctl config nodeapps -n rac1 -s

-- 查看 Listener
srvctl config nodeapps -n rac1 -l
srvctl config listener -n rac1

-- 查看 ASM
srvctl config asm -n rac1


-- 跟踪 srvctl
export SRVM_TRACE=true
srvctl config database -d racdb

-- onsctl

-- 用于配置和管理 ONS （Oracle Notification Service）

-------------------------------------------------------------------------------

[高可用] Failover

-- 注意
-- 不要在 Listener 中设置 GLOBAL_DB_NAME，这个功能会禁用 Client Side Connect Time Failover 和 TAF

-- Oracle 10g RAC Failover 3 种

-- Client Side Connect Time Failover
-- 连接时判断节点是否可用
-- 客户端 tnsname 文件中启用 FAILOVER=ON，默认是 ON
RACDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = racdb)
    )
  )

-- Transparent Application Failover
-- 用户连接到某实例之后，如果实例发生故障，则用户被透明的迁移到健康实例，但为提交事务会回滚
RACDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))
      (LOAD_BALANCE = YES)
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = racdb)
      (FAILOVER_MODE =
        (TYPE = SESSION) -- session 重新执行查询 -- select 返回剩余记录，抛弃已经返回的结果集
        (METHOD = BASIC) -- basic 感知节点故障时才创建连接 -- preconnect 最初建立时就建立连接
        (RETRIES = 180)
        (DELAY = 5)
      )
    )
  )

-- Service Side TAF
-- TAF 配置在服务器端数据库字典里
-- 必须配置 Service

-- Instance Role -- 当有多个 Instance 参与 Service 时，可以配置优先使用哪个 Instance 提供服务
-- preferred -- 首选
-- available -- 后备

-- 手动创建 Service

-- 查看当前 Service
sys@RACDB> show parameter service
NAME                                 TYPE            VALUE
------------------------------------ --------------- ------------------------------
service_names                        string          racdb

-- 创建 Service
srvctl add service -d dbname -s service1 -r inst1 -a inst2 -P basic

-- 确认创建成功，此时是 offline 状态
crs_stat -t -v

-- 配置服务自启动
[oracle@rac1 ~]$ srvctl enable service -d racdb -s service1

-- 启动 Service
srvctl start service -d dbname -s service1

-- 确认状态
crs_stat -t -v

-- Service 被添加
show parameter service

-- 修改 TAF 配置
begin
dbms_service.modify_service (
service_name => 'service1',
failover_method => dbms_service.failover_method_basic,
failover_type => dbms_service.failover_type_select,
failover_retries => 180,
failover_delay => 5);
end;
/

-- 确认修改生效
select name, failover_method, failover_type, goal, clb_goal from dba_services where name = 'service1';
NAME     FAILOVER_METHOD  FAILOVER_TYPE  GOAL     CLB_G
-------  ---------------- -------------- -------- -------

service1 BASIC            SELECT         LONG

1 row selected.

-- 查看配置
srvctl config service -d dbname -s service1 -a

-- 客户端 tnsname 指定
XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vip1)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = vip1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = service1)
    )
  )

[高可用] RAC 下的在线日志组重建

-- RAC 各节点都有自己的私有日志组，在各节点上都会有一个自己的日志线程号，这个线程号由参数 THREAD 确定，可以通过show parameters thread查看
sys@XE> show parameter thread
NAME       TYPE            VALUE
---------- --------------- --------
thread    integer       0

-- Log Group 中的 Thread
-- 与参数中不一致？？？
sys@XE> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS          FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- --------------- ------------- -------------------
         1          1       1921    5242880          2 YES INACTIVE              2297490 2011-08-17 17:00:59
         2          1       1922    5242880          2 NO  CURRENT               2318234 2011-08-22 09:16:01

2 rows selected.

sys@XE> select * from gv$log;
   INST_ID     GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS          FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- --------------- ------------- -------------------
         1          1          1       1921    5242880          2 YES INACTIVE              2297490 2011-08-17 17:00:59
         1          2          1       1922    5242880          2 NO  CURRENT               2318234 2011-08-22 09:16:01

2 rows selected.

-- 当给数据库集群各节点创建新的重做日志组时，需要指定这个新建日志组被分配给哪个线程（即THREAD#列对应的值）
alter database add logfile thread 1
     group 11 ('/oracle/redo11a.log','/oracle/redo11b.log') size 200m,
     group 12 ('/oracle/redo12a.log','/oracle/redo12b.log') size 200m;

alter database add logfile thread 2
     group 21 ('/oracle/redo21a.log','/oracle/redo21b.log') size 200m,
     group 22 ('/oracle/redo22a.log','/oracle/redo22b.log') size 200m;

-- 确认当前日志组不是 current 或 active 状态后，删除旧日志组及物理文件

[高可用] 客户端 RAC TNSNAME 配置

rac =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))
      (LOAD_BALANCE = YES)
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = racdb)
      (FAILOVER_MODE =
        (TYPE = SESSION)
        (METHOD = BASIC)
        (RETRIES = 180)
        (DELAY = 5)
      )
    )
  )

[高可用] 相关 URL

http://cheeseheaddba.blogspot.com/2009/09/clusterware-uninstall.html
http://hi.baidu.com/googleu/blog/item/32c756d8e200e32011df9b93.html
http://space.itpub.net/11763200/viewspace-690362
http://www.itpub.net/thread-1331960-1-1.html
http://blog.goo.ne.jp/j_adversaria/e/9eace0f5c9a27066ce6121fd895d8192
https://forums.oracle.com/forums/thread.jspa?threadID=327805
http://www.oracle-base.com/articles/11g/OracleDB11gR2RACInstallationOnOEL5UsingVirtualBox.php#clone_the_vm
http://www.oracle-base.com/articles/10g/OracleDB10gR2RACInstallationOnCentos4UsingVMware.php
http://space.itpub.net/21993926/viewspace-670961
http://hi.baidu.com/nixsql/blog/item/cb3a348bd7b71c8da5c27240.html
http://space.itpub.net/3090/viewspace-667819
http://www.itpub.net/1009235.html
http://space.itpub.net/16546776/viewspace-694772
http://space.itpub.net/32939/viewspace-563317

[工具使用] VirtualBox 创建共享磁盘

1. 创建共享磁盘
VBoxManage createhd --filename "D:\VM\Linux\sharedisk\vote.vdi" --size 120 --format VDI --variant Fixed
VBoxManage createhd --filename "D:\VM\Linux\sharedisk\ocr.vdi" --size 120 --format VDI --variant Fixed
VBoxManage createhd --filename "D:\VM\Linux\sharedisk\asm1.vdi" --size 2000 --format VDI --variant Fixed
VBoxManage createhd --filename "D:\VM\Linux\sharedisk\asm2.vdi" --size 2000 --format VDI --variant Fixed

2. 挂载共享磁盘到虚拟机
VBoxManage storageattach "rac1" --storagectl "SATA Controller" --port 1 --device 0 --type hdd --medium "D:\VM\Linux\sharedisk\vote.vdi" --mtype shareable
VBoxManage storageattach "rac1" --storagectl "SATA Controller" --port 2 --device 0 --type hdd --medium "D:\VM\Linux\sharedisk\ocr.vdi" --mtype shareable
VBoxManage storageattach "rac1" --storagectl "SATA Controller" --port 3 --device 0 --type hdd --medium "D:\VM\Linux\sharedisk\asm1.vdi" --mtype shareable
VBoxManage storageattach "rac1" --storagectl "SATA Controller" --port 4 --device 0 --type hdd --medium "D:\VM\Linux\sharedisk\asm2.vdi" --mtype shareable

3. 修改共享磁盘模式
VBoxManage modifyhd "D:\VM\Linux\sharedisk\vote.vdi" --type shareable
VBoxManage modifyhd "D:\VM\Linux\sharedisk\ocr.vdi" --type shareable
VBoxManage modifyhd "D:\VM\Linux\sharedisk\asm1.vdi" --type shareable
VBoxManage modifyhd "D:\VM\Linux\sharedisk\asm2.vdi" --type shareable

4. 克隆磁盘
VBoxManage clonehd "D:\RAC1.vdi" "D:\RAC2.vdi"

5. 参照 URL
http://www.oracledistilled.com/virtualbox/creating-shared-drives-in-oracle-vm-virtualbox/

[基础知识] spfile 操作

-- 修改 spfile
alter system set
    parameter = value
    <comment = 'text'>
    <deferred>
    <scope = both|spfile|memory>
    <sid = 'sid|*'>；

-- deferred 延迟设置
select name from v$parameter where issys_modifiable = 'DEFERRED';

NAME
----------------------------------------
backup_tape_io_slaves
audit_file_dest
object_cache_optimal_size
object_cache_max_size_percent
sort_area_size
sort_area_retained_size
olap_page_pool_size

7 rows selected.

-- 取消 spfile 设置，恢复默认值
-- 非 rac 环境，可以用 sid = '*'
alter sysetm reset
    parameter <scope = memory|spfile|both> sid = 'sid|*';

-- spfile 中不存在 sid = demo 的这一项
go@DEMO> alter system reset sga_target scope = spfile sid = 'demo';
alter system reset sga_target scope = spfile sid = 'demo'
*
ERROR at line 1:
ORA-32010: cannot find entry to delete in SPFILE

-- 必须指定 spfile
go@DEMO> alter system reset sga_target sid = '*';
alter system reset sga_target sid = '*'
*
ERROR at line 1:
ORA-32009: cannot reset the memory value for instance * from instance demo

-- 指定 spfile 和 sid
go@DEMO> alter system reset sga_target scope = spfile sid = '*';

System altered.

[管理脚本] 日志切换频率

select
    b.SEQUENCE#,
    b.FIRST_TIME,
    a.SEQUENCE#,
    a.FIRST_TIME,
    round(((a.FIRST_TIME-b.FIRST_TIME)*24)*60,2) minutes
from
    v$log_history a, v$log_history b
where
    a.SEQUENCE# = b.SEQUENCE#+1 and
    b.THREAD#=1
order by
    a.SEQUENCE# desc
/

[管理脚本] 每天归档日志量

SELECT
    trunc(completion_time) "DATE",
    count(*) as count,
    round(((sum(blocks*block_size))/1024/1024),2) mb
FROM v$archived_log
GROUP BY trunc(completion_time)
ORDER BY 1
/

[管理脚本] 外部表实现日志报警日志挖掘

-- 可以查询 alert 日志中的任何信息

-- 创建 bdump 目录
sys@DEMO> create directory b_dump_dir as 'D:\oracle\product\10.2.0\admin\demo\bdump';

Directory created.

--
sys@DEMO> select * from all_directories;
OWNER DIRECTORY_NAME  DIRECTORY_PATH
----- --------------- -------------------------------------------
SYS   B_DUMP_DIR      D:\oracle\product\10.2.0\admin\demo\bdump

1 rows selected.

-- 赋予用户权限
sys@DEMO> grant read, write on directory b_dump_dir to go;

Grant succeeded.

sys@DEMO> conn go/go

-- 创建外部表
create table alert_log
(
    text_line varchar(255)
)
organization external
(
    type oracle_loader
    default directory b_dump_dir
    access parameters
    (
        records delimited by newline
        fields
        reject rows with all null fields
    )
    location
    (
        'alert_demo.log'
    )
)
reject limit unlimited
/

-- 执行查询语句
select
    to_char(last_time,'yyyy-mm-dd hh24:mi') shutdown,
    to_char(start_time,'yyyy-mm-dd hh24:mi') startup,
    round((start_time-last_time)*24*60,2) mins_down,
    round((last_time-lag(start_time) over (order by r)),2) days_up,
    case
        when (lead(r) over (order by r) is null )
        then round((sysdate-start_time),2)
    end days_still_up
from
(
    select r,
    to_date(last_time, 'Dy Mon DD HH24:MI:SS YYYY') last_time,
    to_date(start_time,'Dy Mon DD HH24:MI:SS YYYY') start_time
    from
    (
        select
            r,
            text_line,
            lag(text_line,1) over (order by r) start_time,
            lag(text_line,2) over (order by r) last_time
        from
        (
            select
                rownum r,
                text_line
            from
                alert_log
            where
                text_line like '___ ___ __ __:__:__ 20__' or
                text_line like 'Starting ORACLE instance %'
        )
    )
    where
        text_line like 'Starting ORACLE instance %'
)
/

SHUTDOWN          STARTUP           MINS_DOWN  DAYS_UP DAYS_STILL_UP
----------------- ----------------- ---------- ------- -------------
2011-07-24 15:10  2011-07-24 15:10           0
2011-07-24 15:31  2011-07-24 15:31         .65     .01
2011-07-24 15:36  2011-07-24 15:39        2.48       0
2011-07-24 20:08  2011-07-24 20:57       49.08     .19
2011-07-25 21:51  2011-07-25 21:51           0    1.04
2011-07-29 10:18  2011-07-29 10:19          .9    3.52
2011-07-29 10:20  2011-07-29 10:22           2       0
2011-07-29 10:25  2011-07-29 10:25          .1       0
2011-08-07 11:32  2011-08-07 11:32         .07    9.05
2011-08-07 11:35  2011-08-07 11:36         .05       0
2011-08-07 12:38  2011-08-07 12:38          .5     .04
2011-08-07 17:16  2011-08-07 17:16         .13     .19
2011-08-07 17:21  2011-08-07 17:26        5.18       0
2011-08-07 17:32  2011-08-07 17:32         .12       0
2011-08-08 21:14  2011-08-08 21:14           0    1.15
2011-08-08 22:06  2011-08-08 22:06         .35     .04
2011-08-12 21:39  2011-08-12 21:39           0    3.98
2011-08-12 21:40  2011-08-12 21:40         .05       0
2011-08-13 09:25  2011-08-13 09:25           0     .49          .17

19 rows selected.

[管理脚本] 用户的目录操作权限

column grantee format a10
column grantor format a10
column dir_name format a20
column dir_path format a50
column privilege format a10

break on dir_name
select
    d.directory_name dir_name,
    d.directory_path dir_path,
    p.privilege,
    p.grantee,
    p.grantor
from
    dba_tab_privs p,
    dba_directories d
where
    p.table_name = d.directory_name and
    p.grantee = upper('&user')
order by
    d.directory_name,
    p.privilege
/

[管理脚本] 查看用户连接

select
    /*s.osuser,
    decode
    (
        sign(48-command),
        1, to_char(command), 'Action Code #' || to_char(command)
    ) action,
    p.program,*/
    status,
    s.terminal,
    s.program,
    s.username,
    /*s.fixed_table_sequence activity_meter,
    '' query,
    0 memory,
    0 max_memory,
    0 cpu_usage,*/
    s.sid,
    s.serial#
from
    v$session s,
    v$process p
where
    s.paddr = p.addr and
    s.type = 'USER'
order by s.username, s.osuser
/

[管理脚本] 数据库中的对象

break on owner skip 1
select
    owner,
    object_type,
    status,
    count(*) count#
from
    all_objects
group by
    owner,
    object_type,
    status
order by
    owner,
    object_type,
    status
/

[管理脚本] 数据文件大小

column file_name format a50

select
    tablespace_name,
    file_id,
    file_name,
    round(bytes/1024/1024) "SIZE(M)"
from
    dba_data_files
union all
select
    tablespace_name,
    file_id,
    file_name,
    round(bytes/1024/1024) "SIZE(M)"
from
    dba_temp_files
order by tablespace_name, file_id
/

[管理脚本] 回滚段信息

set linesize 200 pagesize 1400;
SELECT
    r.segment_name,
    r.owner,
    r.tablespace_name,
    r.status,
    r.initial_extent/1024 "INITIAL_EXTENT(K)",
    r.next_extent/1024 "NEXT_EXTENT(K)",
    s.extents,
    ROUND(s.rssize/1024) "RSSIZE(K)",
    s.xacts
FROM
    dba_rollback_segs r,
    v$rollname n,
    v$rollstat s
WHERE
    r.segment_name = n.name and n.usn = s.usn
/

[管理脚本] 数据库启动以来回滚率

select
    a.value commits,
    b.value rollbacks,
    to_char(b.value * 100/ (a.value + b.value), '90.99') || '%' rollbacks_rate
from
(
    select name, value
    from v$sysstat where name = 'user commits'
) a
,
(
    select name, value from v$sysstat
    where name = 'transaction rollbacks'
) b
/

[管理脚本] 查看隐藏参数

1.
column name format a30
column value format a20
column describ format a40
select
    x.ksppinm name,
    y.ksppstvl value,
    x.ksppdesc describ
from
    sys.x$ksppi x,
    sys.x$ksppcv y
where
    x.inst_id = userenv('Instance') and
    x.inst_id = y.inst_id and
    x.indx = y.indx and
    x.ksppinm like '%&par%'
    -- x.ksppinm like '/_%' escape '/'
/

2.
set linesize 132
column name format a30
column value format a25
select
    x.ksppinm name,
    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') isadj
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, ' _', ' ')
/

[管理脚本] 找出未加索引的外键

column columns format a30 word_wrapped
column tablename format a15 word_wrapped
column constraint_name format a15 word_wrapped

select
    table_name,
    constraint_name,
    cname1 || nvl2(cname2,',' || cname2,null) ||
        nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
        nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
        nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null) columns
from
(
    select
        b.table_name,
        b.constraint_name,
        max(decode( position, 1, column_name, null )) cname1,
        max(decode( position, 2, column_name, null )) cname2,
        max(decode( position, 3, column_name, null )) cname3,
        max(decode( position, 4, column_name, null )) cname4,
        max(decode( position, 5, column_name, null )) cname5,
        max(decode( position, 6, column_name, null )) cname6,
        max(decode( position, 7, column_name, null )) cname7,
        max(decode( position, 8, column_name, null )) cname8,
        count(*) col_cnt
    from
    (
        select
            substr(table_name,1,30) table_name,
            substr(constraint_name,1,30) constraint_name,
            substr(column_name,1,30) column_name,
            position
        from
            user_cons_columns
    ) a,user_constraints b
    where
        a.constraint_name = b.constraint_name
        and b.constraint_type = 'R'
        group by b.table_name, b.constraint_name
) cons
where
    col_cnt > ALL
        (
            select count(*)
            from user_ind_columns i
            where i.table_name = cons.table_name
            and i.column_name in (cname1, cname2, cname3, cname4,
                cname5, cname6, cname7, cname8 )
            and i.column_position <= cons.col_cnt
            group by i.index_name
        )
/

[管理脚本] 闪回目录空间

select
    name,
    round(space_limit/1024/1024/1024,2) "LIMIT(G)",
    round(space_used/1024/1024/1024,2) "USED(G)",
    round(space_reclaimable/1024/1024,2) "RECL(M)",
    number_of_files
from
    v$recovery_file_dest
/

[管理脚本] 主外键关系查询

1 指定表约束查询

column owner format a10
column table_name format a20
column column_name format a20
column constraint_name format a20
column constraint_type format a20

select
    a.owner,
    a.table_name,
    b.column_name,
    a.constraint_name,
    a.constraint_type
from
    user_constraints a,
    user_cons_columns b
where
    a.owner = b.owner and
    a.table_name = b.table_name and
    a.table_name = upper('&tbl')
/

------------------------------------------------------------

2 主外键关系树形查询 (所有表或指定父表)

column lv format 999
column table_relation_tree format a70

with temp_constraints as
(
    select
        table_name,
        constraint_name pkey_constraint,
        null fkey_constraint,
        null r_constraint_name
    from user_constraints
    where
        constraint_type = 'P' or constraint_type = 'U'
    union all
    select
        a.table_name,
        a.constraint_name pkey_constraint,
        b.constraint_name fkey_constraint,
        b.r_constraint_name
    from
        user_constraints a,
        user_constraints b
    where
        a.table_name = b.table_name and
        (a.constraint_type = 'P' or a.constraint_type = 'U') and
        b.constraint_type = 'R'
)
select
    (level - 1) lv, rpad('-', (level-1)*5, '-') || table_name table_relation_tree
from temp_constraints
start with
    fkey_constraint is null and table_name = upper('&p_tbl')
connect by
    pkey_constraint <> r_constraint_name and
    prior pkey_constraint = r_constraint_name
order by 1, 2
/

------------------------------------------------------------

3 主键外键层次关系查询

column fk format a40
column pk format a40

select
    uc.constraint_name||' ('||ucc1.TABLE_NAME||'.'||ucc1.column_name||')' fk,
    ucc2.constraint_name||' ('||ucc2.TABLE_NAME||'.'||ucc2.column_name||')' pk
from
    user_constraints uc,
    user_cons_columns ucc1,
    user_cons_columns ucc2
where
    uc.constraint_name = ucc1.constraint_name and
    uc.r_constraint_name = ucc2.constraint_name and
    ucc1.POSITION = ucc2.POSITION and
    uc.constraint_type = 'R'
order by ucc1.TABLE_NAME, uc.constraint_name
/

------------------------------------------------------------

4 主键外键层次关系查询

column parent format a30
column child format a30
column level format 99

with v as
(
    select
        parent_table.table_name parent,
        child_table.table_name child
    from
        user_tables parent_table,
        user_constraints parent_constraint,
        user_constraints child_constraint,
        user_tables child_table
    where
        parent_table.table_name = parent_constraint.table_name and
        parent_constraint.constraint_type IN( 'P', 'U' ) and
        child_constraint.r_constraint_name = parent_constraint.constraint_name and
        child_constraint.constraint_type   = 'R' and
        child_table.table_name = child_constraint.table_name and
        child_table.table_name != parent_table.table_name
)
select level, parent, child
from (
    select * from v
)
start with parent = upper('&p_tbl')
connect by prior child = parent
order by 1, 2, 3
/

------------------------------------------------------------

5 带有复合主外键关系的查询

create or replace function get_str(p_constraint_name varchar2)
    return varchar2
is
    l_column_name varchar2(4000);
begin
    for cur in
        (
            select column_name,position
            from user_cons_columns
            where constraint_name = p_constraint_name
            order by position
        ) loop
        if cur.position = 1 or cur.position is null then
            l_column_name := cur.column_name;
        else
            l_column_name := l_column_name||','||cur.column_name;
        end if;
    end loop;
return l_column_name;
end;
/

with v_pk as
(
    select distinct
        a.constraint_name pk_con,
        a.table_name pk_table,
        get_str(a.constraint_name) pk_col,
        a.owner pk_owner
    from
        user_constraints a,
        user_cons_columns b
    where
        (a.constraint_type = 'P' or a.constraint_type = 'U') and
        a.constraint_name = b.constraint_name and
        a.owner = b.owner
),
v_fk as
(
    select distinct
        c.constraint_name fk_con,
        c.table_name fk_table,
        get_str(c.constraint_name) fk_col,
        c.r_owner r_pk_owner,
        c.r_constraint_name r_pk_con,
        c.owner fk_owner
    from
        user_constraints c,
        user_cons_columns d
    where
        c.constraint_type = 'R' and
        c.constraint_name = d.constraint_name and
        c.owner = d.owner
)
select
    rpad(fk.fk_con,35,'.')||fk_table||'('||fk_col||')' fk_info,
    rpad(pk.pk_con,35,'.')||pk_table||'('||pk_col||')' pk_info
from
    (select * from v_pk) pk,
    (select * from v_fk) fk
where
    pk.pk_owner = fk.r_pk_owner and
    pk.pk_con=fk.r_pk_con
/