ALL_PARTIAL_DROP_TABS dba_unused_col_tabs

ALL_PARTIAL_DROP_TABS describes tables accessible to the current user that have partially completed DROP COLUMN operations. Such operations might have been interrupted by the user or by a system crash.

Related Views

• DBA_PARTIAL_DROP_TABS describes all tables in the database that have partially completed DROP COLUMN operations.

• USER_PARTIAL_DROP_TABS describes tables in the schema of the current user that have partially completed DROP COLUMN operations. This view does not display the OWNER column.

Column	Datatype	NULL	Description
OWNER	VARCHAR2(30)	NOT NULL	Owner of the object
TABLE_NAME	VARCHAR2(30)	NOT NULL	Name of the table

 

---------------------------------------------------------------------------------------------------------------------------

怎样才能查找被set unused 的column

在哪个视图中可以找到set unused 的column的具体的列名？？

试试如下的视图，
查表是否有unused column,可以查出有几列
SQL> desc dba_unused_col_tabs
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
OWNER                                     NOT NULL VARCHAR2(30)
TABLE_NAME                                NOT NULL VARCHAR2(30)
COUNT                                              NUMBER

可以查出是否有没有完成的操作
SQL> desc dba_partial_drop_tabs;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
OWNER                                     NOT NULL VARCHAR2(30)
TABLE_NAME                                NOT NULL VARCHAR2(30)

具体的列名查不到，关键是Oracle会将基表中的信息改变，如下所示

SQL> conn system/manager
Connected.
SQL>
SQL> create table system_t(a number,b number);

Table created.

SQL>
SQL> conn / as sysdba
Connected.
SQL>
SQL> select obj#
  2  from   obj$
  3  where  name='SYSTEM_T';

      OBJ#
----------
     42574

SQL>
SQL> select name from col$ where obj#=42574;

NAME
------------------------------
A
B

SQL>
SQL> select * from dba_unused_col_tabs;

no rows selected

SQL>
SQL> select * from dba_partial_drop_tabs;

no rows selected

SQL>
SQL> conn system/manager
Connected.
SQL>
SQL> alter session set nls_date_format='yyyy-mm-dd hh:mi:ss';

Session altered.

SQL>
SQL> select sysdate from dual;

SYSDATE
-------------------
2004-05-13 10:28:08

SQL>
SQL> ALTER TABLE system_t
  2  SET UNUSED COLUMN b;

Table altered.

SQL>
SQL> conn / as sysdba
Connected.
SQL>
SQL> select * from dba_unused_col_tabs;

OWNER                          TABLE_NAME                          COUNT
------------------------------ ------------------------------ ----------
SYSTEM                         SYSTEM_T                                1

SQL>
SQL> select * from dba_partial_drop_tabs;

no rows selected

SQL>
SQL> select name from col$ where obj#=42574;

NAME
------------------------------
A
SYS_C00002_04051310:28:08$

SQL>
SQL> desc system.system_t
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
A                                                  NUMBER

SQL>
SQL> drop table system.system_t;

Table dropped.

SQL>

from:http://www.itpub.net/thread-221937-1-1.html