RE: Index DDL generator, SQLPLUS want trucation after 80 columns

From: Diana Duncan <Diana_at_fileFRENZY.com>
Date: Tue, 19 Sep 2000 14:07:04 -0400
Message-Id: <10624.117371@fatcity.com>

How about just doing the following? Name the columns you don't want to appear with an alias, then:

col column2 noprint
col column3 noprint
col column4 noprint

I know it's not what you asked, but it's the first thing that came to mind...

Diana

-----Original Message-----
From: Linda Hagedorn [mailto:Linda_at_pets.com] Sent: Tuesday, September 19, 2000 1:45 PM To: Multiple recipients of list ORACLE-L Subject: Index DDL generator, SQLPLUS want trucation after 80 columns

Hello,

I wrote a little index DDL generator using SQL. To get the DDL in the correct order I had to add some literal and sort fields that cannot be part of the output. I'd like to have a linesize of 80 and truncate the rest of the fields. SQLPLUS is wrapping the lines. Does anyone know if there is a way to truncate a SQLPLUS output line at nn columns?

I'm creating 36+ indexes from production. I realize I can cut/paste from an import file, but my eyes were getting blurry looking at the (Oracle are you listening?) scrambled output format.

Any information is appreciated.

This is the ouput I want from the generator (minor hand editing is acceptable - commas, sizes):

CREATE INDEX BVAMDIN.SYS_C003235 on BVADMIN.BV_ALERT_SPEC ( STRINGCOL1
INTCOL3
PCTFREE 10 INITRANS 2 MAXTRANS 255

STORAGE (INITIAL                     NEXT

MINEXTENTS 1 MAXEXTENTS 2G PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE INDEXES NOLOGGING ; This is the wrapped output with the sort fields I want truncated:

CREATE INDEX BVAMDIN.SYS_C003235 on BVADMIN.BV_ALERT_SPEC (

                                         SYS_C003235
        1

STRINGCOL1
1                                        SYS_C003235
        2

INTCOL3
2                                        SYS_C003235
        2

PCTFREE 10 INITRANS 2 MAXTRANS 255
                                         SYS_C003235
        3

STORAGE (INITIAL                     NEXT
                                         SYS_C003235
        4

MINEXTENTS 1 MAXEXTENTS 2G PCTINCREASE 0 FREELISTS 1
                                         SYS_C003235
        5

FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE INDEXES NOLOGGING ;
                                         SYS_C003235
        6

This is the INDEX DDL generator.

set linesize 80 ;
SELECT distinct
substr('CREATE INDEX BVAMDIN.'
||index_name||' on BVADMIN.'||table_name||' ( ',1,80) , ' ', substr(index_name,1,35), 1
from dba_indexes
where index_name in
('BILLING_ADDRESSES_ID_IDX','SYS_C003235','BV_DEST_ITEMS_INDEX',
'SYS_C003400','BV_EXT_BO_REL','BV_EXT_BO_REL_IDX1','BV_EXT_BO_REL_IDX2')
union
select column_name, to_char(column_position), substr(index_name,1,35), 2 from dba_ind_columns
where index_name in
('BILLING_ADDRESSES_ID_IDX','SYS_C003235','BV_DEST_ITEMS_INDEX',
'SYS_C003400','BV_EXT_BO_REL','BV_EXT_BO_REL_IDX1','BV_EXT_BO_REL_IDX2')
union
select substr('PCTFREE 10 INITRANS 2 MAXTRANS 255 ',1,80) , ' ' , substr(INDEX_NAME,1,35), 3
FROM DBA_INDEXES
WHERE INDEX_NAME IN
('BILLING_ADDRESSES_ID_IDX','SYS_C003235','BV_DEST_ITEMS_INDEX',
'SYS_C003400','BV_EXT_BO_REL','BV_EXT_BO_REL_IDX1','BV_EXT_BO_REL_IDX2')
UNION

SELECT substr('STORAGE (INITIAL '||'                   '
||' NEXT '||'                 ',1,80), ' ', 

substr(INDEX_NAME,1,35), 4
FROM DBA_INDEXES
WHERE INDEX_NAME IN
('BILLING_ADDRESSES_ID_IDX','SYS_C003235','BV_DEST_ITEMS_INDEX',
'SYS_C003400','BV_EXT_BO_REL','BV_EXT_BO_REL_IDX1','BV_EXT_BO_REL_IDX2')
UNION
SELECT substr('MINEXTENTS 1 MAXEXTENTS 2G PCTINCREASE 0 FREELISTS 1 ',1,80) ,' ',substr(INDEX_NAME,1,35), 5
FROM DBA_INDEXES
WHERE INDEX_NAME IN
('BILLING_ADDRESSES_ID_IDX','SYS_C003235','BV_DEST_ITEMS_INDEX',
'SYS_C003400','BV_EXT_BO_REL','BV_EXT_BO_REL_IDX1','BV_EXT_BO_REL_IDX2')
UNION
SELECT substr('FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE INDEXES NOLOGGING ; ',1,80)
,' ',substr(INDEX_NAME,1,35), 6
FROM DBA_INDEXES
WHERE INDEX_NAME IN
   ('BILLING_ADDRESSES_ID_IDX','SYS_C003235','BV_DEST_ITEMS_INDEX',
'SYS_C003400','BV_EXT_BO_REL','BV_EXT_BO_REL_IDX1','BV_EXT_BO_REL_IDX2')
order by 3, 4 , 2 ;

Linda Hagedorn

echo '[q]sa[ln0=aln256%Pln256/snlbx]sb3135071790101768542287578439snlbxq'|dc

-- 
Author: Linda Hagedorn
  INET: Linda_at_pets.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L

Received on Tue Sep 19 2000 - 13:07:04 CDT