On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Sunday, August 24, 2008

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

No comments:

Post a Comment