Oracle Magazine - July/August 2012

topic you are not up to speed on, or if you just want to see some really interesting SQL injection techniques, I encourage you to check out bit.ly/IgU3YQ and bit.ly/K7aAKW.

After I build the query in a string, I use a ref cursor to open a cursor that can be sent back to a client application, as shown in Listing 3.

FINDING THE LAST ROW

I have created a view based on multiple tables in Oracle Database, and I am able to fetch the records within a view via SELECT. My question is: If there are newly added records and I want to fetch only the newly added records inside the view, what is the SQL query for fetching those? I tried using the rowid, but it didn’t return the results as expected.

You would have to tell me how you could identify “new rows” if you printed them out on a piece of paper. If you cannot tell which rows are “new” or what the “last row” was, neither can I. Rowids are an address of a row on a block in a file—they are not monotonically increasing values. Even if you only insert into a table, you will find that the rows might not be in “insert order”—sometimes they might be, but other times they won’t. In short, you can never count on their being in any sort of order. For example, I used an automatic segment space managed (ASSM) tablespace with an 8 K block size and inserted some data, as shown in Listing 4.

Now, arguably row “ 4” is the “last” and newest row in the T table, but if I start looking at the rows—and what database blocks they are on—I’ll see a different story, as shown in Listing 5.

If I were to try to use ROWID to find the “last” row or the newest row, I’d be very much let down at this point. What I did was put a small row 1 on the first block and place a larger row 2 on the same first block, but then when I tried to insert row 3, it was too big to fit on the first block with those rows, so it went to the second block in the table. However, when row 4 came along—it was small again—there was room for it on the first block.

Rows go on a block that has free space on
it—enough free space to hold those rows.
They do not go to the “last block” in the
table; if they did, tables would only grow.
We would never be able to reuse space left
behind after a delete.

REDO AND GLOBAL TEMPORARY TABLES

Do temporary tables generate redo for standard data manipulation language] operations?

I think that because every DML operation generates undo, every DML operation on global temporary tables will generate redo.

In short, there is no redo generated on temporary table blocks. However, any undo generated for those blocks will have redo generated. Thus, many operations against global temporary tables will generate redo as

Code Listing 4: Creating table for “last row” test

SQL> create table t ( x int, y varchar2(4000), z varchar2(4000) ); Table created.

SQL> insert into t values ( 1, rpad('*', 1,'*'), rpad('*', 1,'*') ); 1 row created.

SQL> connect / Connected.

SQL> insert into t values ( 2, rpad('*',3000,'*'), rpad('*',3000,'*') ); 1 row created.

SQL> connect / Connected.

SQL> insert into t values ( 3, rpad('*',3000,'*'), rpad('*',3000,'*') ); 1 row created.

SQL> connect / Connected.

SQL> insert into t values ( 4, rpad('*', 1,'*'), rpad('*', 1,'*') ); 1 row created.

Code Listing 5: Query to see order of rowids

SQL> connect / Connected.

SQL> select dbms_rowid.rowid_block_number(rowid), x from t;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) X ————————————————————————————————————————————————————— —————————————— SQL> select rowid, x from t order by rowid desc;