SQL> drop tablespace roger including contents and datafiles;
drop tablespace roger including contents and datafiles
*
ERROR at line 1:
ORA-01561: failed to remove all objects in the tablespace specified
SQL> select owner,segment_name,segment_type
2 from dba_segments
3 where tablespace_name='ROGER';
OWNER SEGMENT_NAME SEGMENT_TYPE
------------ ------------------ ------------------
ROGER 5.155 TEMPORARY
ROGER 5.163 TEMPORARY
ROGER 5.171 TEMPORARY
ROGER 5.179 TEMPORARY
ROGER 5.187 TEMPORARY
SQL> select owner,segment_name,segment_type,HEADER_FILE,HEADER_BLOCK,BYTES/1024/1024
2 from dba_segments
3 where owner='ROGER';
OWNER SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK BYTES/1024/1024
------- --------------- -------------- ----------- ------------ ---------------
ROGER 5.155 TEMPORARY 5 155 2
ROGER 5.163 TEMPORARY 5 163 2
ROGER 5.171 TEMPORARY 5 171 2
ROGER 5.179 TEMPORARY 5 179 .75
ROGER 5.187 TEMPORARY 5 187 .75
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> select ts# from ts$ where name='ROGER';
TS#
----------
6
SQL> update seg$ set type# = 3 where ts#=6;
7 rows updated.
SQL> commit;
Commit complete.
SQL> select file#,block#,TYPE# from seg$ where ts#=6;
FILE# BLOCK# TYPE#
---------- ---------- ----------
5 155 3
5 163 3
5 171 3
5 179 3
5 187 3
5 1371 3
5 1379 3
7 rows selected.
SQL> drop tablespace roger including contents and datafiles;
Tablespace dropped.
SQL> select ts#,name,BLOCKSIZE,FLAGS from ts$ order by 1;
TS# NAME BLOCKSIZE FLAGS
---------- ------------------------------ ---------- ----------
0 SYSTEM 8192 1
1 UNDOTBS1 8192 17
2 SYSAUX 8192 33
3 TEMP 8192 2
4 USERS 8192 33
5 UNDOTBS2 8192 17
6 ROGER 8192 33
7 rows selected.
SQL> select file#,block#,TYPE# from seg$ where ts#=6;
no rows selected
SQL> delete from ts$ where ts#=6;
1 row deleted.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ----------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> delete from ts$ where ts#=5;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select ts#,name,BLOCKSIZE,FLAGS from ts$ order by 1;
TS# NAME BLOCKSIZE FLAGS
---------- ------------------------------ ---------- ----------
0 SYSTEM 8192 1
1 UNDOTBS1 8192 17
2 SYSAUX 8192 33
3 TEMP 8192 2
4 USERS 8192 33
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 230686720 bytes
Fixed Size 1266776 bytes
Variable Size 150997928 bytes
Database Buffers 75497472 bytes
Redo Buffers 2924544 bytes
Database mounted.
Database opened.
SQL> select file#,STATUS,CHECKPOINT_CHANGE#,ONLINE_CHANGE# from v$datafile;
FILE# STATUS CHECKPOINT_CHANGE# ONLINE_CHANGE#
---------- ------- ------------------ --------------
1 SYSTEM 708092 464631
2 ONLINE 708092 464631
3 ONLINE 708092 464631
4 ONLINE 708092 464631
SQL> select username,account_status,default_tablespace
2 from dba_users
3 where username='ROGER';
no rows selected
SQL> select username from dba_users where username like '%ROGER%';
no rows selected
###### 我们发现roger表空间删除一行，roger用户也跟着被删除了。######
关于drop表后，会变成临时表，这个应该是11gR2的新功能，在去年的一篇文章中，我写过了。
大家可以参考这里，
http://hi.baidu.com/xu521huan/blog/item/29cd893db302f7fe3d6d97ba.html
但是我这里的环境是10204，居然会有这个情况，让我有点费解，，这个问题回头还需要研究一下。
最后再用oracle提供的检查数据字典的脚本检查一下，看看是否有数据字典不一致的情况
SQL> show user
USER is "SYS"
SQL> execute hcheck.full
PL/SQL procedure successfully completed. ###### 看来是ok了 ######
SQL> create tablespace roger datafile '/oracle/product/oradata/roger/rogre01.dbf' size 200m autoextend off;
Tablespace created.
SQL> create user roger identified by roger default tablespace roger;
create user roger identified by roger default tablespace roger
*
ERROR at line 1:
ORA-01920: user name 'ROGER' conflicts with another user or role name
SQL> drop user ROGER cascade;
drop user ROGER cascade
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktadrprc-1], [], [], [], [], [], [], []
看了下alert log，对于的600错误trace内容如下：
*** ACTION NAME:() 2011-07-03 13:48:26.295
*** MODULE NAME:(sqlplus@roger (TNS V1-V3)) 2011-07-03 13:48:26.295
*** SERVICE NAME:(SYS$USERS) 2011-07-03 13:48:26.295
*** SESSION ID:(159.3) 2011-07-03 13:48:26.295
kwqmnich: current time:: 5: 48: 26
kwqmnich: instance no 0 check_only flag 1
kwqmnich: initialized job cache structure
*** 2011-07-03 13:51:25.349
+++++++++++++++++++++++++++++++++++++++++++++++++
HCheck Version 8i-11/2.00
Problem: OBJ$ INDEX entry has no IND$ entry
INDEX has no IND$ entry: Obj=51829 ROGER.IDX_A
Problem: OBJ$ TABLE entry has no TAB$ entry
TABLE has no TAB$ entry: Obj=51828 ROGER.TEST1
+++++++++++++++++++++++++++++++++++++++++++++++++
Found 2 potential problems and 0 warnings
Contact Oracle Support with the output
to check if the above needs attention or not
*** 2011-07-03 13:54:52.872
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [ktadrprc-1], [], [], [], [], [], [], []
Current SQL statement for this session:
drop table "ROGER"."TEST1" cascade constraints purge force
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst()+27 call ksedst1() 0 ? 1 ?
ksedmp()+557 call ksedst() 0 ? CE67960 ? 1F ? B712C6FC ?
BFFF5B7C ? 8D02CED ?
ksfdmp()+19 call ksedmp() 3 ? BFFF5CE0 ? ADC338D ?
CE67960 ? 3 ? CE15B3C ?
kgerinv()+177 call 00000000 CE67960 ? 3 ?
kgeasnmierr()+40 call kgerinv() CE67960 ? B7120020 ?
C38DEE0 ? 0 ? BFFF5D18 ?
ktadrprc()+235 call kgeasnmierr() CE67960 ? B7120020 ?
C38DEE0 ? 0 ?
ktssdrp_segment()+1 call ktadrprc() BFFF7004 ? 0 ?
583
dtbdrp()+1462 call ktssdrp_segment() BFFF7004 ? 0 ? 0 ? 0 ?
CE6871C ? 0 ?
dtbdrv()+2235 call dtbdrp() B70EFE44 ? 2A2A05B8 ?
2A2A01D8 ? BFFF72D0 ? 0 ?
BFFF72EC ? 2A4455A0 ? 3 ?
opiexe()+11173 call dtbdrv() B70F0C84 ? 0 ? C ? ADA884D ?
B70F0C84 ? 0 ?
opiosq0()+2701 call opiexe() 4 ? 0 ? BFFF7FA0 ?
opiosq()+19 call opiosq0() 3 ? F ? BFFF8DF4 ? 0 ?
opiodr()+976 call 00000000 4A ? F ? BFFF8DF4 ?
__PGOSF112_rpidrus( call opiodr() 4A ? F ? BFFF8DF4 ? 1F ?
)+159
skgmstack()+139 call 00000000 BFFF8850 ? BFFF8534 ?
CE67A5C ? BFFF8D64 ?
BFFF8834 ? F618 ?
rpidru()+98 call skgmstack() BFFF8834 ? CE67720 ? F618 ?
99B163E ? BFFF8850 ?
rpiswu2()+334 call 00000000 BFFF8D64 ? CE67A5C ?
BFFF8DE0 ? BFFF88B0 ? F618 ?
BFFF88CC ?
rpidrv()+1199 call rpiswu2() 2DB0AAC8 ? 0 ? BFFF8D44 ? 2 ?
BFFF8D90 ? 0 ? BFFF8D44 ? 0 ?
99B195A ? 99B1A48 ?
BFFF8D64 ? 8 ?
rpisplu()+286 call rpidrv() 1F ? 4A ? BFFF8DF4 ? 8 ? 1 ?
1F ?
rpispl()+28 call rpisplu() 1F ? 0 ? 0 ? BFFFA858 ? 3A ?
0 ? 0 ?
kzdukl()+10458 call rpispl() 1F ? 0 ? BFFFA858 ? 3A ? 0 ?
0 ?
kzudrp()+921 call kzdukl() B6F9EB70 ? BFFFBAF0 ?
B7136A44 ? FFFF0041 ?
B6F9EB4C ? B7127680 ?
opiexe()+13175 call kzudrp() B6F9EB68 ? B6F9FFEC ? 0 ? C ?
ADA884D ? B6F9FFEC ?
opiosq0()+2701 call opiexe() 4 ? 0 ? BFFFC090 ?
kpooprx()+215 call opiosq0() 3 ? E ? BFFFC18C ? A4 ?
kpoal8()+673 call kpooprx() BFFFEC74 ? BFFFCF00 ? 17 ?
1 ? 0 ? A4 ?
opiodr()+976 call 00000000 5E ? 17 ? BFFFEC70 ?
ttcpip()+1085 call 00000000 5E ? 17 ? BFFFEC70 ? 0 ?
opitsk()+1054 call ttcpip() CE6F180 ? 5E ? BFFFEC70 ? 0 ?
BFFFE950 ? BFFFED80 ?
opiino()+821 call opitsk() 0 ? 0 ?
opiodr()+976 call 00000000 3C ? 4 ? BFFFF840 ?
opidrv()+466 call opiodr() 3C ? 4 ? BFFFF840 ? 0 ?
sou2o()+91 call opidrv() 3C ? 4 ? BFFFF840 ?
opimai_real()+117 call sou2o() BFFFF824 ? 3C ? 4 ?
BFFFF840 ?
main()+111 call opimai_real() 2 ? BFFFF870 ?
__libc_start_main() call 00000000 2 ? BFFFF934 ? BFFFF940 ?
+211 252C66 ? B742CFF4 ? 0 ?
###### 这里非常的关键 ######
HCheck Version 8i-11/2.00
Problem: OBJ$ INDEX entry has no IND$ entry
INDEX has no IND$ entry: Obj=51829 ROGER.IDX_A
Problem: OBJ$ TABLE entry has no TAB$ entry
TABLE has no TAB$ entry: Obj=51828 ROGER.TEST1
SQL> select * from IND$ where obj#=51829;
no rows selected
SQL> select count(*) from obj$ where obj#=51829;
COUNT(*)
----------
1
SQL> select count(*) from TAB$ where obj#=51828;
COUNT(*)
----------
0
SQL> select count(*) from obj$ where obj#=51828;
COUNT(*)
----------
1
看来是数据字典不一致了，obj 51829 在obj$中存在，但是在ind$不存在，而obj 51828在obj$存在，在tab$中不存在。 这里处理的方式就有2种： 1. 由于是测试库，我完全可以把obj$中的51829 51828 这2条进行给删除就行了。 2. 由于是ind$和tab$中的数据没有，而在obj$存在，那么我们可以手工补齐ind$,tab$中的信息。 关于第1种方法，比较简单，我就不多说了；由于前面我想办法把原来的roger表空间给drop了， 这里就很难再重构了。不过总的来说，其实要想手工补齐ind$,tab$并不困难的，因为其结构如下：
create table ind$ /* index table */
( obj# number not null, /* object number */
/* DO NOT CREATE INDEX ON DATAOBJ# AS IT WILL BE UPDATED IN A SPACE
* TRANSACTION DURING TRUNCATE */
dataobj# number, /* data layer object number */
ts# number not null, /* tablespace number */
file# number not null, /* segment header file number */
block# number not null, /* segment header block number */
bo# number not null, /* object number of base table */
indmethod# number not null, /* object # for cooperative index method */
cols number not null, /* number of columns */
pctfree$ number not null, /* minimum free space percentage in a block */
initrans number not null, /* initial number of transaction */
maxtrans number not null, /* maximum number of transaction */
pctthres$ number, /* iot overflow threshold, null if not iot */
type# number not null, /* what kind of index is this? */
/* normal : 1 */
/* bitmap : 2 */
/* cluster : 3 */
/* iot - top : 4 */
/* iot - nested : 5 */
/* secondary : 6 */
/* ansi : 7 */
/* lob : 8 */
/* cooperative index method : 9 */
flags number not null,
/* mutable flags: anything permanent should go into property */
/* unusable (dls) : 0x01 */
/* analyzed : 0x02 */
/* no logging : 0x04 */
/* index is currently being built : 0x08 */
/* index creation was incomplete : 0x10 */
/* key compression enabled : 0x20 */
/* user-specified stats : 0x40 */
/* secondary index on IOT : 0x80 */
/* index is being online built : 0x100 */
/* index is being online rebuilt : 0x200 */
/* index is disabled : 0x400 */
/* global stats : 0x800 */
/* fake index(internal) : 0x1000 */
/* index on UROWID column(s) : 0x2000 */
/* index with large key : 0x4000 */
/* move partitioned rows in base table : 0x8000 */
/* index usage monitoring enabled : 0x10000 */
/* 4 bits reserved for bitmap index version : 0x1E0000 */
property number not null, /* immutable flags for life of the index */
/* unique : 0x01 */
/* partitioned : 0x02 */
/* reverse : 0x04 */
/* compressed : 0x08 */
/* functional : 0x10 */
/* temporary table index: 0x20 */
/* session-specific temporary table index: 0x40 */
/* index on embedded adt: 0x80 */
/* user said to check max length at runtime: 0x0100 */
/* domain index on IOT: 0x0200 */
/* join index : 0x0400 */
/* functional index expr contains a PL/SQL function : 0x0800 */
/* The index was created by a constraint : 0x1000 */
/* The index was created by create MV : 0x2000 */
/* The following columns are used for index statistics such
* as # btree levels, # btree leaf blocks, # distinct keys,
* # distinct values of first key column, average # leaf blocks per key,
* clustering info, and # blocks in index segment.
*/
blevel number, /* btree level */
leafcnt number, /* # of leaf blocks */
distkey number, /* # distinct keys */
lblkkey number, /* avg # of leaf blocks/key */
dblkkey number, /* avg # of data blocks/key */
clufac number, /* clustering factor */
analyzetime date, /* timestamp when last analyzed */
samplesize number, /* number of rows sampled by Analyze */
rowcnt number, /* number of rows in the index */
intcols number not null, /* number of internal columns */
/* The following two columns are only valid for partitioned indexes */
/*
* Legal values for degree, instances:
* NULL (used to represent 1 on disk/dictionary and implies noparallel), or
* 2 thru EB2MAXVAL-1 (user supplied values), or
* EB2MAXVAL (implies use default value)
*/
degree number, /* number of parallel query slaves per instance */
instances number, /* number of OPS instances for parallel query */
trunccnt number, /* re-used for iots 'inclcol' */
spare1 number, /* number of columns depended on, >= intcols */
spare2 number, /* number of key columns in compressed prefix */
spare3 number,
spare4 varchar2(1000), /* used for parameter str for domain idx */
spare5 varchar2(1000),
spare6 date /* flashback timestamp */
)
create table tab$ /* table table */
( obj# number not null, /* object number */
/* DO NOT CREATE INDEX ON DATAOBJ# AS IT WILL BE UPDATED IN A SPACE
* TRANSACTION DURING TRUNCATE */
dataobj# number, /* data layer object number */
ts# number not null, /* tablespace number */
file# number not null, /* segment header file number */
block# number not null, /* segment header block number */
bobj# number, /* base object number (cluster / iot) */
tab# number, /* table number in cluster, NULL if not clustered */
cols number not null, /* number of columns */
clucols number,/* number of clustered columns, NULL if not clustered */
pctfree$ number not null, /* minimum free space percentage in a block */
pctused$ number not null, /* minimum used space percentage in a block */
initrans number not null, /* initial number of transaction */
maxtrans number not null, /* maximum number of transaction */
flags number not null, /* 0x00 = unmodified since last backup
0x01 = modified since then
0x02 = DML locks restricted to <= SX
0x04 = DML locks <= SX not acquired
0x08 = CACHE
0x10 = table has been analyzed
0x20 = table has no logging
0x40 = 7.3 -> 8.0 data object
migration required
0x0080 = current summary dependency
0x0100 = user-specified stats
0x0200 = global stats
0x0800 = table has security policy
0x020000 = Move Partitioned Rows
0x0400000 = table has sub tables
0x00800000 = row dependencies enabled */
/* 0x10000000 = this IOT has a physical rowid mapping table */
/* 0x20000000 = mapping table of an IOT(with physical rowid) */
audit$ varchar2("S_OPFL") not null, /* auditing options */
rowcnt number, /* number of rows */
blkcnt number, /* number of blocks */
empcnt number, /* number of empty blocks */
avgspc number, /* average available free space/iot ovfl stats */
chncnt number, /* number of chained rows */
avgrln number, /* average row length */
avgspc_flb number, /* avg avail free space of blocks on free list */
flbcnt number, /* free list block count */
analyzetime date, /* timestamp when last analyzed */
samplesize number, /* number of rows sampled by Analyze */
/*
* Legal values for degree, instances:
* NULL (used to represent 1 on disk/dictionary and implies noparallel), or
* 2 thru EB2MAXVAL-1 (user supplied values), or
* EB2MAXVAL (implies use default value)
*/
degree number, /* number of parallel query slaves per instance */
instances number, /* number of OPS instances for parallel query */
/* => the number of dictionary columns => the number of columns
* that have dictionary meta-data associated with them. This is a superset of
* and .
* = +
*/
intcols number not null, /* number of internal columns */
/* => the number of REAL columns (ie) columns that actually
* store data.
*/
kernelcols number not null, /* number of REAL (kernel) columns */
property number not null, /* table properties (bit flags): */
/* 0x01 = typed table, 0x02 = has ADT columns, */
/* 0x04 = has nested-TABLE columns, 0x08 = has REF columns, */
/* 0x10 = has array columns, 0x20 = partitioned table, */
/* 0x40 = index-only table (IOT), 0x80 = IOT w/ row OVerflow, */
/* 0x100 = IOT w/ row CLustering, 0x200 = IOT OVeRflow segment, */
/* 0x400 = clustered table, 0x800 = has internal LOB columns, */
/* 0x1000 = has primary key-based OID$ column, 0x2000 = nested table */
/* 0x4000 = View is Read Only, 0x8000 = has FILE columns */
/* 0x10000 = obj view's OID is system-gen, 0x20000 = used as AQ table */
/* 0x40000 = has user-defined lob columns */
/* 0x00080000 = table contains unused columns */
/* 0x100000 = has an on-commit materialized view */
/* 0x200000 = has system-generated column names */
/* 0x00400000 = global temporary table */
/* 0x00800000 = session-specific temporary table */
/* 0x08000000 = table is a sub table */
/* 0x20000000 = pdml itl invariant */
/* 0x80000000 = table is external */
trigflag number, /* first two bytes for trigger flags, the rest for */
/* general use, check tflags_kqldtvc in kqld.h for detail */
/* 0x00000001 deferred RPC Queue */
/* 0x00000002 snapshot log */
/* 0x00000004 updatable snapshot log */
/* 0x00000008 = context trigger */
/* 0x00000010 = synchronous change table */
/* 0x00000080 = audit vault trigger */
/* 0x00010000 = server-held key encrypted columns exist */
/* 0x00020000 = user-held key encrypted columns exist */
/* 0x00200000 = table had rowmovement */
/* 0x00400000 = lobs use shared segment */
/* 0x00800000 = queue table */
/* 0x10000000 = streams unsupported table */
/* enabled at some point in past */
spare1 number, /* used to store hakan_kqldtvc */
spare2 number, /* committed partition # used by drop column */
spare3 number, /* summary sequence number */
spare4 varchar2(1000), /* committed RID used by drop column */
spare5 varchar2(1000), /* summary related information on table */
spare6 date /* flashback timestamp */
)
SQL> delete from obj$ where obj# in('51828','51829');
2 rows deleted.
SQL> commit;
Commit complete.
SQL> drop user ROGER cascade;
User dropped.
ok了，这个小case就先到这，如果是生产库的话，我们可以还可以借助ODU来进行处理， 回头会再写一篇更加详细文章来模拟一下关于数据字典不一致的处理，多谢大家的关注！
发表回复