Skip to Main Content
  • Questions
  • Doubts on Import/Export + temprary tablespace+shutdown/startup

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Praveen.

Asked: January 03, 2001 - 5:32 pm UTC

Last updated: December 02, 2004 - 8:45 am UTC

Version: 8.1.6

Viewed 1000+ times

You Asked

Hi Tom,
I look after a warehouse which has feeds going on it basically using the insert into... as select from ...the ERP systems.

Now often this blows up my temporary tablespace which is of the type temporary and is around 6 gb in size.Now to get my temp tablespace freed up i have no option but to shut it down.

Earlier you told me another way using
alter tablespace .... permanent;
and then again
alter tablespace .... temporary;
and also told to be careful to use it so that it shall slow down the system when the data dictionary starts to keep track of the temp segments.so i do not use it.
So i generally shutdown and startup,

Now i do
1)alter system checkpoint;
2)shutdown abort
3)startup
and the database is back started in less than 60-70 seconds.
After this startup if i fire shutdown immediate
then it takes up hell lot of time to shutdown .
Why is this so when the startup has already recovered it and is perfectly fine and is not in need to any media recovery,so why should my shutdown immediate take time when i fire it right after startup.
Also i have this doubt
1)Say one load used my temp by 3 gb and fired a commit;
2)then say another load started and did heavy sorts again ,so would oracle reuse the existing 3 gb or would it try to use only the free space and no segments would be dropped of the unused temp segments.


----------------------------------------------
2)
When importing a user's tables from one db to another i got an error
> oerr ora 2437
02437, 00000, "cannot validate (%s.%s) - primary key violated"
// *Cause: attempted to validate a primary key with duplicate values or null
// values.
// *Action: remove the duplicates and null values before enabling a primary
// key.

so i thought that there was a duplicate row in this table.
which i got also a duplicate row.
----------------------------------------------------
Now my question is when the user had taken an export was the primary key constraint enabled/disabled at export time in his db.

It couldn't have been enabled because the primary key would be violated there also.

Then why was the export file haveing this statement

----------------------------------------------------
IMP-00017: following statement failed with ORACLE error 2437:
"ALTER TABLE "ANILRCUSTOMER" ADD CONSTRAINT "PK_ANILRCUSTOMER" PRIMARY KEY ("US"
"ERNAME") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAANIL (INITIAL 53"
"2480 NEXT 524288 MINEXTENTS 1 MAXEXTENTS 249 PCTINCREASE 1 FREELISTS 1 FREE"
"LIST GROUPS 1 BUFFER_POOL DEFAULT) ENABLE NOVALIDATE"
IMP-00003: ORACLE error 2437 encountered
ORA-02437: cannot validate (ANILER.PK_ANILRCUSTOMER) - primary key violated
----------------------------------------------------

Also if the user export had constarints=N the above alter table
should not have been in export file.

So it reduces to it was disabled in his db and then when he took an export it was written to the exp file and imp was trying to
enable it at my end.
---------------------------------------------------------------
---------------------------------------------------------------
Here is the complete log
---------------------------------------------------------------
Connected to: Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production

Import file: expdat.dmp > ANILER.dmp

Enter insert buffer size (minimum is 8192) 30720>

Export file created by EXPORT:V08.00.05 via conventional path
import done in US7ASCII character set and US7ASCII NCHAR character set
export client uses WE8ISO8859P1 character set (possible charset conversion)
export server uses WE8ISO8859P1 NCHAR character set (possible ncharset conversio
n)
List contents of import file only (yes/no): no >

Ignore create error due to object existence (yes/no): no >

Import grants (yes/no): yes >

Import table data (yes/no): yes >

Import entire export file (yes/no): no > y

. importing ANILER's objects into ANILER
. . importing table "ANILRCUSTOMER" 1865 rows imported
IMP-00017: following statement failed with ORACLE error 2437:
"ALTER TABLE "ANILRCUSTOMER" ADD CONSTRAINT "PK_ANILRCUSTOMER" PRIMARY KEY ("US"
"ERNAME") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAANIL (INITIAL 53"
"2480 NEXT 524288 MINEXTENTS 1 MAXEXTENTS 249 PCTINCREASE 1 FREELISTS 1 FREE"
"LIST GROUPS 1 BUFFER_POOL DEFAULT) ENABLE NOVALIDATE"
IMP-00003: ORACLE error 2437 encountered
ORA-02437: cannot validate (ANILER.PK_ANILRCUSTOMER) - primary key violated
About to enable constraints...
IMP-00015: following statement failed because the object already exists:
"ALTER TABLE "ANILRCUSTOMER" ENABLE CONSTRAINT "PK_ANILRCUSTOMER""
Import terminated successfully with warnings.
---------------------------------------------------------------


More than Thanks in advance and Happy New Year too
Praveen

and Tom said...

You are confusing the sympton with the solution.

Temporary segments are allocated once and then managed by the system. By shutting down, what you have done is KICKED out sessions that were using the temporary segments - preventing you from using them. You didn't cause anything to be "more free" then it would have been otherwise -- you removed users from the database -- killed their work -- and took over their temporary tablespace extents!


This statement:

"and the database is back started in less than 60-70 seconds.
After this startup if i fire shutdown immediate
then it takes up hell lot of time to shutdown ."

proves that. A shutdown immediate WAITS for the people who are USING the temporary segments to finish with them and log out. The database will not shutdown with a shutdown immediate until everyone logs out gracefully. By doing a shutdown abort, you just "kick them out" causing them to lose their work (there is probably a group of very angry people out there somewhere wondering who keeps killing their database!)

You need MORE temporary space to be allocated to your system in order to meet your day to day needs, that is all. the alter to a permanent and then temporary would not give you back any space since the space is actually being used.


See the dynamic performance views v$sort_segment and v$sort_usage to see whats allocated and who is currently using it.


2) go back to the source data and issue:

select /*+ full(t) */ pk_value, count(*)
from t
group by pk_value
having count(*) > 1;


and see what that says.



Rating

  (11 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

error:ora 1659 in import the dmp file

cgf, May 22, 2002 - 9:14 pm UTC

Tom
I used the following command for import the dmp file.
C:>imp develop/develop file=c:\backup.dmp full=y
but the computer tell me the following information:
error:ora 1659

please tell me the reason.
OK.
cgf


Tom Kyte
May 22, 2002 - 11:05 pm UTC

you don't have the error messages manual?


01659, 00000, "unable to allocate MINEXTENTS beyond %s in tablespace %s"
// *Cause: Failed to find sufficient contiguous space to allocate MINEXTENTS
// for the segment being created.
// *Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space to the
// tablespace or retry with smaller value for MINEXTENTS, NEXT or
// PCTINCREASE

is this stement correct.......

a reader, May 23, 2002 - 4:28 am UTC

Tom, I thought that 'The database will not
shutdown with a shutdown immediate until everyone logs out gracefully' was a shutdown normal and NOT immediate. I'm sure that immediate kill user sessions and then exits gracefully.

Tom Kyte
May 23, 2002 - 7:41 am UTC

what I meant is that everyone is logged out gracefully -- they are rolled back. they are not aborted. the shutdown immediate results in a "clean" shutdown.

It will kill AND WAIT for the session to be cleaned out before shutting down. That was my point.

A shutdown abort -- that just STOPS the transactions right there, right then. The database is shutdown in a "fuzzy" state -- requires instance recovery upon restart.

sorry for the confusion.

Who is using Temp

Andrew Allen, May 23, 2002 - 11:44 am UTC

Here is a query I use to see who is using temp, how much they are using, and the sql doing it.
- - - - - - - -
SELECT /*+ ORDERED */
u.tablespace,
s.sid || ',' || s.serial# as sid_ser,
s.username,
s.osuser,
(SUM(u.blocks)*TO_NUMBER(a.value))/1048576 as blk_mb,
x.sql_text,
s.module || ':' || s.action as "Module:Action"
FROM v$sort_usage u
,v$session S
,v$sql X
,v$parameter a
WHERE s.saddr = u.session_addr
AND s.sql_address = x.address
AND s.sql_hash_value = x.hash_value
AND a.name = 'db_block_size'
GROUP
BY u.tablespace
, s.sid
, s.serial#
, s.username
, s.osuser
, a.value
, x.sql_text
, s.module
, s.action
ORDER
BY u.tablespace
, s.sid
;


A Little Clarification

TAF, May 23, 2002 - 12:27 pm UTC

Tom,

You might mention to him to convert this temp tablespace to a tempfile instead of being dictionary managed. I had the same problem with a 5g temp tablespace. When it blew it "locked" the tablespace until a system bounce or alter tablespace command. This is an Oracle bug, so to speak. I made the temp tablespace a truly tempfile and even after blowing the tablespace, it was immediately available for reuse. No system bounce needed.

Tom Kyte
May 23, 2002 - 3:33 pm UTC

...
Now often this blows up my temporary tablespace which is of the type temporary
and is around 6 gb in size.
.....

I assume based on his description -- that it is using tempfiles.

A reader, September 07, 2004 - 9:19 am UTC

Tom,

Could you please tell what are the rules/prerequisites and justifications in-order to bounce a production instance?

Tom Kyte
September 07, 2004 - 9:53 am UTC

you'd have to ask "your site" really. only your site knows what their concepts of operations is, what is allowable, what is feasible, what is optimal.


For example -- asktom.oracle.com, it is a "production instance". Guess what, if I feel like bouncing it, I will.

www.oracle.com is a production instance. it is never allowable for it to be down. same with our email, hr, order entry, etc -- not without weeks of advance notice.

Then there are systems that fall in between the two.

A reader, September 07, 2004 - 10:03 am UTC

Tom,

Thank you for the reply. We are planning a O/S reboot sometime. Is it a good time to restart 8.1.7? Can I bounce the instance and go for the o/s reboot? Any issues to be looked at? Please clarify

Dave, September 07, 2004 - 11:17 am UTC

if you are bouncing the OS, then you have no choice but to bounce the instance

A reader, September 07, 2004 - 11:19 am UTC

Ok, Thank you. Any effects of o/s boot on oracle? Any things that should be done manually on oracle after o/s boot? Please suggest as I am new!

Tom Kyte
September 07, 2004 - 12:45 pm UTC

this is something the DBA would take care of?

A reader, September 07, 2004 - 1:13 pm UTC

Tom,

The dba is really busy with some other things and this needs to be done by me. Any pointers?

Tom Kyte
September 07, 2004 - 2:10 pm UTC

you are kidding right?

theoretically, if the DBA set up the machine properly, the database will take care of itself.

if they didn't set it up to autostart, you'll need the DBA as you need DBA privileges.

A reader, September 07, 2004 - 6:17 pm UTC

Tom,

Thank you for the answer. The action was performed and o/s (hp-ux 11.00) was shutdown & brought up and so the oracle. However, there were some application related issues ( still working on ...) Should oracle be shutdown manually/gracefully prior to o/s shutdown? i.e without relying on the o/s d/b shutdown functionality?

Tom Kyte
September 08, 2004 - 8:15 am UTC

it would have been "nice" but not necessary to cleanly shutdown the database yes. Normally, the DBA would have set it up so the machine would include oracle in its normal shutdown processing. So, if the DBA was doing what DBAs should do -- it did shutdown cleanly.

Esi, December 02, 2004 - 8:23 am UTC

i have a cgi web page.now i want it to throw an error where there is a null value.again i want to restrict the input of duplicate values for the mobile number.i have tried all i can but when i get one to work ,the other doesnt work.that is when i manage to check for null values ,it doesnt respond to the following command like moving to the next page but still brings me the page that tells me to fill my form again.what do i do?

Tom Kyte
December 02, 2004 - 8:45 am UTC

what does that have to do with "Doubts on Import/Export + temprary tablespace+shutdown/startup.


Not that you even describe the language or anything you are using (or how this relates to the database)

Not that we want you to put that information here -- since this has nothing whatsoever to do with the other text on this page.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.