| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: stored procedure
This is a multi-part message in MIME format.
------=_NextPart_000_0063_01C050D8.61FE7D00 Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Here is a statement that I created to recreate all the indexes from a certain tablespace. You may adapt it to your case. Run this script before you drop the indexes. It will create sql statements to: 1. for the primary key - drop constraint, drop PK, recreate it, and reestablish the constraint; 2. for other indexes drop and recreate the index. It will use the current storage parameters which might need to be edited.
HTH Djordje
> Anyone have a stored procedure that I can use to drop and recreate the
indexes?
> For many of my load jobs I need to drop the indexes and then create them
again
> later. What I would like is if Oracle didn't drop the definition of the
index
> out of the data dictionary so I would still have the information to
rebuild it.
> Instead I am going to have to create my own index dictionary tables so
that when
> I drop the index the index definition is stored in the table for
rebuilding it
> after the load.
>
> If anyone has any code for this under 8i I'd appreciate it.
>
> Dave Turner
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: David Turner
> INET: turner_at_elvis.mu.org
>
> 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
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
------=_NextPart_000_0063_01C050D8.61FE7D00 Content-Type: application/octet-stream;
name="IndexRecreateCreation.sql"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;
filename="IndexRecreateCreation.sql"
select i.table_name || '00' npr
, CHR(10)||CHR(10)||CHR(10)||CHR(10) from dba_indexes i
, dba_constraints c
where i.owner =3D c.owner and i.table_name =3D c.table_name and i.index_name =3D c.constraint_name and c.constraint_type =3D 'P' and i.tablespace_name=3D'NCWIZ_INDEX'union
, CHR(10)||CHR(10)||CHR(10)||CHR(10)||
'alter table ' || c1.table_name || ' drop constraint ' || =
c1.constraint_name || ';'
, dba_constraints c2
where c1.r_owner||c1.r_constraint_name in
(select i.owner||i.index_name
from dba_indexes i
, dba_constraints c
where i.owner =3D c.owner
and i.table_name =3D c.table_name
and i.index_name =3D c.constraint_name
and c.constraint_type =3D 'P'
and i.tablespace_name=3D'NCWIZ_INDEX'
)
and c1.constraint_type =3D 'R'
, 'alter table ' || i.table_name || ' drop primary key;' from dba_indexes i
, dba_constraints c
where i.owner =3D c.owner and i.table_name =3D c.table_name and i.index_name =3D c.constraint_name and c.constraint_type =3D 'P' and i.tablespace_name=3D'NCWIZ_INDEX'union
, 'alter table ' || i.table_name || CHR(10)
|| ' add constraint ' || c.constraint_name || CHR(10)
|| ' primary key (' || cc.column_name || ') ' || CHR(10)
|| ' using index' || CHR(10)
|| ' NOLOGGING' || CHR(10)
|| ' PCTFREE 10 INITRANS 2 MAXTRANS 255' || CHR(10)
|| ' storage('
|| ' INITIAL ' || decode(sign(s.blocks*2-32),-1,32,s.blocks*2) || =
'K '
|| ' NEXT ' || =
decode(sign(TRUNC(s.blocks/2-32)),-1,32,TRUNC(s.blocks/2)) || 'K '
|| ' PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 121' || CHR(10)
|| ' FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL =
DEFAULT)' || CHR(10)
|| ' tablespace "NCWIZ_IND";' || CHR(10)
from dba_indexes i
, dba_constraints c
, dba_cons_columns cc
, dba_segments s
where i.owner =3D c.owner
and i.table_name =3D c.table_name
and i.index_name =3D c.constraint_name
and c.owner =3D cc.owner
and c.constraint_name =3D cc.constraint_name
and i.owner =3D s.owner
and i.index_name =3D s.segment_name
, 'alter table ' || c1.table_name || ' add constraint ' || = c1.constraint_name || CHR(10)
|| ' foreign key (' || cc1.column_name || ')' || CHR(10)
|| ' references ' || c2.table_name || '(' || =
cc2.column_name || ')' || CHR(10)
|| ' enable;'
from dba_constraints c1
, dba_cons_columns cc1
, dba_constraints c2
, dba_cons_columns cc2
where c1.r_owner||c1.r_constraint_name in
(select i.owner||i.index_name
from dba_indexes i
, dba_constraints c
where i.owner =3D c.owner
and i.table_name =3D c.table_name
and i.index_name =3D c.constraint_name
and c.constraint_type =3D 'P'
and i.tablespace_name=3D'NCWIZ_INDEX'
)
and c1.constraint_type =3D 'R'
and c1.r_owner =3D c1.owner
and c1.r_constraint_name =3D c2.constraint_name
and c1.owner =3D cc1.owner
and c1.constraint_name =3D cc1.constraint_name
and c2.owner =3D cc2.owner
 |
 |