Tablespace

Clear Screen
Ttitle Off
Spool utblspc.txt
Prompt Table: User_Tablespaces

Column Block_SizeK       Format 999 Heading Block|Size|(K)
Column Initial_ExtentK   Format 9999 Heading Initial|Extent|(K)
Column Next_ExtentK      Format 9999 Heading Next|Extent|(K)
Column Max_ExtentsM      Format 9999 Heading Max|Extent|(M)
Column Min_ExtlenK       Format 9999 Heading Min|Extlen|(K)
Column Status            Format A6

Break On Contents Dup Skip 1

Select Tablespace_Name,
   Block_Size/1024        As Block_Sizek,
   Initial_Extent/1024    As Initial_Extentk,
   Next_Extent/1024       As Next_Extentk,
   Min_Extents,
   Max_Extents/1024/1024  As Max_Extentsm,
   Pct_Increase,
   Min_Extlen/1024        As Min_Extlenk,
   Status, Contents, Logging, Force_Logging, Extent_Management, Allocation_Type,
   Segment_Space_Management,Def_Tab_Compression, Retention, Bigfile
From User_Tablespaces
Where Tablespace_Name ^= 'EXAMPLE'
Order By Contents;

Prompt
Prompt ===========================================================================================================================================================
Prompt
Prompt Table: User_Users

Select Username,
   Default_Tablespace,
   Temporary_Tablespace
From User_Users;

Prompt
Prompt ===========================================================================================================================================================
Prompt
Prompt Table: User_Ts_Quotas  ('-1' Is Unlimited)

Column BytesK Format 9999 Heading Bytes|(K)
Column BytesM Format 99.9 Heading Bytes|(M)

Select Tablespace_Name,
   Bytes/1024      As Bytesk,
   Bytes/1024/1024 As Bytesm,
   Max_Bytes, Blocks, Max_Blocks, Dropped
From User_Ts_Quotas;

Prompt
Prompt ===========================================================================================================================================================
Prompt
Prompt Table: Dba_Data_Files

Column Status                               Format A9
Column Bytesm       Heading Bytes|(M)       Format 9999.9
Column Blocksk      Heading Blocks|(K)      Format 999.9
Column Maxbytesg    Heading Max|Bytes|(G)   Format 99.9
Column Maxblocksm   Heading Max|Blocks|(M)  Format 99.9
Column User_Bytesm  Heading User|Bytes|(M)  Format 999.9
Column User_Blocksk Heading User|Blocks|(K) Format 999.9

Select File_Name, File_Id, Tablespace_Name,
   Bytes/1024/1024                 As Bytesm,
   Blocks/1024                     As Blocksk,
   Status, Relative_Fno,
   Autoextensible,
   Round(Maxbytes/1024/1024/1024)  As Maxbytesg ,
   Maxblocks/1024/1024             As Maxblocksm,
   Increment_By,
   User_Bytes/1024/1024            As User_Bytesm,
   User_Blocks/1024                As User_Blocksk,
   Online_Status   
From Dba_Data_Files
Where File_Name Not Like '%EXAMPLE%'
Order By File_Id;

Prompt
Prompt ===========================================================================================================================================================
Prompt
Prompt Table: User_Free_Space

Column Bytes/Blocks  Format 9999    Heading Bytes/|Blocks
Column Bytesk        Format 999,999 Heading Bytes|(K)
Break On File_Id Skip 1 Dup

Select Tablespace_Name, File_Id, Block_Id,
   Bytes/1024    As Bytesk,
   Blocks, Relative_Fno , Bytes/Blocks
From User_Free_Space
Where Tablespace_Name ^= 'EXAMPLE'
Order By File_Id, Bytes Desc;

Prompt
Prompt ===========================================================================================================================================================
Prompt
Prompt Table: Recyclebin

Column Sum(Space) Heading Total|Blocks

Select Sum(Space)
From Recyclebin
Where Ts_Name = 'USERS';
Spool off
Table: User_Tablespaces

       Block Initial   Next            Max   Pct    Min                            Force Extent Allo-   Segment    Def
Tablespace  Size  Extent Extent     Min Extent Incr- Extlen                            Log-  Manag  cation  Space      Tab                     Big-
Name         (K)     (K)    (K) Extents    (M)  ease    (K) Status Contents  Logging   ging  -ement Type    Management Compression Retention   file
---------- ----- ------- ------ ------- ------ ----- ------ ------ --------- --------- ----- ------ ------- ---------- ----------- ----------- ----
SYSTEM         8      64 .            1   2048 .         64 ONLINE PERMANENT LOGGING   NO    LOCAL  SYSTEM  MANUAL     DISABLED    NOT APPLY   NO
SYSAUX         8      64 .            1   2048 .         64 ONLINE PERMANENT LOGGING   NO    LOCAL  SYSTEM  AUTO       DISABLED    NOT APPLY   NO
USERS          8      64 .            1   2048 .         64 ONLINE PERMANENT LOGGING   NO    LOCAL  SYSTEM  AUTO       DISABLED    NOT APPLY   NO

TEMP           8    1024   1024       1 .       0      1024 ONLINE TEMPORARY NOLOGGING NO    LOCAL  UNIFORM MANUAL     DISABLED    NOT APPLY   NO

UNDOTBS1       8      64 .            1   2048 .         64 ONLINE UNDO      LOGGING   NO    LOCAL  SYSTEM  MANUAL     DISABLED    NOGUARANTEE NO
===========================================================================================================================================================
Table: User_Users

         Default      Temporary
Username     Tablespace   Tablespace
------------ ------------ ------------
SCOTT        USERS        TEMP
===========================================================================================================================================================
Table: User_Ts_Quotas  ('-1' Is Unlimited)

Tablespace Bytes Bytes      Max              Max
Name         (K)   (M)    Bytes  Blocks   Blocks Dropped
---------- ----- ----- -------- ------- -------- -------
USERS       5888   5.8        0     736        0 NO
===========================================================================================================================================================
Table: Dba_Data_Files
                                                                                           Rela- Auto-    Max    Max             User   User
File                                                  File Tablespace   Bytes Blocks            tive exten- Bytes Blocks Increment  Bytes Blocks Online
Name                                                    Id Name           (M)    (K) Status      Fno sible    (G)    (M)        By    (M)    (K) Status
----------------------------------------------------- ---- ---------- ------- ------ --------- ----- ------ ----- ------ --------- ------ ------ -------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL10\SYSTEM01.DBF     1 SYSTEM       490.0   61.3 AVAILABLE     1 YES     32.0    4.0     1,280  489.9   61.2 SYSTEM
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL10\UNDOTBS01.DBF    2 UNDOTBS1      40.0    5.0 AVAILABLE     2 YES     32.0    4.0       640   39.9    5.0 ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL10\SYSAUX01.DBF     3 SYSAUX       350.0   43.8 AVAILABLE     3 YES     32.0    4.0     1,280  349.9   43.7 ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL10\USERS01.DBF      4 USERS          8.8    1.1 AVAILABLE     4 YES     32.0    4.0       160    8.7    1.1 ONLINE
===========================================================================================================================================================
Table: User_Free_Space

                                      Rela-
Tablespace File    Block    Bytes          tive Bytes/
Name         Id       Id      (K)  Blocks   Fno Blocks
---------- ---- -------- -------- ------- ----- ------
SYSTEM        1   62,241    3,840     480     1   8192

UNDOTBS1      2    2,313   22,464   2,808     2   8192
UNDOTBS1      2      201      192      24     2   8192
UNDOTBS1      2      521       64       8     2   8192

SYSAUX        3   43,593    9,664   1,208     3   8192
SYSAUX        3   42,873      128      16     3   8192
SYSAUX        3   38,745       64       8     3   8192

USERS         4    1,113       64       8     4   8192
USERS         4    1,009       64       8     4   8192
USERS         4      769       64       8     4   8192
===========================================================================================================================================================
Table: Recyclebin

 Total
Blocks
----------
   144