SQL：查询用户（默认表空间）及其对象的真实表空间
以下SQL不适合用户对象过多的情况，请视具体情况添加“where rownum < xxx”或其他条件限定。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
SQL> set pagesize 900 SQL> set linesize 300 SQL> col "Seg Name" for a30 SQL> select u.username "Username",u.default_tablespace "Def TBS",u.TEMPORARY_TABLESPACE "Def Temporary TBS",s.segment_name "Seg Name",s.segment_type "Seg Type",s.tablespace_name "Seg TBS" from dba_users u,dba_segments s where u.username=s.owner and u.username='&User_Name'; Enter value for user_name: YJJ old 1: select u.username "Username",u.default_tablespace "Def TBS",u.TEMPORARY_TABLESPACE "Def Temporary TBS",s.segment_name "Seg Name",s.segment_type "Seg Type",s.tablespace_name "Seg TBS" from dba_users u,dba_segments s where u.username=s.owner and u.username='&User_Name' new 1: select u.username "Username",u.default_tablespace "Def TBS",u.TEMPORARY_TABLESPACE "Def Temporary TBS",s.segment_name "Seg Name",s.segment_type "Seg Type",s.tablespace_name "Seg TBS" from dba_users u,dba_segments s where u.username=s.owner and u.username='YJJ' Username Def TBS Def Temporary TBS Seg Name Seg Type Seg TBS ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------ ------------------------------ YJJ ADAMHUAN TEMP DUAL1 TABLE USERS YJJ ADAMHUAN TEMP CD TABLE USERS YJJ ADAMHUAN TEMP NAME TABLE USERS YJJ ADAMHUAN TEMP COMFORT1 TABLE USERS YJJ ADAMHUAN TEMP PAID TABLE USERS YJJ ADAMHUAN TEMP BIRTHDAY TABLE USERS YJJ ADAMHUAN TEMP HOLIDAY TABLE USERS YJJ ADAMHUAN TEMP COMFORT TABLE USERS YJJ ADAMHUAN TEMP MATH TABLE USERS YJJ ADAMHUAN TEMP ADDRESS TABLE USERS YJJ ADAMHUAN TEMP MAGAZINE TABLE USERS YJJ ADAMHUAN TEMP INVOICE TABLE USERS YJJ ADAMHUAN TEMP BORROW TABLE USERS YJJ ADAMHUAN TEMP BOOKS TABLE USERS YJJ ADAMHUAN TEMP CARD TABLE USERS YJJ ADAMHUAN TEMP DEPT TABLE USERS YJJ ADAMHUAN TEMP EMP TABLE USERS YJJ ADAMHUAN TEMP WEATHER1 TABLE USERS YJJ ADAMHUAN TEMP LOCATION TABLE USERS YJJ ADAMHUAN TEMP NEWSPAPER TABLE USERS YJJ ADAMHUAN TEMP LODGING TABLE USERS YJJ ADAMHUAN TEMP SKILL TABLE USERS YJJ ADAMHUAN TEMP WORKERSKILL TABLE USERS YJJ ADAMHUAN TEMP WORKER TABLE USERS YJJ ADAMHUAN TEMP OVERALL TABLE USERS YJJ ADAMHUAN TEMP STANDINGS TABLE USERS YJJ ADAMHUAN TEMP GUPIAO TABLE USERS YJJ ADAMHUAN TEMP WEATHER TABLE USERS YJJ ADAMHUAN TEMP ABC TABLE USERS 29 rows selected. SQL> |
——
Finished。
