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...
    Saturday, April 4, 2009
    Tablespace Management
    Tablespace Management

    Tablespace management gets a boost thanks to a sparser SYSTEM, support for defining a default tablespace for users, new SYSAUX,
    and even renaming
    How many times you have pulled your hair out in frustration over users creating segments other than SYS and SYSTEM in the SYSTEM
    tablespace?
    Prior to Oracle9i Database, if the DEFAULT TABLESPACE was not specified when the user was created, it would default to the SYSTEM
    tablespace. If the user did not specify a tablespace explicitly while creating a segment, it was created in SYSTEM—provided the user had quota
    there, either explicitly granted or through the system privilege UNLIMITED TABLESPACE. Oracle9i alleviated this problem by allowing the DBA to
    specify a default, temporary tablespace for all users created without an explicit temporary tablespace clause.
    In Oracle Database 10g, you can similarly specify a default tablespace for users. During database creation, the CREATE DATABASE command
    can contain the clause DEFAULT TABLESPACE . After creation, you can make a tablespace the default by issuing
    ALTER DATABASE DEFAULT TABLESPACE ;
    All users created without the DEFAULT TABLESPACE clause will have as their default. You can change the default tablespace at any
    time through this ALTER command, which allows you to specify different tablespaces as default at different points.
    Important note: the default tablespaces of all users with the old tablespace are changed to , even if something else was specified
    explicitly for some users. For instance, assume the default tablespaces for users USER1 and USER2 are TS1 and TS2 respectively, specified
    explicitly during user creation. The current default tablespace for the database is TS2, but later, the database default tablespace is changed to TS1.
    Even though USER2's default tablespace was explicitly specified as TS2, it will be changed to TS1. Beware this side effect!
    If the default tablespace is not specified during the database creation, it defaults to SYSTEM. But how do you know which tablespace is default for
    the existing database? Issue the following query:
    SELECT PROPERTY_VALUE
    FROM DATABASE_PROPERTIES
    WHERE PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE';
    The DATABASE_PROPERTIES view shows some very important information, in addition to the default tablespace—such as the default temporary
    tablespace, global database name, time zone, and much more.
    Default Tablespace for Nonessential Schemas
    Several schemas such as the intelligent agent user DBSNMP, data mining user ODM are not directly related to user operations, but are important
    to database integrity nonetheless. Some of these schemas used to have SYSTEM as their default tablespace — another reason for the proliferation
    of objects inside that special tablespace.
    Oracle Database 10g introduces a new tablespace called SYSAUX that holds the objects of these schemas. This tablespace is created
    automatically during database creation and is locally managed. The only change allowed is the name of the datafile.
    This approach supports recovery when the corruption of SYSTEM requires a full database recovery. Objects in SYSAUX can be recovered as any
    normal user object while the database itself remains operational.
    But what if you want to move some of these schemas in SYSAUX to a different tablespace? Take, for instance, the objects used by LogMiner,
    which often grow in size to eventually fill up the tablespace. For manageability reasons, you may consider moving them to their own tablespace. But
    what is the best way to do that?
    As a DBA it's important for you to know the correct procedure for moving these special objects. Fortunately, Oracle Database 10g provides a new
    view to take the guesswork out. This view, V$SYSAUX_OCCUPANTS, lists the names of the schemas in the tablespace SYSAUX, their
    description, the space currently used, and how to move them. (See Table 1.)
    Note how LogMiner is shown as clearly occupying 7,488 KB. It's owned by the schema SYSTEM, and to move the objects, you would execute the
    packaged procedure SYS.DBMS_LOGMNR_D.SET_TABLESPACE. For STATSPACK objects, however, the view recommends the export/import
    approach, and for Streams, there is no move procedure—thus, you can't easily move them from the SYSAUX tablespace. The column
    MOVE_PROCEDURE shows correct moving procedures for almost all tools resident in the SYSAUX by default. The move procedures can also be
    used in the reverse direction to get objects back into the SYSAUX tablespace.
    Renaming a Tablespace
    It is common in data warehouse environments, typically for data mart architectures, to transport tablespaces between databases. But the source
    and target databases must not have tablespaces with the same names. If there are two tablespaces with the same name, the segments in the
    target tablespace must be moved to a different one and the tablespace recreated—a task easier said than done.
    Oracle Database 10g offers a convenient solution: you can simply rename an existing tablespace (SYSTEM and SYSAUX excepted), whether
    permanent or temporary, using the following command:
    ALTER TABLESPACE RENAME TO ;
    This capability can also come in handy during the archiving process. Assume you have a range-partitioned table for recording sales history, and a
    partition for each month lives in a tablespace named after the month—for example, the partition for January is named JAN and resides in a
    tablespace named JAN. You have a 12-month retention policy. In January 2004, you will be able to archive the January 2003 data. A rough course
    of action will be something similar to the following:
    1. Create a stand-alone table JAN03 from the partition JAN using ALTER TABLE EXCHANGE PARTITION.
    2. Rename the tablespace to JAN03.
    3. Create a transportable tablespace set for the tablespace JAN03
    4. Rename tablespace JAN03 back to JAN.
    5. Exchange the empty partition back to the table.
    Steps 1, 2, 4 and 5 are straightforward and do not overly consume resources such as redo and undo space. Step 3 is merely copying the file and
    exporting only the data dictionary information for JAN03, which is also a very easy process. Should you need to reinstate the partition archived
    earlier, the procedure is as simple as reversing the same process.
    Oracle Database 10g is quite intelligent in the way it handles these renames. If you rename the tablespace used as the UNDO or the default
    temporary one, it could cause confusion. But the database automatically adjusts the necessary records to reflect the change. For instance,
    changing the name of the default tablespace from USERS to USER_DATA automatically changes the view DATABASE_PROPERTIES. Before the
    change, the query:
    select property_value from database_properties
    where property_name = 'DEFAULT_PERMANENT_TABLESPACE';
    returns USERS. After the following statement is run
    alter tablespace users rename to user_data;
    The above query returns USER_DATA, as all the references to USERS have been changed to USER_DATA.
    Changing the default temporary tablespace does the same thing. Even changing the UNDO tablespace name triggers the change in the SPFILE as
    shown below.
    SQL> select value from v$spparameter where name = 'undo_tablespace';
    VALUE
    --------
    UNDOTBS1
    SQL> alter tablespace undotbs1 rename to undotbs;
    Tablespace altered.
    SQL> select value from v$spparameter where name = 'undo_tablespace';
    VALUE
    --------
    UNDOTBS

    *************************************************************************************

    Labels:

    posted by Srinivasan .R @ 2:09 PM  
    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
  • 10g:Rollback Monitoring
  • How To configure NFS
  • Find Apps Version (11i/R12/12i)
  • Oracle10g:Grid Control Installing
  • Oracle10g Data Guard
  • Oracle Application Installation Guide10g Release3
  • Enable HTTP/OC4J/OPMN debug logging
  • Oracle10g: Data Pump
  • Oracle10g RAC Installation
  • Identifying High Active Sessions
  • 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