分类目录归档：数据库

Oracle中connect by…start with…的使用

发表于 2011 年 10 月 28 日 由 惜分飞

一、语法

大致写法：select * from some_table [where 条件1] connect by [条件2] start with [条件3];
其中 connect by 与 start with 语句摆放的先后顺序不影响查询的结果，[where 条件1]可以不需要。
[where 条件1]、[条件2]、[条件3]各自作用的范围都不相同：

[where 条件1]是在根据“connect by [条件2] start with [条件3]”选择出来的记录中进行过滤，是针对单条记录的过滤， 不会考虑树的结构；

[条件2]指定构造树的条件，以及对树分支的过滤条件，在这里执行的过滤会把符合条件的记录及其下的所有子节点都过滤掉；

[条件3]限定作为搜索起始点的条件，如果是自上而下的搜索则是限定作为根节点的条件，如果是自下而上的搜索则是限定作为叶子节点的条件；

示例：
假如有如下结构的表：some_table(id,p_id,name)，其中p_id保存父记录的id。
select * from some_table t where t.id!=123 connect by prior t.p_id=t.id and t.p_id!=321 start with t.p_id=33 or t.p_id=66;

对prior的说明：
prior存在于[条件2]中，可以不要，不要的时候只能查找到符合“start with [条件3]”的记录，不会在寻找这些记录的子节点。要的时候有两种写法：connect by prior t.p_id=t.id 或 connect by t.p_id=prior t.id，前一种写法表示采用自上而下的搜索方式（先找父节点然后找子节点），后一种写法表示采用自下而上的搜索方式（先找叶子节点然后找父节点）。

二、执行原理
connect by…start with…的执行原理可以用以下一段程序的执行以及对存储过程RECURSE()的调用来说明：

/* 遍历表中的每条记录，对比是否满足start with后的条件，如果不满足则继续下一条，
如果满足则以该记录为根节点，然后调用RECURSE()递归寻找该节点下的子节点，
如此循环直到遍历完整个表的所有记录 。*/
for rec in (select * from some_table) loop
if FULLFILLS_START_WITH_CONDITION(rec) then
RECURSE(rec, rec.child);
end if;
end loop;

/* 寻找子节点的存储过程*/
procedure RECURSE (rec in MATCHES_SELECT_STMT, new_parent IN field_type) is
begin
APPEND_RESULT_LIST(rec); /*把记录加入结果集合中*/
/*再次遍历表中的所有记录，对比是否满足connect by后的条件，如果不满足则继续下一条，
如果满足则再以该记录为根节点，然后调用RECURSE()继续递归寻找该节点下的子节点，
如此循环直到找至叶子节点。*/
for rec_recurse in (select * from some_table) loop
if FULLFILLS_CONNECT_BY_CONDITION(rec_recurse.child, new_parent) then
RECURSE(rec_recurse,rec_recurse.child);
end if;
end loop;
end procedure RECURSE;

三、使用探讨
从上面的执行原理可以看到“connect by…start with…”构造树的方式是：（1）如果是自上而下方式，则把表中的每一条记录都作为根节点来生成树，所以表中有多少条记录就会构造出多少棵树。（2）如果是自下而上的搜索方式，则把表中的每一条记录都作为叶子节点来生成分支，所以表中有多少条记录就会生成多少条分支。
因此如果表中的记录不是严格遵照每条记录都只能有一个父记录的原则，那么就可能有部分记录会存在于多棵树中，那么在查找记录的时候就可能会出现找到多条重复记录的异常情况。

来源：http://hi.baidu.com/haydo/blog/item/069298438e5c6d1073f05d46.html

发表在 Oracle 开发 | 评论关闭

ggsci: error while loading shared libraries

发表于 2011 年 10 月 24 日 由 惜分飞

在部署goldengate过程中发现如下错误：
[oracle@localhost OGG]$ ggsci
ggsci: error while loading shared libraries: /opt/oracle/product/10.2.0/db_1/lib/libclntsh.so.10.1: cannot restore segment prot after reloc: Permission denied

查找资料，没有专门说ogg安装这个错误，是SELinux启用导致，但是有很多其他程序执行过程中报类此错误是有此导致，那么我抱着尝试的态度实验看看：
1、查看SELinux是否被关闭
[oracle@localhost tmp]$ more /etc/selinux/config
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
# enforcing – SELinux security policy is enforced.
# permissive – SELinux prints warnings instead of enforcing.
# disabled – SELinux is fully disabled.
SELINUX=disabled
# SELINUXTYPE= type of policy in use. Possible values are:
# targeted – Only targeted network daemons are protected.
# strict – Full SELinux protection.
SELINUXTYPE=targeted
说明已经关闭。那就奇怪了，既然已经闭关了那为什么还不行？于是我怀疑，是不是有人只是修改了SELINUX=disabled，没有重启系统或者使用命令使其生效导致。

2、查看SELINUX修改是否生效
[root@localhost ~]# getenforce
Enforcing
果然修改没有生效

3、使SELINUX生效
[root@localhost ~]# setenforce 0
再次查询，现在已经生效
[root@localhost ~]# getenforce
Permissive

4、然后启动ggsci
[oracle@localhost ~]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1.1 OGGCORE_11.1.1.1.1_PLATFORMS_110729.1700
Linux, x64, 64bit (optimized), Oracle 10g on Jul 29 2011 19:43:29

Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.

发表在 GoldenGate | 评论关闭

数据文件重命名

发表于 2011 年 10 月 21 日 由 惜分飞

最近一段时间，发现不少pub上不少新手都因为一时大意，添加数据文件名称不规范，然后想重命名该数据文件（或者想删除该数据文件然后重建），处理思路有些不妥，导致一些悲剧的发现，我这里通过实验提供一个自认为比较合理的处理思路：处理思路是数据文件离线重命名

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /opt/oracle/oradata/test/archivelog
Oldest online log sequence     210
Next log sequence to archive   212
Current log sequence           212
--确认数据库是归档模式，使得数据库离线后，可以有归档日志恢复到在线状态

SQL> col name for a50
SQL> select name,file# from v$datafile;

NAME                                                    FILE#
-------------------------------------------------- ----------
/opt/oracle/oradata/test/system01.dbf                       1
/opt/oracle/oradata/test/undotbs01.dbf                      2
/opt/oracle/oradata/test/sysaux01.dbf                       3
/opt/oracle/oradata/test/users01.dbf                        4
/opt/oracle/oradata/test/user32g.dbf                        5
/opt/oracle/oradata/test/xifenfei01.dbf                     6
/opt/oracle/oradata/test/user02.dbf                         7
/opt/oracle/oradata/test/odu02.dbf                          8
/opt/oracle/oradata/test/odu01.dbf                          9
/opt/oracle/oradata/test/odu03.dbf                         10

10 rows selected.

SQL> alter tablespace xff add datafile '/opt/oracle/oradata/test/xifenfei02.chf'
   2    size 10m autoextend off;

Tablespace altered.

SQL>  select name,file# from v$datafile;

NAME                                                    FILE#
-------------------------------------------------- ----------
/opt/oracle/oradata/test/system01.dbf                       1
/opt/oracle/oradata/test/undotbs01.dbf                      2
/opt/oracle/oradata/test/sysaux01.dbf                       3
/opt/oracle/oradata/test/users01.dbf                        4
/opt/oracle/oradata/test/user32g.dbf                        5
/opt/oracle/oradata/test/xifenfei01.dbf                     6
/opt/oracle/oradata/test/user02.dbf                         7
/opt/oracle/oradata/test/odu02.dbf                          8
/opt/oracle/oradata/test/odu01.dbf                          9
/opt/oracle/oradata/test/odu03.dbf                         10
/opt/oracle/oradata/test/xifenfei02.chf                    11

11 rows selected.

SQL> create table chf.xff_test tablespace xff 
  2  as 
  3  select * from dba_objects;
select * from dba_objects
              *
ERROR at line 3:
ORA-01536: space quota exceeded for tablespace 'XFF'


SQL> alter user chf quota 100m on xff;

User altered.


SQL> create table chf.xff_test tablespace xff 
  2  as
  3  select * from dba_objects;

Table created.
--需要重命名的数据文件内有数据，模拟数据库在生产环境中工作

SQL> alter database datafile 11 offline drop ;

Database altered.
--数据文件离线处理

SQL> !mv /opt/oracle/oradata/test/xifenfei02.chf /opt/oracle/oradata/test/xifenfei02.dbf
--系统级别把数据文件修改为正确名称

SQL> alter database rename file '/opt/oracle/oradata/test/xifenfei02.chf' 
     2  to '/opt/oracle/oradata/test/xifenfei02.dbf';

Database altered.
--修改控制文件中数据文件名称

SQL> recover datafile 11;
Media recovery complete.
SQL> alter database datafile 11 online;

Database altered.
--恢复数据文件，并使其online

SQL>  select name,file# from v$datafile;

NAME                                                    FILE#
-------------------------------------------------- ----------
/opt/oracle/oradata/test/system01.dbf                       1
/opt/oracle/oradata/test/undotbs01.dbf                      2
/opt/oracle/oradata/test/sysaux01.dbf                       3
/opt/oracle/oradata/test/users01.dbf                        4
/opt/oracle/oradata/test/user32g.dbf                        5
/opt/oracle/oradata/test/xifenfei01.dbf                     6
/opt/oracle/oradata/test/user02.dbf                         7
/opt/oracle/oradata/test/odu02.dbf                          8
/opt/oracle/oradata/test/odu01.dbf                          9
/opt/oracle/oradata/test/odu03.dbf                         10
/opt/oracle/oradata/test/xifenfei02.dbf                    11

11 rows selected.

如果数据库满足以下条件，可以删除数据文件，重新添加：
1、The database must be open.
2、If a datafile is not empty, it cannot be dropped. If you must remove a datafile that is not empty and that cannot be made empty by dropping schema objects, you must drop the tablespace that contains the datafile.
3、You cannot drop the first or only datafile in a tablespace. This means that DROP DATAFILE cannot be used with a bigfile tablespace.
4、You cannot drop datafiles in a read-only tablespace.
5、You cannot drop datafiles in the SYSTEM tablespace.
6、If a datafile in a locally managed tablespace is offline, it cannot be dropped.
7、db version >= 10g R2

SQL> alter tablespace xff drop datafile 11;
alter tablespace xff drop datafile 11
*
ERROR at line 1:
ORA-03262: the file is non-empty

SQL> alter tablespace xff add datafile '/opt/oracle/oradata/test/xifenfei03.chf' size 10m autoextend off;

Tablespace altered.

SQL> alter tablespace xff drop datafile '/opt/oracle/oradata/test/xifenfei03.chf';

Tablespace altered.
发表在 Oracle | 一条评论