jump to navigation

Describing tables you can’t DESC October 7, 2009

Posted by mwidlake in internals.
Tags: ,

This is more an oddity than anything particularly useful. Sometimes you can’t use the sql*plus DESCRIBE {DESC} command on tables- but you might have an alternative.

I’m doing a lot of work for a client on a database.  I have SELECT ANY DICTIONARY but not SELECT ANY TABLE privilege. This is because there is sensitive data in the database and it is duly protected {and this is certainly not the first time I have worked for a client with full dictionary access but not data access, it’s becoming normal}. I’m granted access to specific things as needs arise.

I knew I had to look at a table called AD_FAKE_STATS_COLUMNS.

select count(*) from mdw.AD_FAKE_STATS_COLUMNS
select count(*) from mdw.AD_FAKE_STATS_COLUMNS
ERROR at line 1:
ORA-00942: table or view does not exist

Have I got the table name wrong?

select table_name,owner
from dba_tables
where table_name = 'AD_FAKE_STATS_COLUMNS'

TABLE_NAME                     OWNER
------------------------------ -----------------

OK, it is there, I don’t have access to it. Fine, I don’t want access to the data, I just want to see the structure of the table:

ORA-04043: object mdw.AD_FAKE_STATS_COLUMNS does not exist

Oh. DESC in sql*plus does not work.

I can’t DESC a table I do not have access to carry out DML on. I’m going to have to go and ask someone to give me permission to see the table. How annoying.

Or do I?

Enter value for tab_name: AD_FAKE_STATS_COLUMNS
old 21: where table_name like upper (nvl('&TAB_NAME','W')||'%')
new 21: where table_name like upper (nvl('AD_FAKE_STATS_COLUMNS','W')||'%')

TAB_OWN  TAB_NAME              COL_NAME              M COL_DEF
                               COLUMN_NAME           Y VARCHAR2(30)
                               COPY_STATS_FROM       N VARCHAR2(61)
                               LOW_VALUE_SQL         N VARCHAR2(100)
                               HIGH_VALUE_SQL        N VARCHAR2(100)
                               DISTINCT_SQL          N VARCHAR2(100)
                               DAYS_HIST_NULL_AVGLEN N NUMBER(3,0)


I have access to the data dictionary. So I can see the structure of the table, which after all is what I wanted and is what the client is happy for me to have.{I’ve never much liked the DESC command in sql*plus, I replaced it with a little sql script against the data dictionary years ago}.

In case you want it, here is the script:

-- tab_desc.sql
-- Martin Widlake date? way back in the mists of time
-- my own replacement for desc.
-- 16/11/01 improved the data_type section
 SET PAUSE 'Any Key...>'
col Tab_own form A10
col tab_name form a22 wrap
col col_name form a28 wrap
col col_def form A14
 break on tab_own skip 1 on tab_name skip 1
 spool tab_desc.lst
 owner                               Tab_Own
,table_name             Tab_Name
,column_name            Col_Name
,decode(NULLABLE,'Y','N','Y') Mand
                ,     to_char(data_precision)||
                                      ,      ','||data_scale)
       ,'LONG RAW',null
				     )  col_def
from dba_tab_columns
where table_name like upper (nvl('&TAB_NAME','WHOOPS')||'%')
order by 1,2,column_id,3,4
spool off
clear col


1. Noons - October 8, 2009

I wonder if the dbms_metadata.get_ddl() package function would work in these conditions?

2. mwidlake - October 8, 2009

That’s an interesting thought…

select dbms_metadata.get_ddl(‘TABLE’,’AD_FAKE_STATS_COLUMNS’,’MDW’) from dual;
ORA-31603: object “AD_FAKE_STATS_COLUMNS” of type TABLE not found in schema “MDW”
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 105
ORA-06512: at “SYS.DBMS_METADATA”, line 2805
ORA-06512: at “SYS.DBMS_METADATA”, line 4333
ORA-06512: at line 1

No, it appears not.

I’m kind of glad about that as I always have to type in selects using dbms_metadata.get_ddl half a dozen times before I get rid of all my syntax and typo mistakes 🙂

3. Log Buffer #165: a Carnival of the Vanities for DBAs | Pythian Group Blog - October 9, 2009

[…] Martin Widlake has some ideas about what he calls an oddity: describing tables you can’t DESC. […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: