内存表信息查看

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;
/