Creating Comma Seperated Lists

From TekiWiki

Jump to: navigation, search

This whole wiki has now moved to http://www.springboardsolutions.com/wiki

Go to this page Creating Comma Seperated Lists

Line 35: Line 35:
order by owner,table_name
order by owner,table_name
-
OWNER TABLE_NAME               COLUMNS
+
OWNER TABLE_NAME                   COLUMNS
-
----- ------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+
----- ---------------------------- -------------------------------------------------------------------
-
SYS  DBA_TABLESPACES          TABLESPACE_NAME,BLOCK_SIZE,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,MAX_SIZE,PCT_INCREASE,MIN_EXTLEN,STATUS,CONTENTS,LOGGING,FORCE_LOGGING,EXTENT_MANAGEMENT,ALLOCATION_TYPE,PLUGGED_IN,SEGMENT_SPACE_MANAGEMENT,DEF_TAB_COMPRESSION,RETENTION,BIGFILE,PREDICATE_EVALUATION,ENCRYPTED,COMPRESS_FOR
+
SYS  DBA_TABLESPACE_GROUPS        GROUP_NAME,TABLESPACE_NAME
-
SYS  DBA_TABLESPACE_GROUPS    GROUP_NAME,TABLESPACE_NAME
+
SYS  DBA_TABLESPACE_USAGE_METRICS TABLESPACE_NAME,USED_SPACE,TABLESPACE_SIZE,USED_PERCENT
-
SYS  DBA_TABLESPACE_THRESHOLDS TABLESPACE_NAME,CONTENTS,EXTENT_MANAGEMENT,THRESHOLD_TYPE,METRICS_NAME,WARNING_OPERATOR,WARNING_VALUE,CRITICAL_OPERATOR,CRITICAL_VALUE
+
SYS  DBA_TAB_COMMENTS            OWNER,TABLE_NAME,TABLE_TYPE,COMMENTS
-
 
+
SYS  DBA_TAB_PRIVS                GRANTEE,OWNER,TABLE_NAME,GRANTOR,PRIVILEGE,GRANTABLE,HIERARCHY
-
 
+
SYS  DBA_TAB_STATS_HISTORY        OWNER,TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,STATS_UPDATE_TIME
-
 
+
SYS  DBA_TAB_STAT_PREFS          OWNER,TABLE_NAME,PREFERENCE_NAME,PREFERENCE_VALUE
</pre>
</pre>
Line 51: Line 51:
select owner,table_name,max(ltrim(sys_connect_by_path(column_name,','),',')) Columns
select owner,table_name,max(ltrim(sys_connect_by_path(column_name,','),',')) Columns
from (
from (
-
select owner,table_name,column_name,row_number() over (partition by table_name order by column_id) rn
+
select owner,table_name,column_name,row_number() over (partition by table_name order by column_name) rn
from sys.dba_tab_columns t
from sys.dba_tab_columns t
)
)
Line 60: Line 60:
group by owner,table_name
group by owner,table_name
order by owner,table_name
order by owner,table_name
 +
 +
OWNER TABLE_NAME                  COLUMNS
 +
----- ---------------------------- -------------------------------------------------------------------
 +
SYS  DBA_TABLESPACE_GROUPS        GROUP_NAME,TABLESPACE_NAME
 +
SYS  DBA_TABLESPACE_USAGE_METRICS TABLESPACE_NAME,TABLESPACE_SIZE,USED_PERCENT,USED_SPACE
 +
SYS  DBA_TAB_COMMENTS            COMMENTS,OWNER,TABLE_NAME,TABLE_TYPE
 +
SYS  DBA_TAB_PRIVS                GRANTABLE,GRANTEE,GRANTOR,HIERARCHY,OWNER,PRIVILEGE,TABLE_NAME
 +
SYS  DBA_TAB_STATS_HISTORY        OWNER,PARTITION_NAME,STATS_UPDATE_TIME,SUBPARTITION_NAME,TABLE_NAME
 +
SYS  DBA_TAB_STAT_PREFS          OWNER,PREFERENCE_NAME,PREFERENCE_VALUE,TABLE_NAME
 +
</pre>
</pre>
Line 76: Line 86:
set colsep " "
set colsep " "
 +
 +
CO,NAME
 +
--,---------------------
 +
AF,AFGHANISTAN
 +
AL,ALBANIA
 +
DZ,ALGERIA
 +
AS,AMERICAN SAMOA
 +
AD,ANDORRA
 +
AO,ANGOLA
 +
AI,ANGUILLA
 +
AQ,ANTARCTICA
 +
AG,ANTIGUA AND BARBUDA
 +
</pre>
</pre>

Revision as of 23:48, 24 November 2011

Retrieved from "http://www.homemarketeer.com/wiki/index.php?title=Creating_Comma_Seperated_Lists"
Personal tools