OracleNotes

Blog

  • Query – Tablespace Max Size

    I sometimes see concerned emails from developers that they are running out of space in tablespace and the DBA need to do something immediately. The DBA will start wondering we have monitoring in place, then how come it did not catch an out of space situation, but a user/developer did…

    For you developer/user, if you are wondering if your database/tablespace is running out of space, you may need to check if the tablespace has autoextensible data files…

    Here is a simple query, that could be used to find the current size of the tablespace and how much it can grow using the autoextensible feature of data file.

      SELECT tablespace_name,
             ROUND (SUM (bytes) / 1048576) curr_size,
             ROUND (SUM (GREATEST (bytes, DECODE (autoextensible, ‘YES’, maxbytes, 0)))/ 1048576)  growable
        FROM dba_data_files
    GROUP BY tablespace_name
    ORDER BY tablespace_name;

  • Rename Diskgroup in ASM

    Rename ASM Diskgroup [11gR2]
    In 11gR2 it is possible to rename an ASM diskgroup. This is especially useful when performing database copy using OS level LUN mirroring technologies.
    The renamedg command is used to rename diskgroups. It has the following options:
    $ renamedg -help
    Parsing parameters..
    phase                           Phase to execute,
                                    (phase=ONE|TWO|BOTH), default BOTH
    dgname                          Diskgroup to be renamed
    newdgname                       New name for the diskgroup
    config                          intermediate config file
    check                           just check-do not perform actual operation,
                                    (check=TRUE/FALSE), default FALSE
    confirm                         confirm before committing changes to disks,
                                    (confirm=TRUE/FALSE), default FALSE
    clean                           ignore errors,
                                    (clean=TRUE/FALSE), default TRUE
    asm_diskstring                  ASM Diskstring (asm_diskstring=’discoverystring’,
                                    ‘discoverystring1’ …)
    verbose                         verbose execution,
                                    (verbose=TRUE|FALSE), default FALSE
    keep_voting_files               Voting file attribute,
                                    (keep_voting_files=TRUE|FALSE), default FALSE
    To rename a diskgroup, it must be stopped [or unmounted].
    $ srvctl stop diskgroup -g oops1
    $ renamedg dgname=oops1 newdgname=good1 verbose=true
    $ srvctl start diskgroup –g good1
    You must manually rename all database files on the diskgroup to reflect the new name using ALTER DATABSE RENAME FILE command.
    Even after successful rename command, old diskgroup resources in Oracle Restart must be manually removed using the srvctl command.