COMMISSION_PCT NUMBER 9 10 10 NO 0
MANAGER_ID NUMBER 10 11 11 NO 0
DEPARTMENT_ID NUMBER 11 12 12 NO 0
/* Decrease the ORA_ARCHIVE_STATE column size to improve formatting in queries */
COLUMN ORA_ARCHIVE_STATE FORMAT a18;
/* The default value for ORA_ARCHIVE_STATE is '0', which means active */
SELECT employee_id, ORA_ARCHIVE_STATE FROM employees_indbarch;
EMPLOYEE_ID ORA_ARCHIVE_STATE
----------- ------------------
251 0
252 0
/* Insert a value into ORA_ARCHIVE_STATE to set inactive */
UPDATE employees_indbarch SET ORA_ARCHIVE_STATE = '20' WHERE employee_id = 252;
/* Only active records are in the following query */
SELECT employee_id, ORA_ARCHIVE_STATE FROM employees_indbarch;
EMPLOYEE_ID ORA_ARCHIVE_STATE
----------- ------------------
251 0
/* Set visibility to ALL to display all records */
ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;
SELECT employee_id, ORA_ARCHIVE_STATE FROM employees_indbarch;
EMPLOYEE_ID ORA_ARCHIVE_STATE
----------- ------------------
251 0
252 20
SQL> desc employees_indbarch
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
1* select COLUMN_NAME,COLUMN_ID,DATA_TYPE from dba_tab_cols where table_name=upper('employees_indbarch')
SQL> /
COLUMN_NAME COLUMN_ID DATA_TYPE
-------------------- --------- --------------------
DEPARTMENT_ID 11 NUMBER
MANAGER_ID 10 NUMBER
COMMISSION_PCT 9 NUMBER
SALARY 8 NUMBER
JOB_ID 7 VARCHAR2
HIRE_DATE 6 DATE
PHONE_NUMBER 5 VARCHAR2
EMAIL 4 VARCHAR2
LAST_NAME 3 VARCHAR2
FIRST_NAME 2 VARCHAR2
EMPLOYEE_ID 1 NUMBER
ORA_ARCHIVE_STATE VARCHAR2
SQL> select dump(ORA_ARCHIVE_STATE,16),dump(EMPLOYEE_ID,16),dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from employees_indbarch;
DUMP(ORA_ARCHIVE_STA DUMP(EMPLOYEE_ID,16) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
-------------------- -------------------- ------------------------------------ ------------------------------------