删除System下的非系统对象，比如表什么的，高手进，小弟在线急等！！！

Question

在oarcle里导入数据库时，小弟不慎把数据库导入到System用户下了，请问怎样把导入的内容删除呀，注意不要把系统的表或对象给删除了！！！

Answer 1

select * from all_objects where to_char(created,'YYYY-MM-DD')='2010-01-23' and owner='SYSTEM';
SQL> desc all_objects;
 名称                                                           是否为空? 类型
 -------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------- OWNER --用户模式（这里有SYS和SYSTEM及其他所有用户名）                                                           NOT NULL VARCHAR2(30)
 OBJECT_NAME --对象名称                                                           NOT NULL VARCHAR2(30)
 SUBOBJECT_NAME                                                                    VARCHAR2(30)
 OBJECT_ID                                                           NOT NULL NUMBER
 DATA_OBJECT_ID                                                                    NUMBER
 OBJECT_TYPE --对象类型，INDEX,TABLE等                                                                    VARCHAR2(19)
 CREATED --创建时间，根据这个来查。                                                           NOT NULL DATE
 LAST_DDL_TIME --最后修改时间，就是对表结构，存储过程等的最后修改时间                                                           NOT NULL DATE
 TIMESTAMP                                                                    VARCHAR2(19)
 STATUS                                                                    VARCHAR2(7)
 TEMPORARY                                                                    VARCHAR2(1)
 GENERATED                                                                    VARCHAR2(1)
 SECONDARY                                                                    VARCHAR2(1)

Answer 2

上面的测试：
SQL> select * from all_objects where to_char(created,'YYYY-MM-DD')='2010-01-23' and owner='SYSTEM';未选定行SQL> create table SYSTEM.test(t number);表已创建。SQL> select * from all_objects where to_char(created,'YYYY-MM-DD')='2010-01-23' and owner='SYSTEM';OWNER                                                        OBJECT_NAME                                          SUBOBJECT_NAME                                                OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE                            CREATED
 LAST_DDL_T TIMESTAMP                        STATUS         TE GE SE
------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ---------- -------------- -------------------------------------- --
-------- ---------- -------------------------------------- -------------- -- -- --
SYSTEM                                                       TEST                                                                                                                   86144          86144 TABLE                                  23-1月 -10
 23-1月 -10 2010-01-23:16:39:43              VALID          N  N  NSQL>

Answer 3

不过我不知道通过导入的方式建表之后，此表数据中的created这个时间戳是否是导入时间，楼主可测试下

Answer 4

可以试着通过查询dba_objects视图中的owner字段来判定某个对象是否是系统拥有的
如果owner为你导入的用户的话
那么应该可以删除的

Answer 5

……算了吧怪我疏忽了 没注意到你导入到SYSTEM下了
还以为只是导入到SYSTEM表空间里了
呵呵

Answer 6

多少个表呀，最好是先spool出sql语句来，一个一个的查。然后再drop叻

Answer 7

如果没有数据更新，也可以尝试恢复一下数据库.. 这样保险.. 表多的话，删起来也麻烦..------------------------------------------------------------------------------
Blog： http://blog.csdn.net/tianlesoftware
网上资源： http://tianlesoftware.download.csdn.net
相关视频：http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
Q Q 群：62697716