Re: how to compress database and move indexes to different tablespace
Date: 5 Sep 92 08:52:44 CST
Message-ID: <1992Sep5.085244.1304_at_hermes.dlogics.com>
Recently, suskind_at_bbs.mdcbbs.com writes:
> I am trying to compress my database. While I am at it I am moving stuff
> into various tablespaces to improve performance and layout. My original
> database has only ONE tablespace "system". My new database will have
> "system", "user", "temp", and "index". I can see how to easily move
> a user's tables into the "user" tablespace by only giving him resource
> in that tablespace and altering his user account so "user" is his default
> tablespace.
This is a very good idea. Also, consider creating a ROLLBACK tablespace just
for your rollback segments. And see if you can put each tablespace on a
different device.
>
> However, how to I move the corrisponding indexes? I have tried using the
> "show" option of IMP to generate a file, but this file is in such a poor
> format I cannot use it without EXTENSIVE editing. Does anybody have a
> utility that will take the file generated by the "show" option and make it
> usable? The format I get is ONLY 80 column records and EVERY line is
> enclosed in double quotes.
Yes, "show" has its problems; you could use the "INDEXFILES=filename"
option which will create usable CREATE statements. You will have to edit this
file carefully to replace the tablespace names. If you are a unix dba then of
course this is easy; otherwise you will have to concoct something.
Here's another solution for oracle v6 VMS which can be adapted to anybody's o/s. It is a SQL script that scans the data dictionary to create another SQL script which it then executes:
$! database_integrity.com
$! d. l. mausner. 17-apr-92.
$! (c) 1992 datalogics, inc.
$!
$ scratch = f$getjpi("","pid")
$ define /user sys$output T'scratch'.com
$ sqlplus -s / _at_sys$input
set pages 0 lines 100 verify off feedb off pause off recsep off column NP format a1 noprint
column TX format a80 word
prompt $ set verify prompt $ sqlplus -s / rem be sure only certain special users have DBA privs.
select 'revoke dba from ' || username || ';' TX
from sys.dba_users
where dba_priv = 1 and
username not in ('SYS','SYSTEM','PUBLIC','OPS$ORACLE') and
username not in ('OPS$TOPS_SYS','OPS$NSG','OPS$RAS','OPS$MFM') and
username like 'OPS$%'
/
rem be sure temporaries default to TEMP1.
select 'alter user ' || username || ' temporary tablespace TEMP1;' TX
from sys.dba_users
where connect_priv = 1 and
temporary_tablespace != 'TEMP1' and
username not in ('SYS','PUBLIC')
/
rem be sure everyone has resources on INDEX1.
select 'grant resource on index1 to ' || username || ';' TX
from sys.dba_users
where connect_priv = 1 and
username not in (
select username from sys.dba_ts_quotas where tablespace_name='INDEX1') and
username not in ('SYS','PUBLIC')
/
rem be sure user indexes are stored in INDEX1 tablespace.
set termout off
drop table u1indexes
/
set termout on
create table u1indexes (xo, xn, bo, bn, un) as
select owner, index_name, table_owner, table_name,
decode(uniqueness, 'NONUNIQUE', null, uniqueness)
from sys.dba_indexes
where tablespace_name = 'USER1'
/
select
'drop index ' || xo || '.' || xn || ';' TX
from u1indexes
/
select
'create ' || un || ' index ' || xo || '.' || xn || ' on ' || bo || '.' || bn TX,
xo NP, xn NP, 0 NP
from u1indexes
union
select
decode(column_position, 1, '(', ',') || column_name TX,
index_owner NP, index_name NP, column_position NP
from sys.dba_ind_columns
where index_name in (select xn from u1indexes)
union
select
') tablespace index1;' TX,
xo NP, xn NP, 999 NP
from u1indexes
order by 2,3,4
/
set termout off
drop table u1indexes
/
set termout on
prompt exit
exit
$ _at_T'scratch'.com $ delete T'scratch'.com;* $ exit
-- Dave Mausner, Senior Consultant / Datalogics Inc / Chicago IL / 312-266-4450 dlm_at_dlogics.com Motto: Just show me the codeReceived on Sat Sep 05 1992 - 16:52:44 CEST