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.

To Read More:  http://download.oracle.com/docs/cd/E11882_01/server.112/e16102/asmdiskgrps.htm#CHDJAGHE

Oracle11gR1 - To Read...

 Database Replay - OTN  Documentation

 Interval and Reference Partitions – Paper   Documentation

 SQL Query Result Cache - OTN   Documentation

SQL Performance Analyzer – OTN  Documentation

 SQL Plan Baseline – OTN  Documentation

Automatic SQL Tuning Enhancements - Documentation

Snapshot Standby - Documentation

Active Data Guard (Real-time query) - Documentation

OLTP Table Compression – OTN-OOW   Documentation

AutoTask Infrastructure - Documentation

Virtual Columns - Documentation

Flashback Data Archive (Oracle Total Recall) - OTN Documentation

Flashback Transaction - OTN  Documentation

Automatic Diagnostic Repository & ADRCI - Documentation

Data Recovery Advisor - OTN  Documentation

Active DB Duplication - Documentation

Multi-section backups - Documentation

Lightweight Jobs in Scheduler - Documentation

Fine grained dependency tracking - Documentation

Passwords are Case Sensitive in Oracle11g

The passwords in Oracle11g are case sensitive. All new users created in the 11g database have case sensitive password by default. For databases upgraded from earlier releases, the passwords are not case sensitive for existing accounts. They become case sensitive when you change password. A new column PASSWORD_VERSIONS is added to DBA_USERS view. A value ‘10G 11G’ in this column indicates that case sensitivity is enforced for the account.

The PASSWORD column is still available in DBA_USERS view, it is not populated anymore. For external authenticated or global accounts, the PASSWORD column indicates such.

select username, password_versions, password from dba_users;

USERNAME                       PASSWORD PASSWORD

------------------------------ -------- ---------------

MGMT_VIEW                      10G 11G

SYS                            10G 11G

SYSTEM                         10G 11G

SAMUEL                                  EXTERNAL

BTHOMAS                        10G 11G

ANONYMOUS

XDB                            10G 11G

Disable Case Sensitive Passwords

Oracle11g has a new parameter to disable the password case sensitivity – SEC_CASE_SENSITIVE_LOGON. This parameter is set to TRUE by default. Change to FALSE for pre-Oracle11g password behavior. This parameter can be changed using ALTER SYSTEM.

SYSDBA/SYSOPER password

The password file created using orapwd utility is also case sensitive by default. To make the password for SYS and SYSDBA/SYSOPER case insensitive, include flag ingnorecase=y in the command line.

$ orapwd file=L11GR1.ora ignorecase=y

Enter password for SYS:

DB Links

When connecting to an Oracle11g database with default SEC_CASE_SENSITIVE_LOGON using a database link from pre-11g database, make sure the database password in Oracle11g database is set up as ALL UPPERCASE. Pre-Oracle11g databases send password in uppercase for db link connections. For 11g to 11g, the password case must be the same; for 11g to pre-11g database, the password case does not matter.

	To Pre-Oracle11g	To Oracle11g
From Pre-Oracle11g	Case does not matter	Uppercase
From Oracle11g	Case does not matter	Same case

Users with default passwords

Oracle11g has a new very useful view to list the database accounts that have default password – DBA_USERS_WITH_DEFPWD. This view has only one column – USERNAME. By default the Oracle system accounts and example accounts are locked in Oracle11g.

select * from dba_users_with_defpwd;

USERNAME

------------------------------

DIP

MDSYS

WK_TEST

CTXSYS

HR

OUTLN

EXFSYS

SCOTT

MDDATA

ORDPLUGINS

ORDSYS

XDB

SI_INFORMTN_SCHEMA

WMSYS

Read Only Tables [Orig Published Apr 2000]

In Oracle (all versions including 10g) you can make a tablespace read only, but not a single table. To make a table read only, you need to have that table in its own tablespace. If the tablespace has many tables, then you can either make all the tables read only or not. For large or critical tables, you can have the tables that need to be made read only grouped together in tablespaces. This is especially important when you're are partitioning large tables. For example, if you partition a table that loads historical data, you can make the older partitions read only by making their corresponding tablespace read only.
To make the tablespace read only, issue the command:

ALTER TABLESPACE READ ONLY;

To make the tablespace back to read write mode, issue the command:

ALTER TABLESPACE READ WRITE;

FYI, you cannot make the SYSTEM tablespace to READ ONLY. You can drop a table from the read only tablespace, but not truncate, update, delete or insert.
So, what do you do if you need to make just one table read only, not all the tables in the tablespace.... Well, you can use triggers. Create a trigger which fires for all insert, update and delete operations on the table... Here is an example:

SQL> create or replace trigger emp_read_only
2 before insert or update or delete
3 on emp
4 begin
5 raise_application_error (-20001, 'Table EMP is read only, you cannot modify data.');
6 end;
7 /

Trigger created.

SQL> delete from emp;
delete from emp
*
ERROR at line 1:
ORA-20001: Table EMP is read only, you cannot modify data.
ORA-06512: at "BIJU.EMP_READ_ONLY", line 2
ORA-04088: error during execution of trigger 'BIJU.EMP_READ_ONLY'

Update:

As of Oracle11g, a new clause is introduced in the ALTER TABLE statement, that can make a table to read-only.

ALTER TABLE READ ONLY;

If you try to perform insert or update or delete on read-only table, you get an ORA-12081 error. The nice part about this feature is you are not able to do TRUNCATE operation on a read-only table. Remember, even if the tablespace is read-only, you can truncate a table. You can perform operations on index associated with read-only tables.

To resume normal update activity on the table, perform 

ALTER TABLE READ WRITE;

A new column READ_ONLY is added to DBA_TABLES. To list all the read-only tables in the database, you could do

select owner, table_name, tablespace_name

from dba_tables

where read_only = 'YES';