----代码
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) |