月归档：四月 2012

ORACLE 十进制与二进制互转函数

发表于 2012 年 4 月 12 日 由 惜分飞

十进制转换二进制

CREATE OR REPLACE FUNCTION NUMBER_TO_BIT(V_NUM NUMBER) 
RETURN VARCHAR IS V_RTN VARCHAR(8);--注意返回列长度
  V_N1  NUMBER;
  V_N2  NUMBER;
BEGIN
V_N1 := V_NUM;
    LOOP
      V_N2  := MOD(V_N1, 2);
      V_N1  := ABS(TRUNC(V_N1 / 2));
      V_RTN := TO_CHAR(V_N2) || V_RTN;
      EXIT WHEN V_N1 = 0;
    END LOOP;
--返回二进制长度
 SELECT lpad(V_RTN,8,0) 
    INTO   V_RTN
    FROM dual;
return V_RTN;
end;

SQL> select NUMBER_TO_BIT(208) from dual;

NUMBER_TO_BIT(208)
-----------------------------
11010000

二进制转换十进制

CREATE OR REPLACE FUNCTION BIT_TO_NUMBER(P_BIN IN VARCHAR2) RETURN NUMBER AS
  V_SQL    VARCHAR2(30000) := 'SELECT BIN_TO_NUM(';
  V_RETURN NUMBER;
BEGIN
  IF LENGTH(P_BIN) >= 256 THEN
    RAISE_APPLICATION_ERROR(-20001, 'INPUT BIN TOO LONG!');
  END IF;
  IF LTRIM(P_BIN, '01') IS NOT NULL THEN
    RAISE_APPLICATION_ERROR(-20002, 'INPUT STR IS NOT VALID BIN VALUE!');
  END IF;
  FOR I IN 1 .. LENGTH(P_BIN) LOOP
    V_SQL := V_SQL || SUBSTR(P_BIN, I, 1) || ',';
  END LOOP;
  V_SQL := RTRIM(V_SQL, ',') || ') FROM DUAL';
  EXECUTE IMMEDIATE V_SQL
    INTO V_RETURN;
  RETURN V_RETURN;
END;

SQL> SELECT BIT_TO_NUMBER('11010000') FROM DUAL;

BIT_TO_NUMBER('11010000')
-------------------------
                      208
发表在 Oracle 开发 | 评论关闭

ORACLE ROWID 分析

发表于 2012 年 4 月 12 日 由 惜分飞

ROWID知识补充
Rowid改为：OOOOOOFFFBBBBBBRRR，占用10个字节（32bit data_object_id +10bit rfile# +22bit block +16bit）。其中，O是对象ID，F是文件ID，B是块ID，R是行ID。

Rowid对应值对应10十进制值

A-Z <==> 0 - 25 (26)
a-z <==> 26 - 51 (26)
0-9 <==> 52 - 61 (10)
+/ <==> 62 - 63 (2)

模拟环境

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> create table t_xff (id number,name varchar2(100));

Table created.

SQL> insert into t_xff values(1,'www.xifenfei.com');

1 row created.

SQL> commit;

Commit complete.

SQL>  alter table t_xff move;

Table altered.

SQL> select rowid,a.* from t_xff a;

ROWID                      ID NAME
------------------ ---------- ------------------------------
AADye6AAEAAAtCcAAA          1 www.xifenfei.com

相关值计算

Data Object number = AADye6
File               = AAE
Block              = AAAtCc
ROW                = AAA

DATA_OBJECT_ID
AADye6
58*64^0+30*64^1+50*64^2+3*64^3+0*64^4+0*64^5
58+1920+204800+786432+0+0=993210

RFILE#
AAE
4*64^0+0*64^1+0*64^2
4+0+0=4

BLOCK
AAAtCc
28*64^0+2*64^1+45*64^2+0*64^3+0*64^4+0*64^5
28+128+184320+0+0+0=184476

验证结果

SQL> select object_id,data_object_id from dba_objects where object_name='T_XFF';

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
    993209         993210

SQL> select dbms_rowid.rowid_object('AADye6AAEAAAtCcAAA') data_object_id#,
  2         dbms_rowid.rowid_relative_fno('AADye6AAEAAAtCcAAA') rfile#,
  3         dbms_rowid.rowid_block_number('AADye6AAEAAAtCcAAA') block#,
  4     dbms_rowid.rowid_row_number('AADye6AAEAAAtCcAAA') row# from dual;  

DATA_OBJECT_ID#     RFILE#     BLOCK#       ROW#
--------------- ---------- ---------- ----------
         993210          4     184476          0

dump方式分析

SQL> select rowid,dump(rowid) from t_xff;

ROWID              DUMP(ROWID)
------------------ ------------------------------------------
AADye6AAEAAAtCcAAA Typ=69 Len=10: 0,15,39,186,1,2,208,156,0,0

DATA_OBJECT_ID
0,15,39,186
15*256*256+39*256+186=993210

RFILE#
1,2(取前10位)
000000001 00 =4

BLOCK
2,208,156(取后22位)
000010 11010000 10011100
2^17+208*256+156=184476
发表在 Oracle | 评论关闭

DB2远程登录配置

发表于 2012 年 4 月 12 日 由 惜分飞

1.服务器端操作

[db2inst2@xifenfei ~]$ netstat -nap |grep db2sysc
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 0.0.0.0:55554               0.0.0.0:*                   LISTEN      8072/db2sysc 0      
[db2inst2@xifenfei ~]$  db2 list db directory

 System Database Directory

 Number of entries in the directory = 1

Database 1 entry:

 Database alias                       = XIFENFEI
 Database name                        = XIFENFEI
 Local database directory             = /home/db2inst2/xifenfei
 Database release level               = c.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =

--配置监听协议
[db2inst2@xifenfei ~]$ db2set db2comm=tcpip

--配置实例端口
[db2inst2@xifenfei ~]$ db2 update dbm cfg using svcename 5000
DB20000I  The UPDATE DATABASE MANAGER CONFIGURATION command completed 
successfully.

--重启生效
[db2inst2@xifenfei ~]$ db2stop
04/02/2012 12:33:36     0   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.
[db2inst2@xifenfei ~]$ db2start
04/02/2012 12:33:41     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.
[db2inst2@xifenfei ~]$ netstat -nap |grep db2sysc
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 0.0.0.0:50000               0.0.0.0:*                   LISTEN      8462/db2sysc 0

2.客户端配置

C:\Windows\system32>db2 catalog tcpip node vm_xff remote 192.168.1.90 server 50000
DB21061E  未初始化命令行环境。

--DB21061E错误解决
C:\Windows\system32>db2cmd

--编目节点
C:\Windows\system32>db2 catalog tcpip node vm_xff remote 192.168.1.90 server 50000
DB20000I  CATALOG TCPIP NODE命令成功完成。
DB21056W  直到刷新目录高速缓存之后，目录更改才生效。

--编目数据库
C:\Windows\system32>db2 catalog db xifenfei as xifenfei at node vm_xff
DB20000I  CATALOG DATABASE命令成功完成。
DB21056W  直到刷新目录高速缓存之后，目录更改才生效。

C:\Windows\system32> db2 list node directory

 节点目录

 目录中的条目数 = 1

节点 1 条目：

 节点名                            = VM_XFF
 注释                            =
 目录条目类型                    = LOCAL
 协议                            = TCPIP
 主机名                          = 192.168.1.90
 服务名称             = 50000

--terminate使操作生效
C:\Windows\system32>db2 terminate
DB20000I  TERMINATE命令成功完成。

3.验证登陆

[db2inst2@xifenfei sqldbdir]$ db2 connect to xifenfei user db2inst2 using xifenfei

   Database Connection Information

 Database server        = DB2/LINUX 9.5.0
 SQL authorization ID   = DB2INST1
 Local database alias   = XIFENFEI

[db2inst1@xifenfei ~]$ db2 list applications

Auth Id  Application    Appl.      Application Id                                                 DB       # of
         Name           Handle                                                                    Name    Agents
-------- -------------- ---------- -------------------------------------------------------------- -------- -----
DB2INST2 db2bp.exe      45         192.168.1.1.38849.120411160214                                 XIFENFEI   1    
DB2INST2 db2bp          50         *LOCAL.db2inst1.120402072313                                   XIFENFEI   1
发表在 DB2 | 评论关闭