Opening the describe window takes a noticeable amount of time, so I enabled Spool SQL (see below).
- What's most interesting is the duplicate query to user_objects (MATERIALIZED VIEW).
- I would guess that an initial query to user_objects without restricting to object_type should be able to determine if it is a materialized view or not (would return two rows) and also return [created, last_ddl_time, object_id, status]
What do you think?
----------------------------------
-- Session:
-- Timestamp: 11:34:11.506
Select 1
from sys.USER_TABLES
where table_name =:nm;
:nm(VARCHAR[10],IN)='TEST_TABLE'
----------------------------------
-- Session:
-- Timestamp: 11:34:11.519
Select 1
from sys.user_objects
where object_name =:nm
and object_type =:t;
:nm(VARCHAR[10],IN)='TEST_TABLE'
:t(VARCHAR[17],IN)='MATERIALIZED VIEW'
----------------------------------
-- Session:
-- Timestamp: 11:34:12.393
Select 1
from sys.user_objects
where object_name =:nm
and object_type =:t;
:nm(VARCHAR[10],IN)='TEST_TABLE'
:t(VARCHAR[17],IN)='MATERIALIZED VIEW'
----------------------------------
-- Session:
-- Timestamp: 11:34:12.397
Select t.table_name, user as owner, t.cluster_name,
t.partitioned, t.iot_type, t.tablespace_name, t.last_analyzed, round(t.num_rows) num_rows,
t.initial_extent, t.degree, t.logging, t.cache, t.buffer_pool
, t.monitoring, t.temporary, t.table_type, t.table_type_owner, t.nested
, t.Compression, t.dropped
, t.compress_for, t.flash_cache, t.cell_flash_cache, t.segment_created
, decode(nvl(tablespace_name, 'x') || upper(partitioned) || nvl(iot_type, 'x') || to_char(pct_free), 'xNOx0', 'YES', 'NO') as External
from sys.user_all_tables t
where t.table_name = :obj;
:obj(VARCHAR[10],IN)='TEST_TABLE'
----------------------------------
-- Session:
-- Timestamp: 11:34:12.406
Select created, last_ddl_time, object_id, status
from sys.user_objects
where object_name = :nm
and object_type = :t;
:nm(VARCHAR[10],IN)='TEST_TABLE'
:t(VARCHAR[5],IN)='TABLE'
----------------------------------
-- Session:
-- Timestamp: 11:34:12.486
Select cols.column_id, cols.column_name as Name, cols.nullable,
cols.data_type as Type, cols.data_type_mod,
decode(cols.data_type, 'CHAR', cols.char_length,
'VARCHAR', cols.char_length,
'VARCHAR2', cols.char_length,
'NCHAR', cols.char_length,
'NVARCHAR', cols.char_length,
'NVARCHAR2', cols.char_length,
null) nchar_length,
decode(cols.data_type, 'NUMBER', cols.data_precision + cols.data_scale, cols.data_length) length,
cols.data_precision Precision, cols.data_scale Scale, cols.data_length dlength, cols.data_default
,cols.char_used
,cols.hidden_column, cols.internal_column_id
,cols.qualified_col_name
,InitCap(cols.histogram) histogram
,cols.virtual_column
,cols.num_distinct, cols.num_nulls, round(cols.density, 5) density
,cols.user_generated, cols.default_on_null, cols.identity_column
,coms.comments
FROM
sys.user_col_comments coms,
sys.user_tab_cols cols
where 1=1
and coms.table_name (+) = cols.Table_Name
and coms.column_name (+) = cols.column_Name
and coms.origin_con_id (+) = TO_NUMBER(sys_context('USERENV','CON_ID'))
and cols.table_name = :TABNAME
order by column_id, internal_column_id;
:TABNAME(VARCHAR[10],IN)='TEST_TABLE'
----------------------------------
-- Session:
-- Timestamp: 11:34:12.497
select column_name, encryption_alg, salt
from sys.user_encrypted_columns
where table_name = :tn;
:tn(VARCHAR[10],IN)='TEST_TABLE'
----------------------------------
-- Session:
-- Timestamp: 11:34:12.505
Select comments
from sys.user_col_comments
where 1=1
and table_name = :obj
and origin_con_id = TO_NUMBER(sys_context('USERENV','CON_ID'))
and column_name = :col;
:obj(VARCHAR[10],IN)='TEST_TABLE'
:col(VARCHAR[13],IN)='TEST_COLUMN_1'
----------------------------------
-- Session:
-- Timestamp: 11:34:12.555
Select c1.column_name, c1.position, a1.constraint_name
from sys.user_cons_columns C1, sys.user_constraints A1
where A1.table_name = C1.Table_name
and A1.constraint_name = C1.Constraint_Name
and A1.constraint_type = 'P'
and A1.table_name =:TabName
ORDER BY c1.position;
:TabName(VARCHAR[10],IN)='TEST_TABLE'
----------------------------------
-- Session:
-- Timestamp: 11:34:12.690
Select t.trigger_name, t.trigger_type, t.triggering_event,
t.when_clause, t.status enabled, o.status, t.owner, o.object_id, t.trigger_body
from sys.DBA_OBJECTS o, sys.DBA_TRIGGERS t
where t.table_owner = :own
and t.table_name = :obj
and o.object_type = 'TRIGGER'
and o.object_name = t.trigger_name
and o.owner = t.owner;
:own(VARCHAR[6],IN)='KLASSX'
:obj(VARCHAR[10],IN)='TEST_TABLE'
----------------------------------
-- Session:
-- Timestamp: 11:34:12.952
Select owner, index_name, uniqueness, status
,INDEX_TYPE, TEMPORARY, PARTITIONED, LOGGING, DEGREE, funcidx_status, join_index
from sys.DBA_INDEXES
where table_owner = :town
and table_name = :tname
order by index_name;
:town(VARCHAR[6],IN)='KLASSX'
:tname(VARCHAR[10],IN)='TEST_TABLE'
