On Oracle Database 12c, Part 5
Our technologist explores the invisible, improves introspection, and expands SQL.
By Tom Kyte
May/June 2014
Usually I take three or four user-submitted questions from the past two months and answer them here in each Ask Tom column. In the last four and in this column, however, I’ve taken a look at some key Oracle Database 12c features. These features are all part of the “12 Things About Oracle Database 12c” or “12 More Things About Oracle Database 12c” presentations I gave at Oracle OpenWorld 2012 and 2013 in San Francisco. (You can find the slides for those presentations on asktom.oracle.com on the Files tab). The first three Oracle Database 12c features I looked at were improved defaults, bigger datatypes, and top-n queries. Next I discussed a new row-pattern-matching clause and how undo for temporary tables has changed in Oracle Database 12c. Then I covered some partitioning improvements, adaptive execution plans, and enhanced statistics. In the last issue, I took a different approach to discuss the #1 new capability of Oracle Database 12c—the Oracle Multitenant option.
In this column, the last of the Oracle Database 12c new features series, I look at invisible columns, SQL text expansion, and improved introspection.
Invisible ColumnsInvisible columns in Oracle Database 12c provide the ability to add a column to any table such that the newly added column will not be visible in a SELECT * query and will not be considered for insertion in an INSERT statement that does not explicitly list that column. In other words, invisible columns enable you to add a column to a table and not affect poorly coded applications that use SELECT * or perform inserts without listing the columns.
To demonstrate, I start with a two-column table:
SQL> create table t 2 ( x int, 3 y int 4 ) 5 / Table created. SQL> insert into t values ( 1, 2 ); 1 row created.
Next I assume the existence of some application code (waiting to be fixed!) that includes a SELECT * FROM T. Even worse, I assume that the application code includes INSERT INTO T VALUES (:x,:y)—an insert that does not specify the columns it is inserting into. (I consider both of these conditions to be bugs in the developed application code, and they need to be fixed, but you might need to add this column right now and get back to the buggy code later.) And I’d like to be able to add a new column without affecting this code. Enter the invisible column.
I can add a new column, Z, using the INVISIBLE attribute:
SQL> alter table t add
( z int INVISIBLE );
Table altered.
If I were to describe this table in SQL*Plus or many other tools, I would see the table without this new column:
SQL> desc t Name Null? Type ————— ———————— —————————— X NUMBER(38) Y NUMBER(38)
This is the default behavior of SQL*Plus, but, by using the SET COLINVISIBLE setting, I can direct it to show the invisible columns:
SQL> set colinvisible on SQL> desc t Name Null? Type ————————————— ———————— —————————— X NUMBER(38) Y NUMBER(38) Z (INVISIBLE) NUMBER(38)
Now because this column is invisible, it will not be selected via a SELECT *:
SQL> select * from t;
X Y
———————————— ———————————
1 2
3 4
Nor will it affect existing inserts that do not include an explicit list of column names:
SQL> insert into t values ( 3, 4 ); 1 row created.
For backward compatibility, this new capability is a bonus. New applications that need to access this column have full access to it:
SQL> insert into t (x,y,z)
2 values ( 5,6,7 );
1 row created.
SQL> select x,y,z from t;
X Y Z
——————————— ———————— ——————————
1 2
3 4
5 6 7
Those new applications only need to explicitly reference column Z, which is the right approach (because, again, using SELECT * and INSERT without a column list should be considered a bug—a bug that needs to be fixed).
Ultimately, after the legacy code has been corrected, you will want to make this column visible:
SQL> alter table t modify z visible;
Table altered.
SQL> select * from t;
X Y Z
——————————— ———————— ——————————
1 2
3 4
5 6 7
A positive side effect of using invisible columns is the ability to logically reorder the columns in a table. I stress that this will be a logical, not a physical, reordering of the columns.
To demonstrate this, I add a new column to the middle of a table. Physically—on disk—this newly added column will be the last column, and if I dump the database block to a trace file, it will appear last. But logically—from the end user perspective—this newly added column will appear as if it were in the middle of the table. It will be visible via a DESCRIBE command in SQL*Plus, the results of a SELECT *, and so on.
To demonstrate logical reordering of columns, I first create a new table:
SQL> create table t 2 ( a int, 3 b int, 4 d int, 5 e int 6 ); Table created.
Now suppose that as part of an application upgrade, I need to add a new column, C, and meet a request to have C appear in the middle of the table. This might be desirable for certain ad hoc reporting tools, which tend to display the columns by default in the order a SELECT * would return them. In the past, this reordering was not really possible, but I could
- Rename the table to something else, say T_TBL
- Add to this table the new column, which would appear at the end of the column list
- Create a view or an editioning view named T that explicitly selects the columns in the right order
- Revoke the grants on T_TBL and issue those grants on the view T
- Update any scripts that create indexes, triggers, constraints, and the like on this table to reference table T_TBL
That process would make the column seem to appear in the middle of the table, but at the cost of a lot of work. Now, however, reordering columns has become rather simple. I can rely on the documented fact (not a trick but, rather, documented behavior) that any column that is set to be visible after being invisible will appear as the last column in the table from a SELECT * perspective. So to do the logical reordering, I need to first hide columns D and E and then add column C. At this point, column C will appear to be right after column B. Next I unhide columns D and E, and they will appear after column C—logically. The following shows the column hiding, adding, and unhiding as well as a query result that shows the logical order of the columns:
SQL> alter table t modify 2 ( d invisible, e invisible ); Table altered. SQL> alter table t add c int; Table altered. SQL> alter table t modify 2 ( d visible, e visible ); Table altered. SQL> desc t Name Null? Type ——————————— ——————————— ——————————— A NUMBER(38) B NUMBER(38) C NUMBER(38) D NUMBER(38) E NUMBER(38)
(Note that on disk the columns will really be stored as A, B, D, E, and C.) You can read more about invisible columns in Oracle Database Administrator’s Guide 12c Release 1 (12.1), Chapter 20, “Managing Tables.”
Improved IntrospectionOracle database application developers have had access to three built-in functions to figure out “Where the heck am I?” in their code:
- DBMS_UTILITY.FORMAT_CALL_STACK
- DBMS_UTILITY.FORMAT_ERROR_ BACKTRACE
- DBMS_UTILITY.FORMAT_ERROR_STACK
These routines, although helpful, were of somewhat limited use. Let’s look at the FORMAT_CALL_STACK routine to see why. Here is a procedure, print_call_stack, for printing out the current call stack:
SQL> create or replace
2 procedure Print_Call_Stack
3 is
4 begin
5 DBMS_Output.Put_Line(
DBMS_Utility.Format_Call_Stack());
6 end;
7 /
Procedure created.
Now, if I have a package, PKG, with nested functions—and even duplicated function names—such as
SQL> create or replace 2 package body Pkg is 3 procedure p 4 is 5 procedure q 6 is 7 procedure r 8 is 9 procedure p is 10 begin 11 Print_Call_Stack(); 12 raise program_error; 13 end p; 14 begin 15 p(); 16 end r; 17 begin 18 r(); 19 end q; 20 begin 21 q(); 22 end p; 23 end Pkg; 24 / Package body created.
I’ll see the output in Listing 1 when I execute the procedure PKG.P.
Code Listing 1: Output from DBMS_UTILITY.FORMAT_CALL_STACK, plus error message
SQL> exec pkg.p
——————— PL/SQL Call Stack ———————
object line object
handle number name
0x6e891528 4 procedure OPS$TKYTE.PRINT_CALL_STACK
0x6ec4a7c0 10 package body OPS$TKYTE.PKG
0x6ec4a7c0 14 package body OPS$TKYTE.PKG
0x6ec4a7c0 17 package body OPS$TKYTE.PKG
0x6ec4a7c0 20 package body OPS$TKYTE.PKG
0x76439070 1 anonymous block
BEGIN pkg.p; END;
*
ERROR at line 1:
ORA-06501: PL/SQL: program error
ORA-06512: at "OPS$TKYTE.PKG", line 11
ORA-06512: at "OPS$TKYTE.PKG", line 14
ORA-06512: at "OPS$TKYTE.PKG", line 17
ORA-06512: at "OPS$TKYTE.PKG", line 20
ORA-06512: at line 1
The bit above BEGIN pkg.p; END; is the output from DBMS_UTILITY.FORMAT_CALL_STACK, whereas the bit below is the error message returned to the client application. (The error message is also available via DBMS_UTILITY.FORMAT_ERROR_BACKTRACE.). As you can see, the output contains useful information, but to use it, you would need to parse it, which can be tricky. The format of these strings, for example, is not set in stone; the strings have changed over the years. (I wrote the OWA_UTIL.WHO_AM_I function, and I did that by parsing these strings. Trust me, the strings change over time!)
But there’s good news. Starting in Oracle Database 12c, you have structured access to the call stack and a series of API calls for interrogating this structure via the new UTL_CALL_STACK package.
To demonstrate, I’ll rewrite the print_call_stack function to use UTL_CALL_STACK, as shown in Listing 2.
Code Listing 2: print_call_stack function, revised and using UTL_CALL_STACK
SQL> create or replace 2 procedure Print_Call_Stack 3 as 4 Depth pls_integer := UTL_Call_Stack.Dynamic_Depth(); 5 6 procedure headers 7 is 8 begin 9 dbms_output.put_line( 'Lexical Depth Line Name' ); 10 dbms_output.put_line( 'Depth Number ' ); 11 dbms_output.put_line( '—————————— ——————— ———————— ——————' ); 12 end headers; 13 procedure print 14 is 15 begin 16 headers; 17 for j in reverse 1..Depth loop 18 DBMS_Output.Put_Line( 19 rpad( utl_call_stack.lexical_depth(j), 10 ) || 20 rpad( j, 7) || 21 rpad( To_Char(UTL_Call_Stack.Unit_Line(j), '99'), 9 ) || 22 UTL_Call_Stack.Concatenate_Subprogram 23 (UTL_Call_Stack.Subprogram(j))); 24 end loop; 25 end; 26 begin 27 print; 28 end; 29 /
With the new print_call_stack function, I am able to figure out how deep I am in the code by using UTL_CALL_STACK .DYNAMIC_DEPTH and can then walk up the stack, using a loop. I will print out the lexical depth along with the line number within the unit I am executing, plus the unit name—and not just any unit name, but the fully qualified, all-the-way-down-to-the-subprogram name within a package and continuing down to the subprogram name within a subprogram name within a subprogram name. For example, running the PKG.P procedure again results in the output and the error message in Listing 3.
Code Listing 3: Output from UTL_CALL_STACK, plus error message
SQL> exec pkg.p Lexical Depth Line Name Depth Number ——————— ——————— ———————— ——————— 1 6 20 PKG.P 2 5 17 PKG.P.Q 3 4 14 PKG.P.Q.R 4 3 10 PKG.P.Q.R.P 0 2 26 PRINT_CALL_STACK 1 1 17 PRINT_CALL_STACK.PRINT BEGIN pkg.p; END; * ERROR at line 1: ORA-06501: PL/SQL: program error ORA-06512: at "OPS$TKYTE.PKG", line 11 ORA-06512: at "OPS$TKYTE.PKG", line 14 ORA-06512: at "OPS$TKYTE.PKG", line 17 ORA-06512: at "OPS$TKYTE.PKG", line 20 ORA-06512: at line 1
This time I get much more than just a line number and a package name, in contrast to what I got previously with DBMS_UTILITY.FORMAT_CALL_STACK. With the UTL_CALL_STACK package, I get not only the line number and the package (unit) name but also the names of the subprograms, and I can see that P called Q called R called P as nested subprograms. Also note that I can see a truer calling level with the lexical depth. For example, I can see that I stepped out of the package to call print_call_stack, which, in turn, called another nested subprogram.
The new UTL_CALL_STACK package will be a nice addition to everyone’s error logging packages. Of course, there are other functions in there for getting owner names, the edition in effect when the code was executed, and more. See Oracle Database PL/SQL Packages and Types Reference 12c Release 1 (12.1), for details.
SQL Text ExpansionThe next Oracle Database 12c feature I look at is SQL text expansion. This new feature enables you to see the expanded text of a SQL statement, with the view text included. This will enable you to see the real query the optimizer is being challenged with.
Many times I have been asked to tune a simple query, such as SELECT * FROM V WHERE X=5, only to discover that the explain plan for the query is 15 pages long and references dozens of objects. To begin to understand that query, I have to understand what V is and what the SQL text behind the V view is. And the text of the V view might itself reference views of views of views. It can be quite a chore to discover what the actual SQL is. SQL text expansion makes this discovery simple.
For example, take the simple query SELECT * FROM ALL_USERS. Under the covers, ALL_USERS—and in fact, the entire Oracle data dictionary—is a view. To see which query is really being executed under the covers, I can use SQL text expansion:
SQL> variable x clob
SQL> begin
2 dbms_utility.expand_sql_text
3 ( input_sql_text =>
'select * from all_users',
4 output_sql_text => :x );
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> print x
X
——————————————————————————————————————
SELECT "A1"."USERNAME"
"USERNAME","A1"."USER_ID"
"USER_ID","A1"."CREATED"
"CREATED","A1"."COMMON" "COMMON" FROM
(SELECT "A4"."NAME"
"USERNAME","A4"."USER#"
"USER_ID","A4"."CTIME"
"CREATED",DECODE(BITAND("A4"."SPARE1",12
8),128,'YES','NO') "COMMON" FROM
"SYS"."USER$" "A4","SYS"."TS$"
"A3","SYS"."TS$" "A2" WHERE
"A4"."DATATS#"="A3"."TS#" AND
"A4"."TEMPTS#"="A2"."TS#" AND
"A4"."TYPE#"=1) "A1"
As you can see, the new EXPAND_SQL_TEXT procedure in the DBMS_UTILITY package delivers SQL text expansion and makes discovery of the actual query being executed rather easy.
SQL text expansion is not limited to views, either. Often you have to tune a query that is being rewritten by Oracle Virtual Private Database and you need to understand what the real SQL text is to begin to tune it. EXPAND_SQL_TEXT helps you discover the real SQL text for that query via the DBMS_RLS package.
To demonstrate this, I set up a small table with a rather simple Oracle Virtual Private Database policy on it. I begin by implementing the security function:
SQL> create or replace
2 function my_security_function
( p_schema in varchar2,
3 p_object in varchar2 )
4 return varchar2
5 as
6 begin
7 return 'owner = USER';
8 end;
9 /
Function created.
As you can see, this is a rather simple function. It will cause each access of the table or view it is associated with to include—transparently—the predicate WHERE OWNER = USER.
Now I’ll continue by defining MY_TABLE and associating this predicate function with the table:
SQL> create table my_table
2 ( data varchar2(30),
3 OWNER varchar2(30) default USER
4 )
5 /
Table created.
SQL> begin
2 dbms_rls.add_policy
3 ( object_schema => user,
4 object_name => 'MY_TABLE',
5 policy_name => 'MY_POLICY',
6 function_schema => user,
7 policy_function =>
'My_Security_Function',
8 statement_types =>
'select, insert, update, delete',
9 update_check => TRUE );
10 end;
11 /
PL/SQL procedure successfully completed.
If I am asked to tune a query against MY_TABLE, I would very much like to see the rewritten query to find out what I’m dealing with. Again, the EXPAND_SQL_TEXT procedure comes in very handy:
SQL> begin
2 dbms_utility.expand_sql_text
3 ( input_sql_text =>
'select * from my_table',
4 output_sql_text => :x );
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> print x
X
————————————————————————————————————————————
SELECT "A1"."DATA" "DATA","A1"."OWNER"
"OWNER" FROM (SELECT "A2"."DATA"
"DATA","A2"."OWNER" "OWNER" FROM
"OPS$TKYTE"."MY_TABLE" "A2" WHERE
"A2"."OWNER"=USER@!) "A1"
Not only can I see the expanded SQL but the text also provides some insight into how Oracle Virtual Private Database does its magic. In this case, Oracle Virtual Private Database simply takes the MY_TABLE reference in the FROM list and turns it into an inline view with the predicate appended to it.
You could always sort of see this rewritten query in earlier releases. It was just much more difficult to piece together. For example, suppose there is a user A who has SELECT access to MY_TABLE. Further, suppose user A would like to see (for tuning) what the query is doing. That user might use an explain plan to do that:
SQL> set autotrace traceonly explain SQL> select * from ops$tkyte.my_table; —————————————————————————————————————— | Id | Operation | Name | —————————————————————————————————————— | 0 | SELECT STATEMENT | | |* 1 | TABLE ACCESS FULL| MY_TABLE | —————————————————————————————————————— Predicate Information (identified by … —————————————————————————————————————— 1 - filter(“OWNER”=USER@!)
Even though you cannot truly see the rewritten SQL, you can certainly piece it together with some work.
For more information on Oracle Virtual Private Database, see Oracle Database Concepts 12c Release 1 (12.1), Chapter 19, “Topics for Database Administrators and Developers.” For more information on EXPAND_SQL_TEXT, see Oracle Database New Features 12c Release 1 (12.1), Chapter 1, “Oracle Database 12c Release 1 (12.1) New Features.”
Next Steps
ASK Tom
Tom Kyte answers your most difficult technology questions. Highlights from that forum appear in this column.
FOLLOW Tom on Twitter
READ more Tom
DOWNLOAD Oracle Database 12c
LEARN more about Oracle Database 12c
FOLLOW Oracle Database
on Twitter
on Facebook
DISCLAIMER: We've captured these popular historical magazine articles for your reference. Links etc contained within these article possibly won't work. These articles are provided as-is with no guarantee that the information within them is the best advice for current versions of the Oracle Database.