Result set rows can be fetched either a row at a time or in groups.
In the fetch stage, the database selects rows and, if requested by the query, orders the rows. Each successive fetch retrieves another row of the result until the last row has been fetched.
In general, the database cannot determine for certain the number of rows to be retrieved by a query until the last row is fetched. Oracle Database retrieves the data in response to fetch calls, so that the more rows the database reads, the more work it performs. For some queries the database returns the first row as quickly as possible, whereas for others it creates the entire result set before returning the first row.
DML statements that must change data use read consistency to retrieve only the data that matched the search criteria when the modification began.
Afterward, these statements retrieve the data blocks as they exist in their current state and make the required modifications. The database must perform other actions related to the modification of the data such as generating redo and undo data.
DML Operation:
SQL> CREATE TABLE SAMAD.TBLDML_TEST AS SELECT * FROM DBA_OBJECTS WHERE ROWNUM <= 5000;
Table created.
SQL> SELECT COUNT(*) FROM SAMAD.TBLDML_TEST;
COUNT(*)
----------
5000
SQL> SELECT EXTENT_ID, BLOCK_ID, BLOCKS FROM DBA_EXTENTS
WHERE SEGMENT_NAME = 'TBLDML_TEST';
EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ----------
0 136 8
1 144 8
2 152 8
3 160 8
4 168 8
5 176 8
6 184 8
7 192 8
8 200 8
9 208 8
10 216 8
11 224 8
12 rows selected.
SQL> SELECT DATA_OBJECT_ID, OBJECT_ID FROM DBA_OBJECTS
WHERE OBJECT_NAME = 'TBLDML_TEST';
DATA_OBJECT_ID OBJECT_ID
-------------- ----------
73020 73020
SQL> SELECT MIN(OBJECT_ID), MAX(OBJECT_ID) FROM SAMAD.TBLDML_TEST;
MIN(OBJECT_ID) MAX(OBJECT_ID)
-------------- --------------
2 5006
SQL> SELECT OBJECT_ID, DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE#,
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK#
FROM SAMAD.TBLDML_TEST WHERE OBJECT_ID IN (2,5000);
OBJECT_ID FILE# BLOCK#
---------- ---------- ----------
2 12 139
5000 12 229Insert a Record into a Table:
SQL> @obj_buffer.sql
Enter value for objid: 73020
old 21: WHERE OBJD= &OBJID
new 21: WHERE OBJD= 73020
FILE# BLOCK# CLASS TYPE STATUS LOCK_ELEMENT_ADD
---------- ---------- ------------------ ---------- ----------------
12 136 1ST LEVEL BMB xcur 00
12 137 2ND LEVEL BMB xcur 00
12 138 SEGMENT HEADER xcur 00
12 139 DATA BLOCK xcur 00
12 140 DATA BLOCK xcur 00
12 141 DATA BLOCK xcur 00
12 142 DATA BLOCK xcur 00
12 143 DATA BLOCK xcur 00
12 144 DATA BLOCK xcur 00
12 145 DATA BLOCK xcur 00
12 146 DATA BLOCK xcur 00
... ... ...
12 220 DATA BLOCK xcur 00
12 221 DATA BLOCK xcur 00
12 222 DATA BLOCK xcur 00
12 223 DATA BLOCK xcur 00
12 224 DATA BLOCK xcur 00
12 225 DATA BLOCK xcur 00
12 226 DATA BLOCK xcur 00
12 227 DATA BLOCK xcur 00
12 228 DATA BLOCK xcur 00
12 229 DATA BLOCK xcur 00
94 rows selected.SQL> conn / as sysdba
Connected.
SQL> startup force
ORACLE instance started.
Total System Global Area 1560278096 bytes
Fixed Size 9135184 bytes
Variable Size 1006632960 bytes
Database Buffers 536870912 bytes
Redo Buffers 7639040 bytes
Database mounted.
Database opened.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 MYPDB MOUNTED
SQL> alter pluggable database MYPDB open;
Pluggable database altered.
SQL> alter session set container = MYPDB;
Session altered.
SQL> @obj_buffer.sql
Enter value for objid: 73020
old 21: WHERE OBJD= &OBJID
new 21: WHERE OBJD= 73020
no rows selected
SQL> INSERT INTO SAMAD.TBLDML_TEST (SELECT * FROM DBA_OBJECTS WHERE OBJECT_ID = 5010);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> @obj_buffer.sql
Enter value for objid: 73020
old 21: WHERE OBJD= &OBJID
new 21: WHERE OBJD= 73020
FILE# BLOCK# CLASS TYPE STATUS LOCK_ELEMENT_ADD
---------- ---------- ------------------ ---------- ----------------
12 137 2ND LEVEL BMB xcur 00
12 138 SEGMENT HEADER xcur 00
12 216 1ST LEVEL BMB xcur 00
12 230 DATA BLOCK xcur 00
12 231 DATA BLOCK xcur 00
SQL> SELECT OBJECT_ID, DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE#,
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK#
FROM SAMAD.TBLDML_TEST WHERE OBJECT_ID IN (2, 5000, 5010);
OBJECT_ID FILE# BLOCK#
---------- ---------- ----------
2 12 139
5000 12 229
5010 12 230SQL> INSERT INTO SAMAD.TBLDML_TEST (SELECT * FROM DBA_OBJECTS WHERE OBJECT_ID = 5020);
1 row created.
SQL> SELECT OBJECT_ID, DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE#,
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK#
FROM SAMAD.TBLDML_TEST WHERE OBJECT_ID IN (2, 5000, 5010, 5020);
OBJECT_ID FILE# BLOCK#
---------- ---------- ----------
2 12 139
5000 12 229
5010 12 230
5020 12 230
SQL> rollback;
Rollback complete.
SQL> SELECT OBJECT_ID, DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE#,
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK#
FROM SAMAD.TBLDML_TEST WHERE OBJECT_ID IN (2, 5000, 5010, 5020); 2 3
OBJECT_ID FILE# BLOCK#
---------- ---------- ----------
2 12 139
5000 12 229
5010 12 230Update a Record on Table:
SQL> UPDATE SAMAD.TBLDML_TEST SET OBJECT_NAME='UPDATE' WHERE OBJECT_ID = 1000;
1 row updated.
SQL> @obj_buffer.sql
Enter value for objid: 73020
old 21: WHERE OBJD= &OBJID
new 21: WHERE OBJD= 73020
FILE# BLOCK# CLASS TYPE STATUS LOCK_ELEMENT_ADD
---------- ---------- ------------------ ---------- ----------------
12 138 SEGMENT HEADER xcur 00
12 139 DATA BLOCK xcur 00
12 140 DATA BLOCK xcur 00
12 141 DATA BLOCK xcur 00
12 142 DATA BLOCK xcur 00
12 143 DATA BLOCK xcur 00
12 144 DATA BLOCK xcur 00
12 145 DATA BLOCK xcur 00
12 146 DATA BLOCK xcur 00
12 147 DATA BLOCK xcur 00
12 148 DATA BLOCK xcur 00
12 149 DATA BLOCK xcur 00
12 150 DATA BLOCK xcur 00
12 151 DATA BLOCK xcur 00
12 153 DATA BLOCK xcur 00
12 154 DATA BLOCK xcur 00
12 155 DATA BLOCK xcur 00
12 156 DATA BLOCK xcur 00
12 156 DATA BLOCK cr 00
12 157 DATA BLOCK xcur 00
12 158 DATA BLOCK xcur 00
12 159 DATA BLOCK xcur 00
... ... ...
12 220 DATA BLOCK xcur 00
12 221 DATA BLOCK xcur 00
12 222 DATA BLOCK xcur 00
12 223 DATA BLOCK xcur 00
12 224 DATA BLOCK xcur 00
12 225 DATA BLOCK xcur 00
12 226 DATA BLOCK xcur 00
12 227 DATA BLOCK xcur 00
12 228 DATA BLOCK xcur 00
12 229 DATA BLOCK xcur 00
12 230 DATA BLOCK xcur 00
12 231 DATA BLOCK xcur 00
90 rows selected.Delete a Record from Table:
SQL> SELECT OBJECT_ID, DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE#,
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK#
FROM SAMAD.TBLDML_TEST WHERE OBJECT_ID IN (2000);
OBJECT_ID FILE# BLOCK#
---------- ---------- ----------
2000 12 173
SQL> DELETE FROM SAMAD.TBLDML_TEST WHERE OBJECT_ID = 2000;
1 row deleted.
SQL> commit;
Commit complete.
Delete and Insert:
After Deleting records from table, all empty PCTUSED from these blocks are first candidate to next insert.
SQL> SELECT OBJECT_ID, DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE#,
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK#
FROM SAMAD.TBLDML_TEST WHERE OBJECT_ID BETWEEN 2 AND 100
ORDER BY OBJECT_ID;
OBJECT_ID FILE# BLOCK#
---------- ---------- ----------
2 12 139
3 12 139
4 12 139
5 12 139
6 12 139
7 12 139
8 12 139
9 12 139
10 12 139
11 12 139
12 12 139
... ... ...
68 12 139
69 12 139
70 12 140
... ... ...
100 12 140
99 rows selected.
SQL> DELETE FROM SAMAD.TBLDML_TEST WHERE OBJECT_ID BETWEEN 2 AND 100;
99 rows deleted.
SQL> COMMIT;
Commit complete.
SQL> SELECT OBJECT_ID, DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE#,
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK#
FROM SAMAD.TBLDML_TEST WHERE OBJECT_ID BETWEEN 2 AND 100
ORDER BY OBJECT_ID; 2 3 4
no rows selected
SQL> INSERT INTO SAMAD.TBLDML_TEST (SELECT * FROM DBA_OBJECTS WHERE OBJECT_ID BETWEEN 2 AND 100);
99 rows created.
SQL> COMMIT;
SQL> SELECT OBJECT_ID, DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE#,
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK#
FROM SAMAD.TBLDML_TEST WHERE OBJECT_ID BETWEEN 2 AND 100
ORDER BY OBJECT_ID;
OBJECT_ID FILE# BLOCK#
---------- ---------- ----------
2 12 139
3 12 139
4 12 139
5 12 139
6 12 139
7 12 139
8 12 139
9 12 139
10 12 139
11 12 139
12 12 139
... ... ...
68 12 139
69 12 139
70 12 140
... ... ...
100 12 140
99 rows selected.Scripts:
[oracle@ol71ora193 ~]$ cat obj_buffer.sql
SELECT FILE#, BLOCK#, DECODE(CLASS#,
1, 'DATA BLOCK',
2, 'SORT BLOCK',
3, 'SAVE UNDO BLOCK',
4, 'SEGMENT HEADER',
5, 'SAVE UNDO HEADER',
6, 'FREE LIST',
7, 'EXTENT MAP',
8, '1ST LEVEL BMB',
9, '2ND LEVEL BMB',
10, '3RD LEVEL BMB',
11, 'BITMAP BLOCK',
12, 'BITMAP INDEX BLOCK',
13, 'FILE HEADER BLOCK',
14, 'UNUSED',
15, 'SYSTEM UNDO HEADER',
16, 'SYSTEM UNDO BLOCK',
17, 'UNDO HEADER',
18, 'UNDO BLOCK') "CLASS TYPE", STATUS, LOCK_ELEMENT_ADDR
FROM V$BH
WHERE OBJD= &OBJID
ORDER BY 1,2,3
/
