space in the temporary tablespace were largely used when create a tab

[signalsys]

I have created a table named "A".
And table "B" is on another database and its size is about 4G.
Now i want to insert into A with the content of table B using dblink and in order to use less undo segment,I use the following statements:

SQL> alter table A nologging;
SQL> insert /*+append*/ into A select * from B;

But during the insertion, I found that the usage of temporary tablespace was increasing rapidly.
For a table about 4G in size, it needs at least 4G temporary tablespace.

I want to know when table A is in nologging mode, Why it needs a lot of space in temporary tablespace during the insertion?

 

[SantaMufasa]

Signal, the "NOLOGGING" option prevents activity on the the LOG BUFFER and subsequent output to the ONLINE REDO LOG files (and possibly the archive redo log files if you are in ARCHIVELOG mode).

"NOLOGGING" has absolutely no impact on the TEMP or UNDO tablespaces.

As far as INSERT activity is concerned, keep in mind what "data" Oracle needs to store in the UNDO segments in case of a ROLLBACK...the "data" prior to INSERT was nothing at all. Therefore, virtually no impact on UNDO.

Oracle uses the TEMP tablespace for sorts that cannot occur in the SGA's SORT_AREA. While INSERTing rows, Oracle doesn't care about the sort order of row data (thus doesn't sort -- or use TEMP tablespace.)

Question: Upon INSERT, what "sort order" does Oracle care about?

Answer: Indexes. Therefore, it is my guess that you have active indexes on your target table that Oracle must sort to keep current.

If you wish to diminish the during-INSERT impact of indexes, then you may wish to consider disabling/dropping them prior to your INSERTing, then rebuild them following the INSERT.

Let us know your thoughts.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[signalsys]

Hi,Santa
You are right. I will check to see whether the empty table has already an index on its columns. And then try it again.
Thnx