Oracle Cloud Infrastructure
Coming soon ...
Apps R12.2
  • R12.2 Architecture
  • Cloning Error (RC-50208)
  • Apps R12.1
  • Major changes from 11i
  • R12:HTTP Debug Logging
  • Compile Apps Schema invalid objects in R12
  • Apps 11i
  • Apps Components and Architecture
  • Concurrent managers
  • Patching
  • Using AD Patch
  • Using AD Control
  • FNDCPASS utility
  • Single-node Installation
  • Multi-node Installation
  • Find Apps Version
  • Cloning
  • Upgrade 11.5.9 to 11.5.10.2
  • Upgrade from 11.5.10.2 to R12
  • Upgrading 9i to 10g with 11i
  • 11i/R12 General Topics
  • AppsDBA System Management Utilities Guide
  • Identifying Long Idle Sessions
  • Identifying High Active Sessions
  • Change hostname for EBS
  • Oracle 12c Database
  • Oracle12c PSU Apply
  • Oracle12c Datafile moved Online
  • Oracle 11g Database
  • Upgrade 10g to 11g R1
  • Upgrade 11.2.0.2 to 11.2.0.3
  • Database 9i-10g
  • Top 99 Responsibilities of a DBA
  • General Info
  • Database Patching
  • 10g:ASM
  • 10g:Data Pump
  • 10g:Data Guard Installing
  • 10g:Rollback Monitoring
  • 10g:Flashback Table
  • Tablespace Management
  • Materialized Views
  • 10g:Enterprise Manager
  • 10g:Upgrade
  • Error:Ora-01631
  • DBA Scripts
  • Disk I/O,Events,Waits
  • Tablespace Information
  • Session Statistics
  • Hit/Miss Ratios
  • User Information
  • Rollback Segments
  • Full Table Scans
  • Contention/Locking
  • Redo Log Buffer
  • Data Dictionary Info
  • Oracle10g Application Server
  • Oracle10g Application Installation
  • (Re)Securing OAS Control
  • Oracle AS10g null protocol issue
  • Oracle Backup & Recovery
  • RMAN
  • RMAN Setup
  • Backup Recovery
  • Flash Recovery Area
  • Oracle10g Discoverer with Apps
    Coming soon ..............
    Troubleshooting
  • Discoverer Troubleshooting
  • Access EBS in mozile
  • Linux and Unix Platforms
  • How To configure NFS
  • Unix/Linux Command
  • Change hostname in Linux
  • SENDMAIL configuration
  • This Oracle Application DBA Portal is the biggest knowledge gateway for the people in the world of Oracle...
    Friday, June 19, 2009
    Tablespace Information
    Tablespace Information
    Here are some scripts related to Tablespace Information .

    Information
    TABLESPACE INFORMATION NOTES:


    Tablespace Name - Name of the tablespace
    Initial Extent - Default initial extent size
    Next Extent - Default incremental extent size
    Min Extents - Default minimum number of extents
    Max Extents - Default maximum number of extents
    PCT Increase - Default percent increase for extent size
    Status - Tablespace status: ONLINE, OFFLINE, or INVALID (tablespace has been dropped)
    Contents - Type of tablespace. This column will have 'TEMPORARY' (v7.3+) for dedicated temporary tablespaces, and 'PERMANENT' for tablespaces that can store both temporary sort segments and permanent objects.

    select TABLESPACE_NAME,
    INITIAL_EXTENT,
    NEXT_EXTENT,
    MIN_EXTENTS,
    MAX_EXTENTS,
    PCT_INCREASE,
    STATUS,
    CONTENTS
    from dba_tablespaces
    order by TABLESPACE_NAME

    Coalesced Exts
    WAIT STATISTIC NOTES:


    Tablespace Name - Name of tablespace
    Total Extents - Total number of free extents in tablespace
    Extents Coalesced - Total number of coalesced free extents in tablespace
    % Extents Coalesced - Percentage of coalesced free extents in tablespace
    Total Bytes - Total number of free bytes in tablespace
    Bytes Coalesced - Total number of coalesced free bytes in tablespace
    Total Blocks - Total number of free oracle blocks in tablespace
    Blocks Coalesced - Total number of coalesced free Oracle blocks in tablespace
    % Blocks Coalesced - Percentage of coalesced free Oracle blocks in tablespace

    select TABLESPACE_NAME,
    TOTAL_EXTENTS,
    EXTENTS_COALESCED,
    PERCENT_EXTENTS_COALESCED,
    TOTAL_BYTES,
    BYTES_COALESCED,
    TOTAL_BLOCKS,
    BLOCKS_COALESCED,
    PERCENT_BLOCKS_COALESCED
    from dba_free_space_coalesced
    order by TABLESPACE_NAME

    Usage
    TABLESPACE USAGE NOTES:


    Tablespace Name - Name of the tablespace
    Bytes Used - Size of the file in bytes
    Bytes Free - Size of free space in bytes
    Largest - Largest free space in bytes
    Percent Used - Percentage of tablespace that is being used - Careful if it is more than 85%

    select a.TABLESPACE_NAME,
    a.BYTES bytes_used,
    b.BYTES bytes_free,
    b.largest,
    round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used
    from
    (
    select TABLESPACE_NAME,
    sum(BYTES) BYTES
    from dba_data_files
    group by TABLESPACE_NAME
    )
    a,
    (
    select TABLESPACE_NAME,
    sum(BYTES) BYTES ,
    max(BYTES) largest
    from dba_free_space
    group by TABLESPACE_NAME
    )
    b
    where a.TABLESPACE_NAME=b.TABLESPACE_NAME
    order by ((a.BYTES-b.BYTES)/a.BYTES) desc

    Users Default (SYSTEM)
    SYSTEM TABLESPACE USAGE NOTES:


    Username - Name of the user
    Created - User creation date
    Profile - Name of resource profile assigned to the user
    Default Tablespace - Default tablespace for data objects
    Temporary Tablespace - Default tablespace for temporary objects

    Only SYS, SYSTEM and possibly DBSNMP should have their default tablespace set to SYSTEM.

    select USERNAME,
    CREATED,
    PROFILE,
    DEFAULT_TABLESPACE,
    TEMPORARY_TABLESPACE
    from dba_users
    order by USERNAME

    Objects in SYSTEM TS
    OBJECTS IN SYSTEM TABLESPACE NOTES:


    Owner - Owner of the object
    Object Name - Name of object
    Object Type - Type of object
    Tablespace - Tablespace name
    Size - Size (bytes) of object

    Any user (other than SYS, SYSTEM) should have their objects moved out of the SYSTEM tablespace

    select OWNER,
    SEGMENT_NAME,
    SEGMENT_TYPE,
    TABLESPACE_NAME,
    BYTES
    from dba_segments
    where TABLESPACE_NAME = 'SYSTEM'
    and OWNER not in ('SYS','SYSTEM')
    order by OWNER, SEGMENT_NAME

    Freespace/Largest Ext
    FREE, LARGEST, & INITIAL NOTES:


    Tablespace - Name of the tablespace
    Total Free Space - Total amount (bytes) of freespace in the tablespace
    Largest Free Extent - Largest free extent (bytes) in the tablespace

    select TABLESPACE_NAME,
    sum(BYTES) Total_free_space,
    max(BYTES) largest_free_extent
    from dba_free_space
    group by TABLESPACE_NAME

    Labels:

    posted by Srinivasan .R @ 12:54 AM  
    0 Comments:
    Post a Comment
    << Home
     
    About Me

    Name: Srinivasan .R
    Home: Chennai, India

    About Me:
    I am working as an Oracle Applications DBA specializing in EBS 11i/R12 with Over 14+ years of experience, mainly in different versions of Oracle Database & Application administration on various platforms like HP-UX, SOLARIS, AIX, Red hat Linux & Windows
    See my complete profile
    High Availability
  • Oracle10g RAC Installation
  • A Quick Reference for Oracle Database 10g RAC on Linux and Unix Platforms
  • Implementing Oracle 10g RAC with ASM on AIX
  • Locked objects for whole RAC
  • Monitor Memory RAC
  • Sessions RAC
  • Install Oracle 11g RAC On Linux
  • Migrating Oracle10g DB to ASM
  • Helpful Links
  • Good Metalink Notes
  • Discoverer:Metalink Notes
  • Logs Scripts:Metalink Notes
  • Support:Metalink Notes
  • Previous Post
  • Disk I/O, Events, Waits
  • General Info
  • Migrating Oracle10g DB to ASM
  • Install Oracle 11g RAC On Linux
  • Sessions RAC
  • Monitor Memory RAC
  • Lists all locked objects for whole RAC
  • Concurrent Managers
  • Backup Recovery
  • RMAN Setup
  • Archives
    Download Software
  • Oracle 11g
  • Oracle 10g
  • 10g Express Edition
  • Oracle 9i
  • Oracle Apps
  • Oracle Linux
  • Oracle VM
  • App Server
  • Solaris
  • Fedora
  • Fedora
  • OpenSUSE
  • Ubuntu
  • Advertisement Links
    INTUIT Technology

    MACHS DATA

    Add Ons
    Locations of visitors to this page

    Add to Google Reader or Homepage

    Template by
    Sreene



    Oracle Application DBA Portal