VIEW: SYS.USER_FREE_SPACE

Source

View Text - Preformatted

select ts.name, fi.file#, f.block#,
       f.length * ts.blocksize, f.length, f.file#
from sys.fet$ f, sys.ts$ ts, sys.file$ fi
where f.ts# = ts.ts#
  and f.ts# = fi.ts#
  and f.file# = fi.relfile#
  and ts.bitmapped = 0
  and (ts.ts# in
         (select tsq.ts#
          from sys.tsq$ tsq
          where tsq.user# = userenv('SCHEMAID') and tsq.maxblocks != 0)
       or exists
          (select null
           from sys.v$enabledprivs
           where priv_number = -15 /* UNLIMITED TABLESPACE */)
      )
union all
select
       ts.name, fi.file#, f.ktfbfebno,
       f.ktfbfeblks * ts.blocksize, f.ktfbfeblks, f.ktfbfefno
from sys.ts$ ts, sys.x$ktfbfe f, sys.file$ fi
where ts.ts# = f.ktfbfetsn
  and f.ktfbfetsn = fi.ts#
  and f.ktfbfefno = fi.relfile#
  and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
  and (ts.ts# in
         (select tsq.ts#
          from sys.tsq$ tsq
          where tsq.user# = userenv('SCHEMAID') and tsq.maxblocks != 0)
       or exists
          (select null
           from sys.v$enabledprivs
           where priv_number = -15 /* UNLIMITED TABLESPACE */)
      )
union all
select
       ts.name, fi.file#, u.ktfbuebno,
       u.ktfbueblks * ts.blocksize, u.ktfbueblks, u.ktfbuefno
from sys.recyclebin$ rb, sys.ts$ ts, sys.x$ktfbue u, sys.file$ fi
where ts.ts# = rb.ts#
  and rb.ts# = fi.ts#
  and rb.file# = fi.relfile#
  and u.ktfbuesegtsn = rb.ts#
  and u.ktfbuesegfno = rb.file#
  and u.ktfbuesegbno = rb.block#
  and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
  and (ts.ts# in
         (select tsq.ts#
          from sys.tsq$ tsq
          where tsq.user# = userenv('SCHEMAID') and tsq.maxblocks != 0)
       or exists
          (select null
           from sys.v$enabledprivs
           where priv_number = -15 /* UNLIMITED TABLESPACE */)
      )
union all
select ts.name, fi.file#, u.block#,
       u.length * ts.blocksize, u.length, u.file#
from sys.ts$ ts, sys.uet$ u, sys.file$ fi, sys.recyclebin$ rb
where ts.ts# = u.ts#
  and u.ts# = fi.ts#
  and u.segfile# = fi.relfile#
  and u.ts# = rb.ts#
  and u.segfile# = rb.file#
  and u.segblock# = rb.block#
  and ts.bitmapped = 0
  and (ts.ts# in
         (select tsq.ts#
          from sys.tsq$ tsq
          where tsq.user# = userenv('SCHEMAID') and tsq.maxblocks != 0)
       or exists
          (select null
           from sys.v$enabledprivs
           where priv_number = -15 /* UNLIMITED TABLESPACE */)
      )

View Text - HTML Formatted

SELECT TS.NAME
, FI.FILE#
, F.BLOCK#
, F.LENGTH * TS.BLOCKSIZE
, F.LENGTH
, F.FILE# FROM SYS.FET$ F
, SYS.TS$ TS
, SYS.FILE$ FI WHERE F.TS# = TS.TS#
AND F.TS# = FI.TS#
AND F.FILE# = FI.RELFILE#
AND TS.BITMAPPED = 0
AND (TS.TS# IN (SELECT TSQ.TS#
FROM SYS.TSQ$ TSQ
WHERE TSQ.USER# = USERENV('SCHEMAID')
AND TSQ.MAXBLOCKS != 0) OR EXISTS (SELECT NULL
FROM SYS.V$ENABLEDPRIVS
WHERE PRIV_NUMBER = -15 /* UNLIMITED TABLESPACE */) ) UNION ALL SELECT TS.NAME
, FI.FILE#
, F.KTFBFEBNO
, F.KTFBFEBLKS * TS.BLOCKSIZE
, F.KTFBFEBLKS
, F.KTFBFEFNO FROM SYS.TS$ TS
, SYS.X$KTFBFE F
, SYS.FILE$ FI WHERE TS.TS# = F.KTFBFETSN
AND F.KTFBFETSN = FI.TS#
AND F.KTFBFEFNO = FI.RELFILE#
AND TS.BITMAPPED <> 0
AND TS.ONLINE$ IN (1
, 4)
AND TS.CONTENTS$ = 0
AND (TS.TS# IN (SELECT TSQ.TS#
FROM SYS.TSQ$ TSQ
WHERE TSQ.USER# = USERENV('SCHEMAID')
AND TSQ.MAXBLOCKS != 0) OR EXISTS (SELECT NULL
FROM SYS.V$ENABLEDPRIVS
WHERE PRIV_NUMBER = -15 /* UNLIMITED TABLESPACE */) ) UNION ALL SELECT TS.NAME
, FI.FILE#
, U.KTFBUEBNO
, U.KTFBUEBLKS * TS.BLOCKSIZE
, U.KTFBUEBLKS
, U.KTFBUEFNO FROM SYS.RECYCLEBIN$ RB
, SYS.TS$ TS
, SYS.X$KTFBUE U
, SYS.FILE$ FI WHERE TS.TS# = RB.TS#
AND RB.TS# = FI.TS#
AND RB.FILE# = FI.RELFILE#
AND U.KTFBUESEGTSN = RB.TS#
AND U.KTFBUESEGFNO = RB.FILE#
AND U.KTFBUESEGBNO = RB.BLOCK#
AND TS.BITMAPPED <> 0
AND TS.ONLINE$ IN (1
, 4)
AND TS.CONTENTS$ = 0
AND (TS.TS# IN (SELECT TSQ.TS#
FROM SYS.TSQ$ TSQ
WHERE TSQ.USER# = USERENV('SCHEMAID')
AND TSQ.MAXBLOCKS != 0) OR EXISTS (SELECT NULL
FROM SYS.V$ENABLEDPRIVS
WHERE PRIV_NUMBER = -15 /* UNLIMITED TABLESPACE */) ) UNION ALL SELECT TS.NAME
, FI.FILE#
, U.BLOCK#
, U.LENGTH * TS.BLOCKSIZE
, U.LENGTH
, U.FILE# FROM SYS.TS$ TS
, SYS.UET$ U
, SYS.FILE$ FI
, SYS.RECYCLEBIN$ RB WHERE TS.TS# = U.TS#
AND U.TS# = FI.TS#
AND U.SEGFILE# = FI.RELFILE#
AND U.TS# = RB.TS#
AND U.SEGFILE# = RB.FILE#
AND U.SEGBLOCK# = RB.BLOCK#
AND TS.BITMAPPED = 0
AND (TS.TS# IN (SELECT TSQ.TS#
FROM SYS.TSQ$ TSQ
WHERE TSQ.USER# = USERENV('SCHEMAID')
AND TSQ.MAXBLOCKS != 0) OR EXISTS (SELECT NULL
FROM SYS.V$ENABLEDPRIVS
WHERE PRIV_NUMBER = -15 /* UNLIMITED TABLESPACE */) )