Temporary tablespaces

Restriction on Taking Tablespaces Offline: You cannot take a temporary tablespace offline.

To get an overview of temp tablespace usage:

select TABLESPACE_NAME,CURRENT_USERS,SEGMENT_FILE,USED_EXTENTS,FREE_EXTENTS from v$sort_segment;

select username, user, contents, segtype, tablespace from v$tempseg_usage;

If you want to drop non-default temporary tablespace, you issue something like:

> drop tablespace tempt including contents and datafiles;

At least in 10.2 the previous statement got hanging until the temporary segments (temp tables in my case) were not cleared.

Also after killing the non-default temp tablespace 'tempt', the temp tables created in there became invalid:

SCOTT@andrkydb> insert into te values (6);
insert into te values (6)
*
ERROR at line 1:
ORA-00959: tablespace 'TEMPT' does not exist

it is also funny, that I now can neither use, nor drop the temp table 'te':

SCOTT@andrkydb> drop table te;
drop table te
*
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already in use

Metalink article (Doc ID: Note:270645.1) says that table need to be truncated before drop. But it still doesn't help. I cannot drop the table.

If I recreate the tablespace 'tempt' I can use the table but still cannot drop it!

Instance restart helped...

This problem doesn't appear if we operate only with default temporary tablespaces.

Archive logs FORMAT

You use LOG_ARCHIVE_FORMAT parameter to specify how will the archive log files look like. You have the following possible variables:

%s log sequence number
%S log sequence number, zero filled
%t thread number
%T thread number, zero filled
%a activation ID
%d database ID
%r resetlogs ID that ensures unique names are constructed for the archived log files across multiple incarnations of the database

There is not a single word in the document that some of the variables are actually REQUIRED
So after setting it to something like

ALTER SYSTEM SET log_archive_format='%d_%s_%r.arc' SCOPE=SPFILE;

After restarting the instance you will get an error like this:

ORA-19905: log_archive_format must contain %s, %t and %r

Why not to do the check when the parameter is actually set? Instead oracle chooses to check it on startup and you end up with an unstartable instance. You also cannot restore the spfile from autobackup, because rman needs instance running at least in NOMOUNT mode. The only option is to boot with a pfile (no matter where you'll get it) . But this is the only solution oracle offers you

It also seems that you cannot affect the format of archive logs saved to the DESTINATION 10, which is set to USE_DB_RECOVERY_FILE_DEST value. It must be that oracle manages filenames on his own inside the recovery area.

RMAN Point-in-time recovery

Metalink Docid Note:370500.1

RMAN> run {
set until time "to_date('10.07.07 17:23:36','dd.mm.yy hh24:mi:ss')";
restore database;
recover database;
}

This shit is not working if you don't set the NLS_LANG environment variable. It fails with an error like:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Also note how is the date parameter passed to the set until time. it's in double-quotes on some reason.

Compile invalid objects

Article at orace-base

SQL*Plus script to list not valid objects:

COLUMN object_name FORMAT A30
SELECT owner,
   object_type,
   object_name,
   status
FROM   dba_objects
WHERE  status != 'VALID'
ORDER BY owner, object_type, object_name;

To compile automatically use one of these:

UTL_RECOMP.RECOMP_SERIAL(schema=>'SCOTT');

-- if schema is null, then all objects will be compiled

EXEC DBMS_UTILITY.compile_schema(schema => 'SCOTT');