Describing tables you can’t DESC

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 10.2.0.3 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
------------------------------ -----------------
AD_FAKE_STATS_COLUMNS          MDW

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:

desc mdw.AD_FAKE_STATS_COLUMNS
ERROR:
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?

@tab_desc
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
-----------------------------------------------------------------
MDW      AD_FAKE_STATS_COLUMNS TABLE_NAME            Y VARCHAR2(30)
                               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 ON
 SET PAUSE 'Any Key...>'
 SET PAGES 24
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
 select
 owner                               Tab_Own
,table_name             Tab_Name
,column_name            Col_Name
,decode(NULLABLE,'Y','N','Y') Mand
,data_type||decode(data_type
       ,'NUMBER','('
        ||decode(to_char(data_precision)
                ,null,'38'
                ,     to_char(data_precision)||
                      decode(data_scale,null,''
                                      ,      ','||data_scale)
                 )
                    ||')'
       ,'DATE',null
       ,'LONG',null
       ,'LONG RAW',null
,'('||Substr(DATA_LENGTH,1,5)||')'
				     )  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
--