select * from dba_tables where OWNER='schema名称';

--可以参考，查询数据库中所有模式名称和所有者
select
SCH_OBJ.NAME SCH_NMAE,
USER_OBJ.NAME OWNER,
SCH_OBJ.CRTDATE
from
SYSOBJECTS SCH_OBJ,
SYSOBJECTS USER_OBJ
where
SCH_OBJ.PID = USER_OBJ.ID
AND SCH_OBJ.TYPE$ = 'SCH'
ORDER BY
SCH_OBJ.NAME;

dba权限用户：
--查看所有模式
select object_name from dba_objects where object_type ='SCH';

--查看某个模式下的表
select object_name from dba_objects where object_type ='TABLE' and owner ='模式名';

普通用户：
--查看所有模式
select NAME from sysobjects where type$ = 'SCH'

--查看某个模式下的表
select A.*
from sysobjects A,
sysobjects B
where A.SCHID =B.ID
and A.type$ = 'SCHOBJ'
and A.SUBTYPE$ ='UTAB'
and A.SCHID IN (select ID from sysobjects where type$ = 'SCH' AND NAME = '模式名')
and A.pid = -1;