Oracle 不能关闭报错： XXX datafile is in use
select b.sid ,b.* from v$datafile a ,v$lock b where a.ts# =b.id1 and a.FILE#=xxx;
Purpose
To provide information about the LOCK: TS "Temporary Segment (also TableSpace)"
Details
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Lock: TS "Temporary Segment (also TableSpace)"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
This lock has different arguments in Oracle7 and Oracle8.
Oracle8 and higher
~~~~~~~~~~~~~~~
The TS lock either protects a temporary segment or a bitmap tablespace.
The temporary segment may be either for true TEMP usage or may be
a segment which was once, or will become, a real segment.
Eg: DROP table converts the TABLE segment to a TEMP segment which is
then cleaned up.
TS-id1-id2 arguments
~~~~~~~~~~~~~~~~~~~~
Id1 Id2 Use
~~~ ~~~ ~~~
Tablespace id Relative DBA TS lock protecting the segment
(TS$.TS#) in this tablespace at this RDBA.
(See <SupTool:ODBA> to convert
an RDBA to a File/Block combination)
Tablespace id Instance ID If ID2 is small it is an instance ID ---inst_id
This is used for bitmapped
temporary tablespaces.
Useful SQL
~~~~~~~~~~
Tablespace name:
SELECT name FROM sys.ts$ where TS# = &ID1;
File / Block:
Use <SupTool:ODBA> on ID2 to get the relative file/block
Holders:
SELECT * from V$LOCK where type='TS' and lmode>0;
Waiters:
SELECT * from V$LOCK where type='TS' and request>0;
Notes
~~~~~
TS lock related issues can be caused by SMON spending a long time
performing TEMP segment cleanup. See Note:61997.1.
In RAC, "enq: TS - contention' issues are related to instances stealing each others temp space
due to unbalanced temp consumption. See Enhancement Request bug:9128656.
Solutions to this are reduce temp spill or assign temp TS to each instance/query.
Related
~~~~~~~
SMON - Temporary Segment Cleanup and Free Space Coalescing Note.61997.1
How To Efficiently Drop A Table With Many Extents Note.68836.1
~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
Oracle7
~~~~~~~
Temporary segment locks are used for two main purposes in Oracle7.
The first use is to serialize the "high water mark" or where the
highest allocated point of the segment is. The second is used to
serialize creation, use, and deletion of a temp segment.
The tablespace enqueue is acquired either when dropping that tablespace
or when creating a rollback segment in it. The purpose is to avoid
deadlocks that can occur on resource in the row cache (dictionary cache).
The TS enqueue is also used for SORT SEGMENT HANDLES in TEMPORARY
tablespaces in 7.3 and higher.
TS-id1-id2 arguments
~~~~~~~~~~~~~~~~~~~~
Id1 Id2 Use
~~~ ~~~ ~~~
segment dba 0 Create, use, delete temp segment
segment dba 1 Serialize access to the "high water mark"
tablespace# 2 prevent deadlock during create rollback
segment and create tablespace.
Related
~~~~~~~
Parameter:BUMP_HIGHWATER_MARK_COUNT (Oracle 7.3).
特殊临时段
Purpose
This note describes why a user process can consume large amounts of CPU
after dropping a table consisting of many extents, and a potential
workaround to stop the problem occurring. Essentially the CPU is being
used to manipulate the extents i.e. moving used extents (uet$) to free
extents (fet$). In certain circumstances it may be possible to regulate
this CPU activity.
Scope
This article is intended to assist DBAs who may need to drop a table consisting of many extents.
Details
Permanent object cleanup
If a permanent object (table) is made up of many extents, and the object is
to be dropped, the user process dropping the object will consume large
amounts of CPU - this is an inescapable fact. However, with some forethought
it is possible to mitigate the effects of CPU usage (and hence the knock-on
effect on other users of system resources) thus:
1. Identify, but do NOT drop the table
2. Truncate the table, specifying the REUSE STORAGE clause. This will be
quick as extents are not deallocated; the highwater mark is simply
adjusted to the segment header block.
3. Deallocate unused extents from the table, SPECIFYING THE KEEP CLAUSE.
This is the crux - you can control how many extents are to be deallocated
by specifying how much (in terms of Kb or Mb) of the table is NOT
to be deallocated.
Example:
o. Table BIGTAB is 2Gb in size and consists of 262144 8Kb extents
o. There is little CPU power available, and (from past experience) it is known that dropping an object of this number of extents can take days
o. The system is quiet at night times (no other users or batch jobs)
In the above example the table could be dropped in 'phases' over the period
of a few nights as follows:
1. Truncate the table, specifying the REUSE STORAGE clause:
SQL> TRUNCATE TABLE BIGTAB REUSE STORAGE;
2. If it takes 3 days (72 hours) to drop the table, spread this out over 6 nights i.e. drop 1/3 Gb per night. This can be achieved in 6 (nightly)
steps as follows:
Night 1:
SQL> ALTER TABLE BIGTAB DEALLOCATE UNUSED KEEP 1707M; (2Gb*5/6)
Night 2:
SQL> ALTER TABLE BIGTAB DEALLOCATE UNUSED KEEP 1365M; (2Gb*4/6)
Night 3:
SQL> ALTER TABLE BIGTAB DEALLOCATE UNUSED KEEP 1024M; (2Gb*3/6)
Night 4:
SQL> ALTER TABLE BIGTAB DEALLOCATE UNUSED KEEP 683M; (2Gb*2/6)
Night 5:
SQL> ALTER TABLE BIGTAB DEALLOCATE UNUSED KEEP 341M; (2Gb*1/6)
Night 6:
SQL> DROP TABLE BIGTAB;
NOTE:
If the table only needed truncating, no drop statement is needed here.
The same method can be applied if LOB segments or indexes are involved.
SQL> ALTER TABLE MODIFY LOB () DEALLOCATE UNUSED KEEP M;
SQL> ALTER INDEX DEALLOCATE UNUSED KEEP M;
Caveats
o. If you have inadvertently tried to drop the table, this method will
not work. This is because the drop will first convert the segment to
a temporary segment, and only then start cleaning up the now temporary
segment's extents. Thus, if the drop is interrupted, the temporary
segment will now be cleaned up by SMON.
o. This method will only work for table, lob and index segment types.
o. This method will not work for segments bigger than 4gb in size due to
unpublished bug:
1190939 -- ORA-3277 WHEN ISSUING AN ALTER TABLE DEALLOCATE UNUSED > 4G (fixed in 10g and higher)
worked for indexes as per SR#18121218.6[This section is not visible to customers.]
RELATED DOCUMENTS
Note:61997.1 SMON - Temporary Segment Cleanup and Free Space Coalescing
----实际应用
Symptoms
Dropping temporary tablespace sometimes hangs up as bellow.
SQL> SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES
2 WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
PROPERTY_VALUE
------------------------------------------------------------
TEMP
SQL> CREATE TEMPORARY TABLESPACE TEMPDUMMY TEMPFILE 'tempdummy01.dbf' SIZE 200M;
Tablespace created.
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPDUMMY;
Database altered.
SQL> DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;
--> This drop statement hangs up
This user process is waiting for TS enqueue which is held by SMON.
SQL> SELECT SID,ID1,ID2,LMODE,REQUEST FROM V$LOCK WHERE TYPE='TS';
SID ID1 ID2 LMODE REQUEST
---------- ---------- ---------- ---------- ----------
977 3 0 6 0
977 3 1 0 6
978 3 1 3 0
978 6 1 3 0
SQL> SELECT PROGRAM FROM V$SESSION WHERE SID=978;
PROGRAM
------------------------------------------------
oracle@xxxxx.xx.oracle.com (SMON) 在做清理
Cause
This is because temporary segment in TEMP tablespace is allocated by some process(es) and this area is protected by TS enqueue.
SQL> SELECT TABLESPACE FROM V$SORT_USAGE;
TABLESPACE
------------------------------
TEMP
Solution
You can avoid this problem by restarting DB after changing default temporary tablespace, and then drop the old temporary tablespace.