Which are the potential use cases for Flashback Data Archive, introduced in Oracle 11gR1?

Flashback Data Archive is handy for many purposes. Here are some ideas:

- To audit for recording how data changed
- To enable an application to undo changes (correct mistakes)
- To debug how data has been changed
- To comply with some regulations that require data must not be changed after some time.
Flashback Data Archives are not regular tables so they can't be changed by typical users.
- Recording audit trails on cheaper storage thereby allowing more retention at less cost


Remember: Inserts do not create archive records; because they are not changes to data.

Source: Arup Nanda

What are the different types of database links used in Oracle?

Connected user
A local user accessing a database link in which no fixed username and password have been specified.
Note that connected users does not have to be the user who created the link, but is any user who is accessing the link.

Example:
CREATE PUBLIC DATABASE LINK sales USING 'sales_db';

Fixed user
A user whose username/password is part of the link definition.

CREATE PUBLIC DATABASE LINK sales CONNECT TO scott IDENTIFIED BY tiger USING 'sales_db';

If you are logged in locally as scott and try to use the database link, a connection as scott is made on the remote side, too.
If the user scott doesn't exist in the remote database, an error will be thrown.

In some cases the database link must contain the entire connect string:

CREATE PUBLIC DATABASE LINK mydatabaselink
CONNECT TO scott
IDENTIFIED BY tiger
USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=remoteserver1.domain.com)(PORT=1521))(CONNECT_DATA= (SID=proddb01)))';

Current user
The link uses the userid/password of the current user to log onto the remote database.

CREATE DATABASE LINK sales CONNECT TO CURRENT_USER USING 'sales';

Note: Current user links are only available through the Oracle Advanced Security option.


Database links are either private or public, authenticated or non-authenticated.
Specify PUBLIC to create a public database link available to all users.

If the value of the GLOBAL_NAMES initialization parameter is TRUE, then the database link must have the same name as the database to which it connects.

How to generate scripts to move partitions to a new tablespace while updating the indexes

Probably many ways of doing this, but here is how I generated scripts to move table partitions from one tablespace to another:

-- Find table partitions with GLOBAL indexes:
SELECT 'ALTER TABLE ' || TABLE_OWNER || '.' || TABLE_NAME || ' MOVE PARTITION "' || PARTITION_NAME || '" TABLESPACE DATA2 NOCOMPRESS UPDATE GLOBAL INDEXES;'
FROM    dba_tab_partitions
WHERE   table_owner         = 'USER1'
AND     tablespace_name     = 'DATA1'
AND     subpartition_count  = 0
AND     table_name IN (
        SELECT  distinct table_name   
        FROM    dba_part_indexes
        WHERE   owner = 'USER1'
        AND     locality = 'GLOBAL'
)
ORDER BY table_name DESC;

-- Find table partitions with LOCAL indexes:
SELECT 'ALTER TABLE ' || TABLE_OWNER || '.' || TABLE_NAME || ' MOVE PARTITION "' || PARTITION_NAME || '" TABLESPACE DATA2 NOCOMPRESS UPDATE INDEXES;'
FROM    dba_tab_partitions
WHERE   table_owner         = 'USER1'
AND     tablespace_name     = 'DATA1'
AND     subpartition_count  = 0
AND     table_name IN (
        SELECT  distinct table_name   
        FROM    dba_part_indexes
        WHERE   owner = 'USER1'
        AND     locality = 'LOCAL'
)
ORDER BY table_name DESC;


-- Find table partitions with no index at all
SELECT 'ALTER TABLE ' || TABLE_OWNER || '.' || TABLE_NAME || ' MOVE PARTITION "' || PARTITION_NAME || '" TABLESPACE DATA2 NOCOMPRESS;'
FROM    dba_tab_partitions
WHERE   table_owner         = 'USER1'
AND     tablespace_name     = 'DATA1'
AND     subpartition_count  = 0
AND     table_name NOT IN (
        SELECT  table_name   
        FROM    dba_part_indexes
        WHERE   owner = 'USER1'
)
ORDER BY table_name DESC;

How to delete lines in vi (unix)

d1G = delete to top including current line
Dgg = delete from current line to bottom of file

How to make a table read only (11g only)

ALTER TABLE table_name READ ONLY;

The command would make the table read-only even for its owner.

To reverse the operation:

ALTER TABLE table_name READ WRITE;

What is the definition of "Partition Pruning"?

From the Oracle 11 documentation


Oracle Database explicitly recognizes partitions and subpartitions.
It then optimizes SQL statements to mark the partitions or subpartitions that need to be accessed and eliminates (prunes) unnecessary partitions or subpartitions from access by those SQL statements.

In other words, partition pruning is the skipping of unnecessary index and data partitions or subpartitions in a query.

See also my post about the difference between dynamic and static pruning

How to move a table containing LOB columns to a new tablespace

Find out which tables have LOB objects:

SELECT  TABLE_NAME,COLUMN_NAME,SEGMENT_NAME,TABLESPACE_NAME,INDEX_NAME
FROM    DBA_LOBS 
WHERE   OWNER = 'USR1';

Genrate a "move table" script:

select 'alter table ' || owner || '.' || table_name || ' move lob(' || column_name ||') store as (tablespace LOB_DATA);'
from   dba_tab_columns c
where  c.owner = 'USR1'
and    c.data_type like '%LOB%';

Query returns the following statements:

alter table USR1.LG_LOG move lob(MESSAGE_CONTEXT) store as (tablespace LOB_DATA);
alter table USR1.TFW_TEST_RESULT move lob(SQL_NUM_MATCHED_ERROR_ROWS) store as (tablespace LOB_DATA);

Note:
The LOB index is an internal structure that is strongly associated with LOB storage.
This implies that a user may not drop the LOB index or rebuild it.