Printable View
Hi Friends,
I have a user in peoplesoft environment with some 10,000 tables but almost 6000 tables are empty. I'm trying to drop this user by using cascade option but it is taking too long...it's more than one hour since I have issued the command.
Could anyone please tell me why drop user is taking so much time.
Thanks and Advance
Regards
Sandy
maybe there are some tables which have many extents which need to be de-allocated first. Or maybe you are breaking some constraints somewhere and it will come back and tell you sooner or later
What version of Oracel are you using?
In versions prior to 8.1.6 it could take a long time as the process was very ineffecient.
Regards
Thanks for your quick reply friends...
I'm using 8.1.7 on Solaris 7. I think there is no constraint problem as peoplesoft takes care of all the constraints automatically...we don't have any customized data also...
Or I think probably this may be the because of the large amount of data...??
Still the drop is going on...
Can there be any Table Locking problem?
Well my uneducated guess is that regardless of how much data is in the table the object and all it's branches have to be written out of the data dictionary. So, if there are 10 bazillion tables and 20 bazillion indexes, synonyms, views ect..., well, you do the math.
I still maintain that it is much easier to just shoot the users.
:D
MH
With the figures you have given about your tables, my guess is it would take you around 2 hours. Apart from cleaning up your tablespace, system tablespace would also undergo corresponding amount of cleanup time. With around 2000 objects, it takes me almost 10 minutes. Maybe someone else will share their timings.
consdier writing an sqr to truncate all the tables and then issuing
the drop user, we do that to reload test from prod and seems to work well
steve
Try to :
Disable constraints for all tables in the schema
Truncate all tables
Drop tables
Drop user
Quote:
Originally posted by patel_dil
With the figures you have given about your tables, my guess is it would take you around 2 hours. Apart from cleaning up your tablespace, system tablespace would also undergo corresponding amount of cleanup time. With around 2000 objects, it takes me almost 10 minutes. Maybe someone else will share their timings.
When DROP, TRUNCATE, ALTER is issued, actual clean up of data or whatsoever will not happen.
Oracle will only drop in the POINTERS pointing to the Mem Locations, This should not take much time..
Actual Problem might be in droping the Pointers itself, as i faced some problems in droping user & got internal error ORA 600, because Oracle was unable to DROP a object in that user.
What i would suggest is try droping all objects, and you will know which object is the culprit.
You could loop and use DYNAMIC SQL exec to drop all objects in that schema.
Abhay.
Oh, it takes much more for Oracle to drop a database object than just to "drop some poiters pointing to mem locations". It takes *many* updates/deletes/inserts into data dictionary tables and/or fixed tables to drop a single table.Quote:
Originally posted by abhaysk
When DROP, TRUNCATE, ALTER is issued, actual clean up of data or whatsoever will not happen.
Oracle will only drop in the POINTERS pointing to the Mem Locations,
For example, you drop a table, and oracle has to do:
- delete one row from OBJ$
- delete one or more rows from COL$
- probably delte some rows from IND$
- delete one or more rows from SEG$
- delete one or more rows from UET$
- insert one or more row into FET$
- probably delete some rows from IDL_* tables
- ...
And all those tables are quite heavily indexed and I have named just a few dictionary tables (perhaps less than 10%) that must be taken care of just for one simple DROP TABLE statement. So you can immagine all this work might take quite a significant amount of time. Not to speak about all the work that SMON has to perform afterwards to actually clean up the space in tablespaces.
So it is much more to do than just to drop few "pointers".
Thanks for dropping that pointer.
Quote:
Originally posted by jmodic
Oh, it takes much more for Oracle to drop a database object than just to "drop some poiters pointing to mem locations". It takes *many* updates/deletes/inserts into data dictionary tables and/or fixed tables to drop a single table.
For example, you drop a table, and oracle has to do:
- delete one row from OBJ$
- delete one or more rows from COL$
- probably delte some rows from IND$
- delete one or more rows from SEG$
- delete one or more rows from UET$
- insert one or more row into FET$
- probably delete some rows from IDL_* tables
- ...
And all those tables are quite heavily indexed and I have named just a few dictionary tables (perhaps less than 10%) that must be taken care of just for one simple DROP TABLE statement. So you can immagine all this work might take quite a significant amount of time. Not to speak about all the work that SMON has to perform afterwards to actually clean up the space in tablespaces.
So it is much more to do than just to drop few "pointers".
Jurij, As always, Your answers are always great.
Oh Yes, its again droping the pointers of the Mem Locs...Quote:
Originally posted by jmodic
Oh, it takes much more for Oracle to drop a database object than just to "drop some poiters pointing to mem locations". It takes *many* updates/deletes/inserts into data dictionary tables and/or fixed tables to drop a single table.
For example, you drop a table, and oracle has to do:
- delete one row from OBJ$
- delete one or more rows from COL$
- probably delte some rows from IND$
- delete one or more rows from SEG$
- delete one or more rows from UET$
- insert one or more row into FET$
- probably delete some rows from IDL_* tables
- ...
So it is much more to do than just to drop few "pointers".
Wat ever u mentioned is correct as per Logical Point...
like delete one row from N number of BASE tables.....for DROPing just one TABLE/OBJECT... THIS operation dosent take more than few milli sceonds... as i mentioned, its again droping pointer of the memory location...
What do you think DELETE operration will do...do u think Oracle/OS ( combined )will try to make 1's to 0's at datafile/hard disk level?
Ahh..then it would take days to DROP a SCHEMA with 10000 objects.
Delete is nothing but droping the pointer pointing to the MEM location where the record exist.
We would have been very clear if ORACLE would have had given source code on how DELETE, DROP all goes about.
Ofcource it will take some time for SMON to clean up / reorganise some DATA blocks in Tablespace...
But not as much the POSTER posted that it taking hours together to do a DROP operation...
Well in such case, i would assume that OS/Harware Config/System Config is not good enough to handle MEM operations...Or some where the PROCESS is struck.
Or there is really some problem with the ORACLE server in droping some of the objects or rather a particular object.
Abhay.
First we have some terminology isues here. When you say "pointers of the Mem Locs" - what exactly do you mean here? Pointers in RAM (SGA) that point to some other locations in RAM (SGA)? As this is what usually is understood as "pointers of the Mem Locs". And what has droping tables to do with SGA and pointers?Quote:
Originally posted by abhaysk
Oh Yes, its again droping the pointers of the Mem Locs...
Wat ever u mentioned is correct as per Logical Point...
like delete one row from N number of BASE tables.....for DROPing just one TABLE/OBJECT... THIS operation dosent take more than few milli sceonds... as i mentioned, its again droping pointer of the memory location...
Or do you mean memory loactions that hold some addresses to certain storage (disk) locations?
Or do you mean storage (disk) loactions that hold some addresses to other storage (disk) locations?
Can you be more specific?
Hm, and what do you think Oracle will do? Do some changes in SGA and that's it? You have some ideas about how transactions are processed in oracle, right? So you must know there must be something done at datafile/hard disk level, right? DROP table *is a transaction* and it can't be commited untill the changes are written to disks (redo log files at least) - is there any doubt about that?Quote:
What do you think DELETE operration will do...do u think Oracle/OS ( combined )will try to make 1's to 0's at datafile/hard disk level?
Now of course DBWR will not try to zero-out every bit of the disk space table has occupied. But I have allready told you in previous reply what changes it will have to do to the datadictionary tables. And those changes ARE VERY PHISICAL, they must be performed in buffer cache and then they must be written to disks before the DROP TABLE can complete.
Well yes, if each of those tables would consist of hundreds or thousands of dictionary managed extents, then it actually takes days to drop the schema! Even if each table has only one extent it will take quite some time to drop schema with 10000 segments - have you tried something like this yet? I did. It certanly was taking much more than it would take Oracle to "drop some memory pointers".Quote:
Ahh..then it would take days to DROP a SCHEMA with 10000 objects.
Ah, here we are again. Where are those pointers? In memory? No. On disk? Yes, of course. More specificaly, in the header of database blocks where the records are stored. So what do you mean by "droping the pointer"? It has to perform write operation to database block holding that row. And there is nothing mystical about that - the modification has to be performed physicaly, on disks, no other way around this. Of course changes are made in buffer cache first (blocks need to be read into cache first if they are not there yet), but then those changes need to be written to disks. No other way.Quote:
Delete is nothing but droping the pointer pointing to the MEM location where the record exist.
Why would you need a source code for that? The whole thing is crystally clear, documented and described in many manuals and books.Quote:
We would have been very clear if ORACLE would have had given source code on how DELETE, DROP all goes about.
It is so easy to perform a demonstration on any database where dropping a single table can take minutes or even hours to complete. It has been discused and demonstrated many times in this forum too, so you could try it yourself.Quote:
Ofcource it will take some time for SMON to clean up / reorganise some DATA blocks in Tablespace...
But not as much the POSTER posted that it taking hours together to do a DROP operation...
Well in such case, i would assume that OS/Harware Config/System Config is not good enough to handle MEM operations...Or some where the PROCESS is struck.
Or there is really some problem with the ORACLE server in droping some of the objects or rather a particular object.
Abhay.
In any case, I recomend you to read Note:68836.1 (How To Efficiently Drop A Table With Many Extents) on Metalink. And some Concept Manual rereading would be beneficial, too. ;)
what has SMON to do with all this?!
That was a really good explanation Jurij.
Sure, I nail it with a simple explaination and Jurij gets all the credit! Ha, such is life.
Let's show a little love for the uneducated Juniors guess.
MH
:D
Jurij:
PS see below demo to prove that the time taken for Modifying the BASE TABLES (DATA DICT) is not much. And yes time taken for DROPING segement will be more if it has many extents.
Code:===========
USER 1
===========
TEST:SYS> ed
Wrote file afiedt.buf
1 create tablespace DROP_USER datafile 'C:\oracle\oradata\test\Drop_User.dbf' size 100M autoextend on next 10M maxsize 200M
2 extent management DICTIONARY
3* default storage (initial 1K next 1K pctincrease 0 maxextents UNLIMITED)
TEST:SYS> /
Tablespace created.
TEST:SYS> ed
Wrote file afiedt.buf
1 create user DROPING_USER identified by DROPING
2* default tablespace DROP_USER
TEST:SYS> /
User created.
Elapsed: 00:00:01.03
TEST:SYS> grant dba,connect,resource to DROPING_USER;
Grant succeeded.
Elapsed: 00:00:02.02
After creation I create 100 Segments in above user with only 1 segment having extents greater than 500.
TEST:SYS> conn DROPING_USER/DROPING
Connected.
TEST:SYS> set sqlprompt TEST:DROPING_USER>
TEST:DROPING_USER>ed
Wrote file afiedt.buf
1 begin
2 for i in 1..100 loop
3 execute immediate ('CREATE TABLE TAB' || i ||' (ID number) tablespace drop_user');
4 end Loop;
5* end;
TEST:DROPING_USER>/
PL/SQL procedure successfully completed.
Elapsed: 00:00:08.09
TEST:DROPING_USER>create table tab101 (ID number) tablespace drop_user
2 storage (initial 512 next 512 maxextents unlimited);
Table created.
TEST:DROPING_USER>begin
2 for i in 1..1000000 loop
3 insert into TAB101 values(i);
4 end Loop;
5 end;
6 /
PL/SQL procedure successfully completed.
Elapsed: 00:02:17.01
TEST:DROPING_USER>commit;
Commit complete.
Elapsed: 00:00:00.00
TEST:DROPING_USER>select count(*) from dba_extents where segment_name='TAB101';
COUNT(*)
--------------------
800
Elapsed: 00:00:01.05
Now i drop the user using CASCADE
TEST:DROPING_USER>conn / as sysdba
Connected.
TEST:DROPING_USER>drop user DROPING_USER cascade;
User dropped.
Elapsed: 00:00:52.00
Note it took 52 Secs.
My Point for all these demostration is as below..Code:=================
User 2
=================
Here i create 300 segments with 2 segments having extents greater than 500.
TEST:SYS> create TABLESPACE DROPING_TABLESPACE datafile 'C:\oracle\oradata\test\Drop_tablespace.dbf' size 100M autoextend on next 10M maxsize 200M
2 extent management DICTIONARY
3 default storage (initial 1K next 1K pctincrease 0 maxextents UNLIMITED);
Tablespace created.
Elapsed: 00:00:04.08
TEST:SYS> create user DROP_TABLESPACE identified by DROPING
2 default tablespace DROP_USER;
User created.
Elapsed: 00:00:00.00
TEST:SYS> grant dba,connect,resource to DROP_TABLESPACE;
Grant succeeded.
Elapsed: 00:00:00.00
TEST:SYS> spool off
TEST:SYS>conn DROP_TABLESPACE/DROPing
Connected.
TEST:SYS>set sqlprompt TEST:DROP_TABLESPACE>
TEST:DROP_TABLESPACE>begin
2 for i in 1..100 loop
3 execute immediate ('CREATE TABLE TAB' || i ||' (ID number) tablespace drop_user');
4 end Loop;
5 end;
6 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:09.07
TEST:DROP_TABLESPACE>begin
2 for i in 101..300 loop
3 execute immediate ('CREATE TABLE TAB' || i ||' (ID number) tablespace DROPING_TABLESPACE ');
4 end Loop;
5 end;
6 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:05.00
TEST:DROP_TABLESPACE>create table tab301 (ID number) tablespace DROPING_TABLESPACE
2 storage (initial 512 next 512 maxextents unlimited);
Table created.
Elapsed: 00:00:00.00
TEST:DROP_TABLESPACE>create table tab302 (ID number) tablespace DROPING_TABLESPACE
2 storage (initial 128 next 64 maxextents unlimited);
Table created.
TEST:DROP_TABLESPACE>select distinct tablespace_name, count(segment_name) from dba_segments where owner='DROP_TABLESPACE'
2 group by tablespace_name;
TABLESPACE_NAME COUNT(SEGMENT_NAME)
------------------------------ --------------------
DROPING_TABLESPACE 202
DROP_USER 100
TEST:DROP_TABLESPACE>ed
Wrote file afiedt.buf
1* select tablespace_name,owner,extents from dba_segments where segment_name in ('TAB302','TAB301')
TEST:DROP_TABLESPACE>/
TABLESPACE_NAME OWNER EXTENTS
------------------------------ ------------------------------ --------------------
DROPING_TABLESPACE DROP_TABLESPACE 451
DROPING_TABLESPACE DROP_TABLESPACE 752
Elapsed: 00:00:05.00
TEST:DROP_TABLESPACE>drop tablespace DROPING_TABLESPACE including contents;
Tablespace dropped.
Elapsed: 00:00:30.02
TEST:DROP_TABLESPACE>drop tablespace DROP_USER including contents;
Tablespace dropped.
Elapsed: 00:00:07.06
TEST:DROP_TABLESPACE>
TEST:DROP_TABLESPACE>conn / as sysdba
Connected.
TEST:DROP_TABLESPACE>drop user DROP_TABLESPACE;
User dropped.
Elapsed: 00:00:05.04
If we add above timings..effectively we needed just 42 secs to drop user having 302 segments as compared to direct DROP USER
that we saw previously it took 52 secs to drop only 100 segments...
1) DROP ofcource is a TRAN and it will be recorded in REDOs...and also many DATA DICT BASE tables have to
be modified ( most of them being DELETE ) and wont take much time.
2) WE saw above that the sencond method infact took less time then first 1 and with more segments than in first..so we can infer that DELETING ( modifying )
DATA DICT BASE tables will not take much time.
3) Since we droped TABLESPACE..it has HEADER info since its associated with the FILE level sctucts...so DROP of these MEM Locs
by OS will not be a problem at all...BUT in case of DROP USER CASCASE, oracle/OS has to first ensure that all the segments
be droped and then USER. So, we know AT SEGEMNT level we dont have HEADER info as we have at EXTENT,BLOCK level.
So when a SEGEMNT is needed to be droped...it will try to drop the MEM locations of all extents...thats time consuming
as these extents allocated are not at file level...but the are inside FILES.
Obiviously if MANY extents are there, then it will take hell lots of time.
Rather if we would have DROPED the tablespace which contains these segments it would be much faster.
So one need to degin there DB as to have all USER objects in a set of Tablespaces.
Quote:
Originally posted by jmodic
First we have some terminology isues here. When you say "pointers of the Mem Locs" - what exactly do you mean here? Pointers in RAM (SGA) that point to some other locations in RAM (SGA)? As this is what usually is understood as "pointers of the Mem Locs". And what has droping tables to do with SGA and pointers?
Or do you mean memory loactions that hold some addresses to certain storage (disk) locations?
Or do you mean storage (disk) loactions that hold some addresses to other storage (disk) locations?
Can you be more specific?
I mean POINTER to MEM Location as below...
DELETE ROW -> Data stored at Disk Level has Mem Location. This Mem Loction will be dropped by OS is wat i mean.
I dunno in detail about the Mem Allocation/De-allocation by OS, but i assume there will be a Pointer pointing
to the actual DATA Mem Loc ( Pointer to Pointer ).
Drop Table -> Now here comes a bit of Problem while droping Mem Locations...Problem is like at harddisk level the Contigious
Mem allocated ( Seeming ) will be so called Extent/BLOCK in oracle. If this command is issued oracle has to indentify
all such locations and be droped by OS.
Quote:
Originally posted by jmodic
Hm, and what do you think Oracle will do? Do some changes in SGA and that's it? You have some ideas about how transactions are processed in oracle, right? So you must know there must be something done at datafile/hard disk level, right? DROP table *is a transaction* and it can't be commited untill the changes are written to disks (redo log files at least) - is there any doubt about that?
Now of course DBWR will not try to zero-out every bit of the disk space table has occupied. But I have allready told you in previous reply what changes it will have to do to the datadictionary tables. And those changes ARE VERY PHISICAL, they must be performed in buffer cache and then they must be written to disks before the DROP TABLE can complete.
[/B]
Ya ofcource Data Dict tables have to be updated ( rather entry/row deleted )..
So lets say we need to delete N rows from N different BASE tables...it wont require much time to DROP mem loc...
It will be PHYSICAL only when INSERT or UPDATE on table takes place.
Well yes if many extents...it because..segment doesnt have header info...had it than it would have been drop of MEM loc to thisQuote:
Originally posted by jmodic
Well yes, if each of those tables would consist of hundreds or thousands of dictionary managed extents, then it actually takes days to drop the schema! Even if each table has only one extent it will take quite some time to drop schema with 10000 segments - have you tried something like this yet? I did. It certanly was taking much more than it would take Oracle to "drop some memory pointers".
[/B]
pointer rather than droping Pointer to all those extents.
If its DELETE it would be a PHYSICAL write of "0000..." to the loc where the row exist.Quote:
Originally posted by jmodic
modification has to be performed physicaly, on disks, no other way around this. Of course changes are made in buffer cache first [/B]
I hope i am very clear this time.
Abhay.
not sure if yu know what you are talking about, segments has headers (well otherwise where are the freelists stored)?
The steps Oracle process DMLs:
Processing DML
A data manipulation language (DML) statement requires only two phases of processing,
1. Parse is the same as the parse phase used for processing a query.
2. Execute requires additional processing to make data changes.
DML Execute Phase
To execute a DML statement:
1. If the data and rollback blocks are not already in the buffer cache, the server process reads them from the data files into the buffer cache.
2. The server process places locks on the rows that are to be modified. The rollback block is used to store the before-image of the data, so that the DML statements can be rolled back if necessary.
3. The data blocks record the new values of the data.
4. The server process records the before image to the undo block and updates the data block. Both of these changes are done in the database buffer cache. Any changed blocks in the buffer cache are marked as dirty buffers. That is, buffers that are not the same as the corresponding blocks on the disk.
The processing of a DELETE or INSERT command uses similar steps. The before image for a DELETE contains the column values in the deleted row, and the before image of an INSERT contains the row location information.
Are you assumming all or you know how Oracle works internally?
Abhay, i could not understand a single thing that you were trying to explain...
What are mem loc on disks ?? Never heard such term in relation to Oracle. Just found one link related to RAID5 when i searched for "Memory Location on Disk" and found no match for "mem loc on disk" :)Quote:
Data stored at Disk Level has Mem Location ??
And if im not wrong then in the second case extents that you have created are all empty extents. So whats the big deal in showing how fast you can drop them. That will be fast anyway. And BTW you dropped the tablespaces before dropping the user, so while dropping the user you were just removing the user entries from Data Dictionary tables.
PS: Didn't read the whole thread, its sooooo confusing, if i read it completly then i think ill loose my mind or loose my concepts. :)
No extents are FULL...in second case as well...but jus didnt include all that code beacuse while submiting it prompted for only 100000 letters or so to be used.Quote:
Originally posted by adewri
And if im not wrong then in the second case extents that you have created are all empty extents. So whats the big deal in showing how fast you can drop them. That will be fast anyway. And BTW you dropped the tablespaces before dropping the user, so while dropping the user you were just removing the user entries from Data Dictionary tables.
PS: Didn't read the whole thread, its sooooo confusing, if i read it completly then i think ill loose my mind or loose my concepts. :)
Well had you read compelete post, then you would have known, Why in second case i droped Tablespace to see good response.
Point is.. as Jurij Said...
While DROPING tables will fire all DICT BASE tables to be modified and will take considerable time.
I said, it aint take much...
So when i DROPED the TS including contents, all Tables will also needed to be dropped right ( i mean DICT tables should be updated )...
Well had you done with DROP USER than to it needs to DROP all the TABLES ( I mean Modify DICT tables )...
Compare the effective times is what i said.
Abhay.
Quote:
Originally posted by pando
not sure if yu know what you are talking about, segments has headers (well otherwise where are the freelists stored)?
The steps Oracle process DMLs:
Processing DML
A data manipulation language (DML) statement requires only two phases of processing,
1. Parse is the same as the parse phase used for processing a query.
2. Execute requires additional processing to make data changes.
DML Execute Phase
To execute a DML statement:
1. If the data and rollback blocks are not already in the buffer cache, the server process reads them from the data files into the buffer cache.
2. The server process places locks on the rows that are to be modified. The rollback block is used to store the before-image of the data, so that the DML statements can be rolled back if necessary.
3. The data blocks record the new values of the data.
4. The server process records the before image to the undo block and updates the data block. Both of these changes are done in the database buffer cache. Any changed blocks in the buffer cache are marked as dirty buffers. That is, buffers that are not the same as the corresponding blocks on the disk.
The processing of a DELETE or INSERT command uses similar steps. The before image for a DELETE contains the column values in the deleted row, and the before image of an INSERT contains the row location information.
Are you assumming all or you know how Oracle works internally?
This is all fine..and aint take much time is what i was trying to prove..( BASE table Modification )....but..forget it.
But dropping tablespaces is not an appropriate solution. A tablespace can host more than one schema objects.
So this solution would be specific.
Abhay,
I can understand what you are trying to say, but to drop a user or schema , I dont think dropping the corresponding tablespace would be the best solution taking into consideration different database environments.
Exactly..I also mentioned ( if you have read that LONG post )Quote:
Originally posted by adewri
But dropping tablespaces is not an appropriate solution. A tablespace can host more than one schema objects.
So this solution would be specific.
Set being Distinct for DIFFERNT USERS/SCHEMAS.Quote:
Rather if we would have DROPED the tablespace which contains these segments it would be much faster.
So one need to degin there DB as to have all USER objects in a set of Tablespaces.
I think you are getting me now..i mean what really i wanted to convey...I was being LOGICAL in my approach.
Abhay.
I am assuming it to be DW type DB.Quote:
Originally posted by thomasp
Abhay,
I can understand what you are trying to say, but to drop a user or schema , I dont think dropping the corresponding tablespace would be the best solution taking into consideration different database environments.
Ofcourse you cant have N set of TS for N users in OLTP type of DBs..Well in such DBs i dont think there will necessity of having 10000 objects for particular schema.
Abhay.
This is a peoplesoft db, unless I'm much mistaken.Quote:
Originally posted by abhaysk
I am assuming it to be DW type DB.
Ofcourse you cant have N set of TS for N users in OLTP type of DBs..Well in such DBs i dont think there will necessity of having 10000 objects for particular schema.
Abhay.
And why would dropping the tablespace be an improvement? You still have to drop the objects from the data dictionary, and then you have to recreate the TS anyway. I don't see the advantage.
Dropping a user with all those objects is going to take time. My advice is to just suck it up.
PS my posts on page 2.Quote:
Originally posted by slimdave
And why would dropping the tablespace be an improvement? You still have to drop the objects from the data dictionary, and then you have to recreate the TS anyway. I don't see the advantage.
abhaysk,
i retried your experiment with few modifications.
As you can see from the results:
METHOD 1:
METHOD 2:Quote:
SQL> drop user DROPING_USER cascade;
User dropped.
Elapsed: 00:00:49.02
SQL> drop tablespace DROP_USER;
Tablespace dropped.
Elapsed: 00:00:37.05
Both methods take same amount of time!!!Quote:
SQL> drop tablespace DROP_USER including contents;
Tablespace dropped.
Elapsed: 00:01:19.09
SQL> drop user DROPING_USER;
User dropped.
Elapsed: 00:00:08.03
Modifications used to even the experiments:
1. For both methods I used objects from you method 1 - the only difference from your method one is the dropping.
2. As you can see, in method 1 I included "drop tablespace" after dropping user to have the same job done - without this METHOD 1 IS EVEN FASTER!!!
3. Both times, after creating objects before dropping I restarted the database not to let caching to mess with my results.
Few other things:
- i was the only user on machine;
- it is solaris, not nt;
- INTERESTINGLY with your "inserting code" I got 3065 extents in TAB101, not 800.
:confused:
I think you have to give more consideration in creating the same environment for experiments you do.