NetNews Usenet Archive 1992 #20/NN_1992_20.iso/spool/comp/database/oracle/1513

Text File | 1992-09-08 | 4.1 KB | 124 lines

Path: sparky!uunet!mcsun!sunic!psinntp!psinntp!dlogics!hermes.dlogics.com!dlm
From: [e-mail]
Newsgroups: comp.databases.oracle
Subject: Re: how to compress database and move indexes to different tablespace
Message-ID: <[e-mail]>
Date: 5 Sep 92 08:52:44 CST
References: <[e-mail]>
Organization: Datalogics, Inc., Chicago, IL
Lines: 113

Recently, [e-mail] 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 / @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
$    @T'scratch'.com
$    delete T'scratch'.com;*
$    exit
--
Dave Mausner, Senior Consultant / Datalogics Inc / Chicago IL / 312-266-4450
[e-mail]                                 Motto: Just show me the code