select column only if it exists

i need to display tablespace attributes

SQL> select tablespace_name, status, contents, logging, extent_management, allocation_type, segment_space_management, retention, bigfile from dba_tablespaces;
TABLESPACE_NAME STATUS CONTENTS LOGGING EXTENT_MAN ALLOCATIO SEGMEN RETENTION BIG
——————– ——— ——— ——— ———- ——— —— ———– —
SYSTEM ONLINE PERMANENT LOGGING LOCAL SYSTEM MANUAL NOT APPLY NO

looks fine. but what if I try that on my oracle7 database? I will get ORA-00904: invalid column name

ok, I will then select only the columns that exist! than select from dba_tablespaces

t.sql:
set termout off
def logging=”"
def extent_management=”"
def allocation_type=”"
def segment_space_management=”"
def retention=”"
def bigfile=”"
col logging new_v logging
col extent_management new_v extent_management
col allocation_type new_v allocation_type
col segment_space_management new_v segment_space_management
col retention new_v retention
col bigfile new_v bigfile
select ‘,logging’ logging from dba_tab_columns where owner=’SYS’ and table_name=’DBA_TABLESPACES’ and COLUMN_NAME=’LOGGING’;
select ‘,extent_management’ extent_management from dba_tab_columns where owner=’SYS’ and table_name=’DBA_TABLESPACES’ and COLUMN_NAME=’EXTENT_MANAGEMENT’;
select ‘,allocation_type’ allocation_type from dba_tab_columns where owner=’SYS’ and table_name=’DBA_TABLESPACES’ and COLUMN_NAME=’ALLOCATION_TYPE’;
select ‘,segment_space_management’ segment_space_management from dba_tab_columns where owner=’SYS’ and table_name=’DBA_TABLESPACES’ and COLUMN_NAME=’SEGMENT_SPACE_MANAGEMENT’;
select ‘,retention’ retention from dba_tab_columns where owner=’SYS’ and table_name=’DBA_TABLESPACES’ and COLUMN_NAME=’RETENTION’;
select ‘,bigfile’ bigfile from dba_tab_columns where owner=’SYS’ and table_name=’DBA_TABLESPACES’ and COLUMN_NAME=’BIGFILE’;
set termout on ver off
select tablespace_name, status, contents &logging &extent_management &allocation_type &segment_space_management &retention &bigfile from dba_tablespaces;

let’s try

SYS@LSC69 AS SYSDBA/7.3.4.5
SQL> @t
TABLESPACE_NAME STATUS CONTENTS
——————– ——— ———
SYSTEM ONLINE PERMANENT

SYS@LSC65 AS SYSDBA/8.1.7.4
SQL> @t
TABLESPACE_NAME STATUS CONTENTS LOGGING EXTENT_MAN ALLOCATIO
——————– ——— ——— ——— ———- ———
SYSTEM ONLINE PERMANENT LOGGING DICTIONARY USER

SYS@LSC67 AS SYSDBA/9.2.0.6
SQL> @t
TABLESPACE_NAME STATUS CONTENTS LOGGING EXTENT_MAN ALLOCATIO SEGMEN
——————– ——— ——— ——— ———- ——— ——
SYSTEM ONLINE PERMANENT LOGGING DICTIONARY USER MANUAL

SYS@LSC63 AS SYSDBA/10.2.0.1
SQL> @t
TABLESPACE_NAME STATUS CONTENTS LOGGING EXTENT_MAN ALLOCATIO SEGMEN RETENTION BIG
——————– ——— ——— ——— ———- ——— —— ———– —
SYSTEM ONLINE PERMANENT LOGGING LOCAL SYSTEM MANUAL NOT APPLY NO

one script for any version

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>