Oracle DDL statment ( like CTAS) , after executed, not show in V$SQL-CodePudding

Why Oracle "DDL" statements (like "CTAS"), after executed, does not shown in V$SQL view ? How can get "SQL_ID" of that? I want to use "SQL_ID" in SQl plan baselines. TNX

CodePudding user response：

CTAS operations appear in the v$sql view

SQL> create table t1 as select * from dba_objects ;

Table created.

SQL> select sql_text,sql_id from v$sql where sql_text like '%create table t1 as select%' ;

SQL_TEXT
--------------------------------------------------------------------------------
SQL_ID
-------------
create table t1 as select * from dba_objects
4j5kv6x7cz5r7

select sql_text,sql_id from v$sql where sql_text like '%create table t1 as selec
t%'
5n4xnjkt3vz3h

SQL Plan Baselines are part of SPM ( SQL Plan Management )

A SQL plan baseline. A plan baseline is a set of accepted plans that the optimizer is allowed to use for a SQL statement. In the typical use case, the database accepts a plan into the plan baseline only after verifying that the plan performs well. In this context, a plan includes all plan-related information (for example, SQL plan identifier, set of hints, bind values, and optimizer environment) that the optimizer needs to reproduce an execution plan.

If you are using a CTAS recurrently, I guess you are doing it in batch mode, thus dropping the table and then recreate it afterwards using the mentioned CTAS command. I would rather try to see what is the problem with the SELECT itself of that statement.

But SQL Baselines are more focus for solving queries which plans can be fixed and evolved as the optimizer is not always choosing the best one.

CodePudding user response：

V$SQL shows just the first 20 charachter for CTAS. It is a bug in Oracle Database 11g. For more details, see: (https://stackoverflow.com/questions/27623646/oracle-sql-text-truncated-to-20-characters-for-create-alter-grant-statements/28087571#28087571\)1.