CREATE OR REPLACE VIEW ADM_TABLE AS
SELECT /* for Memory Tables */
'SYS_TBS_MEMORY' TBS_NAME,
B.USER_NAME || '.' || A.TABLE_NAME TABLE_NAME,
(C.FIXED_ALLOC_MEM + C.VAR_ALLOC_MEM) ALLOCSIZE,
TABLE_ID,
REPLICATION_COUNT REPL_COUNT
FROM SYSTEM_.SYS_TABLES_ A, SYSTEM_.SYS_USERS_ B, V$MEMTBL_INFO C
WHERE A.USER_ID = B.USER_ID
AND A.TABLE_OID = C.TABLE_OID
AND A.TBS_ID = 1
AND A.USER_ID != 1
AND A.TABLE_TYPE = 'T';
exec showTables('User_name'); --查看某个用户的表信息
create or replace procedure SHOWTABLES(p1 in varchar(40))
as
-- exec showTables('User_name');
cursor c1 is
select SYSTEM_.SYS_TABLES_.TABLE_NAME
from SYSTEM_.SYS_TABLES_
where SYSTEM_.SYS_TABLES_.USER_ID =
(select SYSTEM_.SYS_USERS_.USER_ID
from SYSTEM_.SYS_USERS_
where SYSTEM_.SYS_USERS_.USER_NAME = upper(p1)
AND system_.SYS_TABLES_.TABLE_TYPE = 'T');
v1 CHAR(40);
begin
open c1;
SYSTEM_.PRINTLN('-------------------');
system_.print(p1);
SYSTEM_.PRINTLN(' Table');
SYSTEM_.PRINTLN('-------------------');
loop
fetch C1 into v1;
exit when C1%NOTFOUND;
system_.print(' ');
SYSTEM_.PRINTLN(v1);
end loop;
SYSTEM_.PRINTLN('-------------------');
close c1;
end;
/