Re: IO question

From: Nathan Carney <ncarney_at_btinternet.com>
Date: Sun, 20 Jan 2002 22:10:03 +0000 (UTC)
Message-ID: <a2ff7p$9vi$1@knossos.btinternet.com>

To select tablespace and data_file info you could use the following (logged on as a dba, system , sys, internal) from sqlplus.

column tablespace_name format a20
column file_name format a50

select tablespace_name , file_name , round(bytes/1048576,2) "Size_MB" from dba_data_files
order by tablespace_name
/

To find out where the indexes and tables are placed try:

column segment_type format a20
column tablespace_name format a20

Select owner , segment_type, tablespace_name, count(*) "Number" ,. round(sum(bytes/1048576),2) "Size_MB"
from dba_segments
group by owner , segment_type, tablespace_name order by owner , segment_type, tablespace_name /

To create a new tablespace for the indexes use

create tablespace tablespace_name
datafile 'full_file_path_and_file_name' size xxxxM,
datafile 'full_file_path_and_file_name' size xxxxM,

...
datafile 'full_file_path_and_file_name' size xxxxM /

You won't need any down time for this but it would be advisable to do this at a quiet time.

Indexes can also be moved without a database outage using the below syntax (and in 8i+ you can force the move).

set heading off
set pagesize 0
set linesize 100
set feedback off

spool /tmp/move_indexes.sql

select 'ALTER INDEX '||owner||'.'||segment_name||chr(10)

         ||' STORAGE (INITIAL '||floor(bytes/1048576)+1||' NEXT ||'floor(bytes/10485760)+1||' PCTINCREASE 0 PCTFREE 5)'||chr(10)

         ||'REBUILD TABLESPACE index_tablespace;' from dba_segments
where owner = upper('&owner')
and tablespace_name != 'index_tablespace' and segment_type = 'INDEX'
/

spool off

Where index_tablespace_name is the tablespace you wish to move your indexes into.

Then just run the created file.

You can also move tables using an Alter table move ... syntax if they are in the wrong tablespace.

NO downtime should be required for any of these operations assuming you have the space for the new tablespace.

Hope this helps

Nathan

"Fergus" <sinhask_at_swbell.net> wrote in message news:oIp28.8144$Lz3.3081275523_at_newssvr30.news.prodigy.com...
> see my 2c below

>
>

> "A Wong" <ajkwong5_at_hotmail.com> wrote in message
> news:_ml28.17299$467.747785_at_news2.calgary.shaw.ca...
> > it's better to have your index on a seperate physical disk than data,
> > correct?

>

> in general, always a good idea - also depends on your hardware, of course

>

> > how do you accomplish that?

>

> by specifying the tablespace datafile location on different disk
> log in as sysdba and select * from dba_data_files - for file locations
> you will have to figure out which tablespace constains index and which
> contains data. look at data and index sql for help.

>

> >create a partition on each disk, but
> > how do you put different tablespaces on each disk?

>

> normal disk partitions will not help - since they are logical - physically
> they are on same disks and use same disk controllers etc. it should be
> preferable on separate disks.

>

> each tablespace consists of one or more files - see dba_tablespaces and
> dba_data_files. The location of datafiles is in dba_data_files

>

> >also, the database is
> > live, so would this take long to do?

>

> Might need some downtime - depending on your how many datafiles you
> actually have and what are their sizes - you can do ground work and write
> scripts beforehand - then all depends on time the datafile copying takes.
>
> If you just wish to move index tablespaces and provided your vendor has
not
> put index and data in same file, you can drop the indexes, drop the
> tablespaces
> and recreate them as per your wish.

>
>


> >The install of the database was done

> > by using scripts provided by the vendor...so I don't know much about how
> > it's set up, but am slowly learning

>
> Also verify if moving stuff around will not void support from your
vendor -
> if applicable.

>
> Received on Sun Jan 20 2002 - 16:10:03 CST