Oracle PL/SQL/System Packages/dbms rowid

DBMS_ROWID.ROWID_BLOCK_NUMBER(RowID)

 
SQL> select DBMS_ROWID.ROWID_BLOCK_NUMBER(RowID)
  2    from dual
  3   where RowNum=1;
                                                              
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
                                2082
SQL>

dbms_rowid.rowid_block_number(rowid) and ora_rowscn

 
SQL>
SQL> CREATE TABLE DEPT(
  2      DEPTNO NUMBER(2),
  3      DNAME VARCHAR2(14),
  4      LOC VARCHAR2(13)
  5  );
Table created.
SQL>
SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK");
1 row created.
SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS");
1 row created.
SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO");
1 row created.
SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON");
1 row created.
SQL>
SQL>
SQL>
SQL> update dept
  2     set dname = lower(dname)
  3   where deptno = 10;
1 row updated.
SQL>
SQL> select deptno, dname,
  2         dbms_rowid.rowid_block_number(rowid) blockno,ora_rowscn
  3    from dept;
    DEPTNO DNAME             BLOCKNO ORA_ROWSCN
---------- -------------- ---------- ----------
        10 accounting          42706    1295679
        20 RESEARCH            42706    1295679
        30 SALES               42706    1295679
        40 OPERATIONS          42706    1295679
SQL> drop table dept;
Table dropped.
SQL>

DBMS_ROWID.ROWID_RELATIVE_FNO(RowID)

 

SQL> select DBMS_ROWID.ROWID_RELATIVE_FNO(RowID)
  2    from dual
  3   where RowNum=1;
                                                              
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
------------------------------------
                                   1

DBMS_ROWID.ROWID_RELATIVE_FNO(RowID),DBMS_ROWID.ROWID_BLOCK_NUMBER(RowID)

 
SQL> select DBMS_ROWID.ROWID_RELATIVE_FNO(RowID),
  2         DBMS_ROWID.ROWID_BLOCK_NUMBER(RowID)
  3    from dual
  4   where RowNum=1;
                                                              
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)  DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------  ------------------------------------
                                   1                                  2082

DBMS_ROWID.ROWID_ROW_NUMBER(RowID)

 

SQL> select DBMS_ROWID.ROWID_ROW_NUMBER(RowID)
  2    from dual;
                                                              
DBMS_ROWID.ROWID_ROW_NUMBER(ROWID)
----------------------------------
                                 0
SQL>
SQL>

DBMS_ROWID.ROWID_TO_RESTRICTED(RowID,0)

 
SQL> select DBMS_ROWID.ROWID_TO_RESTRICTED(RowID,0)
  2    from dual;
                                                              
DBMS_ROWID.ROWID_T
------------------
00000822.0000.0001

where rowid = dbms_rowid.rowid_create(1,49627,7,138,0)

 
SQL>
SQL> CREATE TABLE EMP(
  2      EMPNO NUMBER(4) NOT NULL,
  3      ENAME VARCHAR2(10),
  4      JOB VARCHAR2(9),
  5      MGR NUMBER(4),
  6      HIREDATE DATE,
  7      SAL NUMBER(7, 2),
  8      COMM NUMBER(7, 2),
  9      DEPTNO NUMBER(2)
 10  );
Table created.
SQL> update emp
  2   set sal = sal * 1.1
  3   where hiredate < to_date("08/SEP/1981","DD/MON/YYYY");
0 rows updated.

SQL>
SQL> select *
  2   from emp
  3   where rowid = dbms_rowid.rowid_create(1,49627,7,138,0);
SQL>
SQL> drop table emp;
Table dropped.