HI,


For solution is necessary extra space DBMS_REDEFINITION.
The scenary no more space leaf on tablespace and diskgroup.
If not possible monitoring process is problem for us?
We are blind regarding execution where it causes problems if there is a cancellation of the drop in the middle of the path.

Thank's,


Bruno H Isomura.

Hi, Crhis!


You what now?
Re: Not now.

The problem solved in waiting executing process drop column, time executing about 10 hours.
We stayed with table locked for 10 hours! The more impact went to team system, where the application was unavailable.
I was looking for a more efficient way for the activity, as they had not planned, we were left with this situation without any contour plane.
I understand that DBMS_REDEFITION for this situation is the best, but we fell into the scenario and we were sold, because there was no way to monitor the weather or improve the time.
As the table had already started the drop process the object was locked in the data dictionary.
If not exist other form monitoring process and contour plane, patience.
Thanks for help and atention!

BR,

Bruno H. Isomura.

Hi, Bruno : first things first, let me try to clarify V$SESSION_LONGOPS' meaning.... See, to 'run' a given SQL, the RDBMS may need to execute many operations, such as read an index, create a hash table, create temporary data, read data blocks, sort data, etc, etc... V$SESSION_LONGOPS do NOT register the duration of the SQL, BUT the duration of any and all ** LONG Operations ** that a given SQL did - LONG here is a operation taking more than a few seconds.
The two possible missing points for V$SESSION_LONGOPS, thus, can be :

a. if a given SQL is taking a long time to execute due to a myriad of small operations (reading a ton of data blocks,say, but one block a time) this will NOT be registered in V$SESSION_LONGOPS, becuase each operation (each block read, in my example) takes only a fraction of a second

b. v$session_longops register ** Operations Being Done ** : if the session is doing nothing, ie, is WAITING for something (waiting for RDBMS inner works such as gererate, write/read logs and archive them, waiting for locks, waiting for other sessions reading/using the data blocks in question, etc) this will NOT BE REGISTERED inside V$SESSION_LONGOPS

Back to your case : if you are seeing nothing in V$SESSION_LONGOPS, much probably the session doing your 'ALTER TABLE DROP COLUMN' is WAITING for something OR doing an awful lot of small operations... To monitor and see what is occurring :

1. use a lock-waiting script : search in My Oracle Support/Metalink for 'lock script', you will find many good ones

2. query frequently the V$ that register the WAITs and ACTIONs of the session doing the DROP and the internal RDBMS activities : among others, see V$ACCESS, V$SESSION, V$SESSION_WAIT, V$SESSTAT, v$SYSSTAT ...

3. do a TRACE of the session doing the ALTER TABLE : with this, a trace file will be generated in the server, and this trace file will show to you what the session is doing, in details

Regards,

J. Laurindo Chiappa

P.S. :

see, drop columns IS a very COMPLEX operation : column names are referred in so many places inside the data dictionary, the column can be in use/referred by other objects... And in top of that, a LARGE portion of redo/undo can be generated... See https://www.red-gate.com/simple-talk/sql/oracle/dropping-columns/ for an example...
So, if your table is large AND is being actively used, please do a favor to yourself and ADD SOME SPACE in this database, allowing you to use DBMS_REDEFINITION... You will not regret this decision...

Ops! Talk about bad timing : just saw that your problem is solved...
Well, anyway next time you will have some options to try in the sense of monitoring the execution and get some explanations for the time elapsed beside querying V$SESSION_LONGOPS ...

Best regards,

J. Laurindo Chiappa

Hi, José.

Thanks for the overview, but for the past information already known.
What was of doubt, there was a scenario that is NO possibility estimate time remaings , the problem was already past and the situation does not exist rollback.

1. If stop the executing DROP COLUMN provide error.
2. If CONTINUE the executing provide LOCK in dictionary. LIBRARY CACHE LOCK.

In the excerpt:

"He was accompanying by the amount of time he did by block rows commit..."

In the end, system team learned that they should have a DBA appraisal before taking certain actions,rsrsrs.

In this situation, what you make?
Thank's,

Bruno H. Isomura.

Hi, Bruno, answers in-line :

"What was of doubt, there was a scenario that is NO possibility estimate time remaings..."

See, as I said the RDBMS is capable to give to us an estimatation only for LONG RUNNING operations : for (say) DML locks, it is Impossible for the RDBMS to give us ANY approximation/estimation of the time remaining for the release of a given lock, this is governed by the OTHER transaction who is the Owner of the lock - in Oracle RDBMS, a session waiting for a lock will wait UNTIL the session who 'owns' the lock closes the Transaction with a COMMIT or a ROLLBACK, thus releasing the lock ...

"In this situation" (a situation where the RDBMS is unable to give to us an Estimation because the control is outside the database itselfs) ", what you make?"

First thing, I would do the same thing as you said, ie : demand the presence of a DBA in ** any ** non-routine operation that could lead to long waits, be any DDL in big/frequently accessed tables or whatever....

Second thing, I ** will *** have some kind of MONITORING in my databases, and this monitoring WILL warn me about sessions suffering from long waits for locks, SQLs suffering from long/unusual durations, database doing internal activities (log or undo processing/generation, log archiving, block cleaning, whatever) in a non-usual frequency, long-running transactions, disk space shortage, doing an awful lot of small I/Os (what, as I said, are not registered in V$SESSION_LONGOPS)...
So, in your case, let´s say that your duhvelopers (against your command) started (without the help from you, the DBA) some DDL (or anything like that) in a big and intensely used table and thus they are suffering from long lock waiting : your database monitoring solution will WARN you and you could act in and kill the lock´s owner session, or something...

Regards,

J. Laurindo Chiappa