Oracle 11.1 and above read-only tables

In Oracle 11.1 and above you can use alter table clause to change table to read-only or read-write mode.

Change table to read-only:
SQL> alter table test_table read only;

After this users can't run deletes, inserts or updates for changed table.

Change table to read-write:

SQL> alter table test_table read write;

After this users can again run deletes, inserts or updates for this table.

You can check table mode from user_tables, all_tables or dba_tables views (check the READ_ONLY column).

Invisible Index Oracle 11.1 and above.

Starting in Oracle 11.1 is it possible to test index before starting to use it with all sessions. And it is also possible to test affect of index dropping before actually dropping the index.  These can be done with invisible index feature which allows you to set optimizer seeing index only in the session you are using.

Here is a couple of examples how to do this:

Create invisible index:


SQL> CREATE INDEX test_invis_idx ON test_table (column_name) INVISIBLE;

There is new init param 'optimizer_use_invisible_indexes' which tells if optimizer sees the invisible indexes. And default values for this parameter is false ( so optimizer does not see invisible indexes by default and because of this queries does not use this kind of indexes). You can set this parameter true for the session you are using and test new index with only that session:
SQL> ALTER SESSION SET optimizer_use_invisible_indexes=true;

Then run queries that are using new index and if everything works like you want then change index visible and all sessions start to use new index because optimizer sees this index ( all new indexes are visible by default ):

SQL> ALTER INDEX test_invis_idx  VISIBLE;

And same way you can test affect of dropping index. First change index you like to drop invisible and see if queries are starting to working poorly. If they are change index back to visible. And if they are working ok without the index you can drop it safely.

SQL> ALTER INDEX test_invis_idx  INVISIBLE;
SQL> DROP INDEX test_invis_idx;


You can check index state from the user_indexes , all_indexes or dba_indexes views like this:

SQL> select index_name,VISIBILITY from user_indexes where index_name='test_invis_idx';

INDEX_NAME                     VISIBILIT

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

TEST_INVIS_IDX           VISIBLE

Lock table clause changes in Oracle 11.1 and above.

In Oracle 11.1 and above you can set wait time for LOCK TABLE clause.
Default value for lock table clause is WAIT . And with that lock table clause will wait as long it gets the lock for the table. But you can give also waith time for lock table clause with command like this:
LOCK TABLE <table_name> IN <lock_mode> MODE WAIT <seconds to wait>;
For example:
SQL> LOCK TABLE test1 IN exclusive MODE WAIT 60;


And if you use NOWAIT instead of WAIT then lock table clause will give error straight away if it does not get table lock for table.

DDL Lock timeout in Oracle 11.1 and above.

If you need to run DDL command (for example modify column size) for highly used (modified) table.  It could be difficult to get DDL command throught. To easier this in Oracle 11.1 and above you can use following command to set time which DDL command will wait to get lock in the table:
alter session set ddl_lock_timeout=<seconds_that_DDL_will_wait>;
for example set DDL to wait 60 seconds to get the lock in the table:
SQL> alter session set ddl_lock_timeout=60;

After this you can run DDL command to make table modification and DDL command will wait for 60 seconds to get table lock to make it changes. If it does not get table lock for that time it will give error. 

NOTE! Default value for ddl_lock_timeout is 0 (NOWAIT).
So it gives error straight if it does not get lock in the tables.

Shrink TEMP Tablespace or Tempfile in Oracle 11.1 and above.

In Oracle 11.1 and above versions you can shrink TEMP tablespace and/or Tempfiles.

With dba_temp_free_space view you can check temp tablespace space usage:
SQL> SELECT * FROM dba_temp_free_space;

With command:
ALTER TABLESPACE <temp_tablespace_name> SHRINK SPACE KEEP <size_for_temp_tablespace>M;
You can shrink temp tablespace to certain size.

For example to shrink temp tablespace to 40M:
SQL> ALTER TABLESPACE temp SHRINK SPACE KEEP 40M;

And if you want to shrink certain tempfile you can do it with following command:
ALTER TABLESPACE <temp_tablespace_name> SHRINK TEMPFILE <'path_to_tempfile_and_tempfile_name'> KEEP <size_for_tempfile>M;

For example to shrink test_temp01.dbf tempfile to 40M :
SQL> ALTER TABLESPACE temp SHRINK TEMPFILE '/u01/app/oracle/oradata/testdb/test_temp01.dbf' KEEP 40M;


If you don't set the KEEP clause in the end of the shrink clause then tablespace or tempfile will be shrinked as small as possible (1M).