Re: Dropping Tables and Tablespaces

From: Michael Serbanescu <mserban_at_postoffice.worldnet.att.net>
Date: 1997/08/01
Message-ID: <33E19893.6F7A@postoffice.worldnet.att.net>#1/1

O. Use TRUNCATE TABLE <table_name> statement before running SQL*Loader or as an option in your SQL*Loader control file. TRUNCATE is an option like APPEND/REPLACE/INSERT. Experiment to see which alternative is faster.

1. Any objects/structures associated with a table (indexes, constraints, triggers) are automatically dropped when the table is dropped (views, packages, procedures remain, but become invalid).
2. You are right: dropping and re-creating a tablespace will eliminate that tablespace's fragmentation and improve performance. Alternatively, rather that dropping and re-creating the tablespace, you could use the ALTER TABLESPACE <tablespace_name> COALESCE statement in your SQL script. Again, experiment (if you have that luxury) to see which way is better.
3. ORA-01119 means insufficient space on device; either delete the datafile (in your DOS batch scripts) or use the REUSE clause in the CREATE TABLESPACE statement in the SQL scripts. BTW, you don't have to issue a COMMIT after dropping the table and/or tablespace: a DDL statement (CREATE/DROP/ALTER) does an implicit COMMIT after it executes.

Hope this helps.

Michael Serbanescu

---

Jack Pawsat wrote:
>
> A little background:
> I am running Oralce 7.2 on a Novell 4.12 NetWare Server.
> We download 1GB data from a Wang server weekly. I cannot download only
> recently changed information from the Wang; I have to download the whole
> tamale. Currently this is a weekly process, but we would like it to be
> more often.
>
> So, I want to automate it as much as possible. To this end, I am writing
> several DOS batch programs and SQL scripts. Using SQL*Loader, the data is
> inserted into the Oracle tables.
>
> When I try to overwrite the existing data using REPLACE, the process is
> very slow and I run out of extents. (One table has about 750,000 records
> and is 650mb large.) So, I drop the table and insert the data fresh.
>
> Ok, now my questions begin:
>
> 0. Is there a better way?
>
> 1. When I drop a table, should I drop its indexes as well? Or is this
> done automatically?
>
> 2. I have assigned a tablespace to each table. When I drop the table,
> should I drop the table space? I said "yes", thinking that recreating the
> tablespace would lead to better optimization.
>
> 3. After I drop the table and tablespace, I issue a COMMIT and then try to
> recreate the tablespace. But why do I get the following error every
> time? (Perhaps, I shouldn't drop the tablespace, but this error is
> bothering me)
>
> CREATE TABLESPACE DORFSPACE
> *
> ORA-01119: error in creating database file
> 'SERVER2/VOL1:\DATABASE\DORF.DAT'
> ORA-09200: sfccf: error creating file
> OSD-02074: a truncated write occurred
>
> ----------------------------------------
> Jack Pawsat
  Received on Fri Aug 01 1997 - 00:00:00 CDT