| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: drop index or export table
Hi,
Steve <schen_at_prodigy.net> wrote in article
<80l943$5jnm$1_at_newssvr03-int.news.prodigy.com>...
> Thank you for the points. Yes, it is using temp tablespace instead of
> system tablespace.
>
> The foreign keys are pains. They prevent you to rebuild. I
had
> the error saying 'cannot extent temp segent in index tablespace' on the
> foreign key index rebuilding. I don't know if it is related to foreign
> key issue (I did not disable it then)
You got that error possibly because of 2 reasons:
Here is what you can do:
Coalesce the fragmented index tablspace by issuing.
alter tablespace INDEX_TS coalesce;
Then try to re-create. Do not rebuild, recreate.
Another thing that I do which others may disagree, is I set the pctincrease
for all the tablespaces
NOT TABLES, except SYSTEM, RBS, TOOLs etc. to 1. With this set up SMON
process takes care
of COALESCEing diskspace.
alter tablespace INDEX_TS default storage (pctincrease 1 );
If you want to see the fragmentation on any tablespace, then run this
script. You may want to run
this script prior to COALESCEing your index tablespace to get a feel for
it.
set dof off pause off
/************************************************************************ * * * COMPANY : Oracleguru * * FILE NAME : analyze_tablespace_usage.sql * * LANGUAGE : SQL/SQL*Plus/PL-SQL * * INPUT PARAM : This script expects 1 parameter: Tablespace_name * * OUTPUT PARAM: None. * * INPUT FILES : None. * * OUTPUT FILES: $HOME/rep/analyze_tablespace_names_TNAME.lst * * where TNAME = Tablespace_name entered on * * command line to run this script * * * * DESCRIPTION : This script generates tablespace usage for a * * TABLESPACE name entered on the command line. * * * ************************************************************************/
set pause off termout off verify off wrap on serveroutput on size 1000000 set newpage 0 pagesize 58 linesize 80
/*
Generate tablespace analysis report only on Wednesdays. Uncomment the following statements if this is what you want to do.
whenever sqlerror exit
select 1/0
from dual
where to_char(sysdate, 'day') not in ('wednesday');
*/
clear breaks
column today new_value today noprint column time new_value time noprint
/*
Remove report file if exists, otherwise in case of problem, you may
receive a print out of an old file.
*/
host rm -f $HOME/rep/analyze_tablespace_usage_&1..lst
spool $HOME/rep/analyze_tablespace_usage_&1..lst
set feedback off
ttitle today center 'TABLE AND INDEX SPACE USAGE IN TABLESPACE &1' -
right 'Page ' format 990 sql.pno skip 1 -
time -
skip 1 -
analyze_tablespace_usage.sql
select to_char(sysdate, 'DD-MON-YYYY') today,
to_char(sysdate, 'HH:MI:SS AM') time from dual;
set feedback on
/*
Compute total and unused disk space in tablespace. */
DECLARE
total_blocks number; total_bytes number; unused_blocks number; unused_bytes number;/* Above High water mark */ last_used_extent_file_id number; last_used_extent_block_id number; last_used_block number; free_space number;/* Below High water mark */ user_id all_tables.owner%type; object_name all_tables.table_name%type; object_type varchar2 ( 5);--
cursor c1
is
select owner, table_name, 'TABLE'
from all_tables
where tablespace_name = upper('&1')
union
select owner, index_name, 'INDEX'
from all_indexes
where tablespace_name = upper('&1');
BEGIN
open c1;
LOOP
fetch c1
into user_id, object_name, object_type;
exit when c1%notfound;
dbms_space.unused_space
( user_id,
object_name,
object_type,
total_blocks,
total_bytes,
unused_blocks,
unused_bytes,
last_used_extent_file_id,
last_used_extent_block_id,
last_used_block
);
dbms_space.free_blocks
( user_id,
object_name,
object_type,
0,
free_space);
dbms_output.put_line
( object_type||' Name = '||user_id||'.'||object_name ); dbms_output.put_line
( '**********' );
dbms_output.put_line
( 'Total Bytes = '||to_char(total_bytes, '999,999,990')||
' Free Bytes above Water Mark = '||
to_char(unused_bytes,'999,999,990') );
dbms_output.put_line
( 'Bytes to W.Mark= '||to_char(total_bytes -
unused_bytes,'999,999,990')||
' Free Bytes below Water Mark = '||
to_char(free_space*8192, '999,999,990') );
dbms_output.put_line
(
'-------------------------------------------------------------------------
-------');
END LOOP;
END;
..
/
column object format a26 heading 'OBJECT' column file_id format 99990 heading 'FILE|ID ' column block_id format 999990 heading 'BLOCK|ID ' column blocks format 99990 heading 'BLOCKS' column bytes format 9,999,999,999 heading 'BYTES'
ttitle today center 'POSSIBLE FRAGMENTATION IN TABLESPACE &1' -
right 'Page ' format 990 sql.pno skip 1 -
time -
skip 2 -
analyze_tablespace_usage.sql skip 2
select 'freespace' owner, ' ' object,
file_id, block_id, blocks, bytes bytes
from sys.dba_free_space
substr(segment_name, 1, 31),
file_id, block_id, blocks, bytes bytes
from sys.dba_extents
break on bytes on report
compute sum of bytes on report
column segment_name format a26
column tablespace_name format a15
ttitle today center 'SPACE USAGE BY OBJECTS IN TABLESPACE &1' -
right 'Page ' format 990 sql.pno skip 1 -
time -
skip 2 -
analyze_tablespace_usage.sql skip 2
select segment_name, tablespace_name, sum(bytes) bytes
from sys.dba_extents
where tablespace_name = '&1'
group by segment_name, tablespace_name
/
set feedback off
column space heading ' SPACE' column name heading ' INDEX NAME' column btree_space format 9,999,999,999 heading 'TOTAL SPACE' column used_space format 9,999,999,999 heading 'USED SPACE'
ttitle today center 'USED AND FREE SPACE USAGE FOR TABLESPACE &1' -
right 'Page ' format 990 sql.pno skip 1 -
time -
skip 2 -
analyze_tablespace_usage.sql skip 2
select 'Free Space ' space, sum(bytes ) bytes
from sys.dba_free_space
where tablespace_name = upper('&1')
union
select 'Used Space ' space, sum(bytes ) bytes
from sys.dba_extents
where tablespace_name = upper('&1')
/
prompt
prompt
prompt. ********** END OF REPORT *********
spool off
exit
>
>
> Suresh Bhat <suresh.bhat_at_mitchell-energy.com> wrote in message
> news:01bf2d28$9888b0e0$a504fa80_at_mndnet...
> > Correction:
> >
> > My previoous post should say SYS's temporary tablespace not SYSTEM's.
> >
> > Also, if the indexed constraints are foreign keys then you may have to
> > disable them before recreating.
> >
> > Further, Conner suggested rebuilding, this will require twice as much
disk
> > space as recreating,
> > because the old index is still present until the new one is finished.
So
> > you will need 600M which is a lot.
> > Except having the old index for quesries, there is no great advantage
in
> > rebuilding index if you are not moving the index to another tablespace.
> >
> >
> > Suresh
> >
> >
> > Suresh Bhat <suresh.bhat_at_mitchell-energy.com> wrote in article
> > <01bf2c8b$ae5efc00$a504fa80_at_mndnet>...
> > > Hi,
> > >
> > > Steven <SCHEN_at_prodigy.net> wrote in article
> > > <80es3p$3ep0$1_at_newssvr04-int.news.prodigy.com>...
> > > > I have a table with 200M and 8 extents, but its 2 indexes are 250M
> with
> > > over
> > > > 30 extents each.
> > > >
> > > > Do we drop and rebuild indexes or export/import table?
> > > Drop and recreate indexes. Make sure that you have 275m extra space
in
> > > your SYSTEM's temporary tablespace where the index is temporarily
built
> > by
> > > SYS account before it is moved to the specified tablespace.
> > >
> > > > Would table
> > > > export/import also rearrange index extent?
> > >
> > > I believe that is correct. For the following to work you must have
> > > exported with compress=y.
> > >
> > > If you want to see what those storage parameters are then use:
> > > imp indexfile=create_script.sql
> > >
> > > This will not import any data, it simply creates a script for indexes
> and
> > > tables (commented out).
> > >
> > > While you are at it, you may want to try
> > >
> > > imp show=y log=create_script.sql
> > >
> > > With the same effect, but creates script for almost every object
> > including
> > > packages, functions,triggers,
> > > constraints,grants etc.
> > >
> > > > On the other hand, some indexes are associated with constraint
> columns.
> > > > Haven't tried if they can be dropped.
> > > I don't believe that matters.
> > >
> > > Suresh Bhat
> > > Oracleguru
> > > www.oraclegur.net
> > >
>
>
>
Received on Mon Nov 15 1999 - 10:29:19 CST
 |
 |