Chinaunix首页 | 论坛 | 博客
  • 博客访问: 555519
  • 博文数量: 43
  • 博客积分: 8000
  • 博客等级: 中将
  • 技术积分: 1510
  • 用 户 组: 普通用户
  • 注册时间: 2006-06-01 15:07
文章分类

全部博文(43)

文章存档

2011年(1)

2009年(12)

2008年(30)

我的朋友

分类: Oracle

2008-05-21 14:17:27

----代码
create cluster user_objects_cluster_btree
(username varchar2(30))
size 1024
/

create index user_objects_idx
on cluster user_objects_cluster_btree
/

create table user_info
(username,user_id,account_status,lock_date,expiry_date,default_tablespace,temporary_tablespace,created,profile)
cluster user_objects_cluster_btree(username)
as
select username,user_id,account_status,lock_date,expiry_date,default_tablespace,temporary_tablespace,created,profile
from dba_users
where 1=0;


create table users_objects
(owner,object_name,object_id,object_type,created,last_ddl_time,timestamp,status)
cluster user_objects_cluster_btree(owner)
as
select owner,object_name,object_id,object_type,created,last_ddl_time,timestamp,status
from dba_objects
where 1=0
/



 insert
when (r=1) then
into user_info
(username,user_id,account_status,lock_date,
 expiry_date,default_tablespace,temporary_tablespace,created,profile)
values
(username,user_id,account_status,lock_date,
 expiry_date,default_tablespace,temporary_tablespace,user_created,profile)
when (1=1) then
into users_objects
(owner,object_name,object_id,object_type,created,last_ddl_time,timestamp,status)
values
(owner,object_name,object_id,object_type,obj_created,last_ddl_time,timestamp,status)
select
a.username,a.user_id,a.account_status,a.lock_date,
a.expiry_date,a.default_tablespace,a.temporary_tablespace,
a.created user_created,a.profile,
b.owner,b.object_name,b.object_id,b.object_type,
b.created obj_created,
b.last_ddl_time,b.timestamp,b.status,
row_number() over(partition by owner order by object_id) r
from dba_users a,dba_objects b
where a.username=b.owner
and a.username<>'SYS';
阅读(1307) | 评论(0) | 转发(0) |
0

上一篇:10g的dbms_stats

下一篇:部分分析函数的使用

给主人留下些什么吧!~~