Stuff I Don’t Use Often Enough to Remember

This is some stuff I need every now and then that I can’t always remember. Where no version is given, I am referring to version 10.1.0.4.0.

Key-preservedness and Updatability
In order to update a column in a view, it must map directly to a column in a key-preserved table. For a particular view, a table is key-preserved if its keys are also keys of the view. In other words, a table is key-preserved if Oracle can tell, just from the data dictionary, that any row in the underlying table will appear at most once in the view. Tom’s discussion.

Primary Keys and NULLable Columns
The important difference between a primary key and a unique key is that a primary key may not contain any nullable columns. If you ask Oracle to create a primary key including a nullable column, Oracle’s action depends on the contents of the column. If the column contains null values, Oracle will fail the operation with an error. If the column does not contain any null values, Oracle will silently mark it as NOT NULL, and continue creating the index.

DETERMINISTIC Functions
The DETERMINISTIC keyword in a PL/SQL function is very rarely noticed by Oracle. It will require its presence before creating a function-based index on the function, but will otherwise largely ignore it. Discussion. 10gR2 should apparently honour DETERMINISTIC.

Parameterised Views
Views can be parameterised, but not in the same way as PL/SQL code. Contexts are used in applications to set variables, and in view queries to retrieve them. Example.

Forcing Type Drops
You can specify “FORCE” with a DROP TYPE command. This will remove the type, even if it is already in use in tables. It is an unrecoverable operation.

Schema Recompilation
In the absence of a helpful GUI, using 10g, this is fast:
dbms_utility.compile_schema (USER, compile_all => FALSE, reuse_settings => TRUE);
Without 10g, the COMPILE_ALL parameter is not used, and all objects, regardless of STATUS, are recompiled.

Finding Details of Current Session

select *
from v$session
where audsid = userenv(’sessionid’);

Column Renaming
Is O(1), with a data dictionary change caused by an ALTER TABLE statement.

Running Processes
If Oracle thinks a job will take more than about 3s to complete, it will create an entry in v$session_longops for it. This will includes a column TIME_REMAINING, which is pretty accurate. Jobs are not removed automatically on completion, so either compare SOFAR to TOTALWORK or join to v$session to see if a job is still active. DBMS_APPLICATION_INFO is used by code to populate fields in v$session in real time while running.

Correlation of Inline Views
This is possible, and is called left correlation. The populating query occurs in the FROM clause as usual, and after (“to the right of”) any tables it is correlated to. The populating query can then be written as though it were an ordinary subquery, but it is then wrapped in calls to the pseudofunctions MULTISET, CAST, and TABLE. The second example under “Collection Unnesting Examples” here demonstrates this technique. Remember that the CAST … AS …function requires a datatype to cast to.

Foreign Key Behaviour
There are only three options for what happens to a child row after the parent is deleted. CASCADE deletes the child row, SET NULL removes the data in the FK field of the child, and NO ACTION stops the delete from happening at all. There is no option to check validity on insert and then ignore the row.

Locking and Unlocking User Accounts
ALTER USER username ACCOUNT [UN]LOCK; Easy as.

Constraint Enabled Time
LAST_CHANGE in USER_CONSTRAINTS is the last time at which a constraint was enabled or disabled.

Moving Tables and Their LOBs Across Tablespaces
Moving the table:

alter table table_name move tablespace tablespace_name;

Moving a LOB:

alter table table_name move lob(column_name) store as (tablespace tablespace_name);

Having moved them across tablespaces, any indexes on those tables will be marked by Oracle as UNUSABLE (in ALL_INDEXES.STATUS), presumably because the ROWIDs in the index will all be wrong. Indexes need to be rebuilt with:

alter index index_name rebuild;

Attempts to insert a row – and therefore access the index – without rebuilding will result in an ORA-01502 error.

Flushing Data Blocks from Memory
alter system flush buffer_cache;

Generating Numbers
select level
from dual
connect by level < x

Table Versioning
The feature that allows multiple versions of a table (not individual rows) to co-exist is Workspace Management.

Table Recovery
flashback table table_name to before drop;

PARALLEL_ENABLE
This is the equivalent of asserting the highest purity level – the function does not read or write package or database state. Parallel SQL queries which call the function may therefore spawn multiple copies of the function.

Finding USER_DUMP_DEST
select value
from v$parameter
where name=’user_dump_dest’;

Running 10053 Traces
alter session set events ‘10053 trace name context forever’;
and
alter session set events ‘10053 trace name context off’;

Recovering Offlined Datafiles
As sysdba:
recover datafile n;
and
alter database datafile n online;

Finding the Environment Without v$instance
select sys_context(‘USERENV’, ‘INSTANCE_NAME’)
from dual;

[IDENTIFIER] “is an invalid identifier”
Oracle identifiers, including the STATID parameter seen in DBMS_STATS, cannot start with a digit or contain a hypen.