Shrink temporary tablespace

Der schnellste Weg, einen temporären Tablespace zu verkleinern, ist, einen neuen temporären TS anzulegen, alle User des alten auf den neuen umzuleiten und den alten zu droppen. Da die Extents eines temporären Tablespaces erst beim Herunterfahren der Instanz freigegeben werden, läuft ein SHRINK oder RESIZE in der Regel auf Fehler.

Also funktioniert in 10g folgendes:

-- Anlage eines neuen temporären TS
create temporary tablespace temp1 tempfile …;

-- Generierung von Queries, mit denen allen DB-User 
-- der neue temporären TS zugeordnet wird
select 'alter user ' || username || ' temporary tablespace temp1;'
  from dba_users
 where temporary_tablespace = 'TEMP';

-- den neuen temporären TS als default TS setzen
alter database default temporary tablespace temp1;

-- alle Sessions beenden, die noch Einträge in v$sort_usage halten

-- jetzt kann man den alten temporären TS wegwerfen
drop tablespace temp;

alter tablespace temp1 rename to temp;

Nachtrag 16.05.2011: In 11.2.0.1 funktioniert das Vorgehen immer noch (was nicht allzu sehr überrascht)

Nachtrag 23.03.2012: Tom Kyte weist dieser Tage darauf hin, dass das Verkleinern in 11g deutlich einfacher geworden ist - es genügt ein "alter tablespace temp_xyz shrink space".

Connect by level

zu Dokumentierungszwecken:

select rownum
  from dual
connect by level <= 10
/

    ROWNUM
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 Zeilen ausgewählt.

Als Nummerngenerator verwendbar.

Letzte Tabellenänderungen

Das Datum der letzten Strukturänderung findet man in user_objects, also z.B.:

select object_name
     , last_ddl_time
  from user_objects  
 where object_name = 'NAME_DER_TABELLE'    
   and object_type = 'TABLE';

Die letzte Änderung eines Datensatzes kann man sich in 10g mit Hilfe der Pseudocolumn ORA_ROWSCN anzeigen lassen. Diese Pseudocolumn liefert die SCN der letzten Änderung des Blocks, in dem sich der Datensatz befindet (theoretisch kann man auch die Änderung des Einzelsatzes berücksichtigen, aber dazu muss die Tabelle mit der Option <rowdependencies> erstellt worden sein). Diese SCN kann man sich über eine Funktion in einen Timestamp umwandeln lassen (was aber nur in einem bestimmten Zeitraum funktioniert, da das Mapping SCN - Timestamp offenbar nicht dauerhaft gespeichert wird):

select ora_rowscn
     , scn_to_timestamp(ora_rowscn)
  from NAME_DER_TABELLE;

Für Versionen vor 10g fällt mir keine so simple Methode ein, um Datensatzänderungen zu ermitteln. Da könnte man vielleicht mit Audit, dem Logminer oder gruseligen Triggern arbeiten, aber das alles wäre jedenfalls deutlich aufwändiger.

Nachtrag 04.05.2011: da dieser Eintrag offenbar relativ häufig aufgerufen wird, hier noch der Verweis auf Tanel Poders lastchanged.sql-Script, das noch ein paar Schritte über die hier gezeigten Möglichkeiten hinaus geht. Der Link zum download des Scripts auf der angesprochenen Seite scheint aktuell nicht zu funktionieren, aber möglicherweise nur temporär, da der Herr Poder schreibt: "I plan to fix the broken links some time between now and my retirement." In der Zwischenzeit empfiehlt er, die komplette Script-Sammlung zu laden - und das lohnt sich in diesem Fall ganz gewiß.

Current_Sessions

Das folgende Statement liefert ein paar allgemeine Informationen zu den aktiven Sessions. Die spid ist enthalten, um unter Windows eine Zuordnung der sid zum zugehörigen Trace-file zu ermöglichen. Lesbar wird die Ausgabe unter Windows mit einem entsprechend dimensionierten DOS-Fenster und passenden sqlplus-Einstellungen:

set lines 2000
set tab off

select s.osuser,
       s.username,
       s.sid || ',' || s.serial# sid_serial#,
       p.spid,
       s.terminal,
       s.module,
       s.seconds_in_wait,
       to_char(s.logon_time, 'dd.mm.yyyy hh24:mi:ss') logon_time,
       sql.sql_text
  from v$sqlarea sql
     , v$process p
  , v$session s
 where s.paddr = p.addr
   and s.sql_address = sql.address (+)
   and s.sql_hash_value = sql.hash_value (+)
   and s.username is not null
order by s.osuser, s.username, s.sid
/

Reorganisation von Tablespaces

In 10g gibt es zwar einen Shrink-Befehl für Tabellen, aber eine Möglichkeit, einen TS automatisch zu reorganisieren, ist mir nicht bekannt. Da ist man dann auf exp/imp (bzw. expdp/impdp) und alter table ... move tablespace ... (bzw. alter index ... rebuild tablespace ...) angewiesen.

Aus diesem Grund verwende ich meine TS immer als logische Einheiten für zusammengehörige Elemente, die ich im Fall einer Löschung komplett loswerden möchte (also Tabellen und zugehörige Indizes; damit ich dann ggf. den TS samt allen data files wegwerfen kann).

Die Trennung von Indizes und Tabellen war meines Wissens früher einmal aus Performancegründen interessant (zur Verteilung von IOs), aber heute spielt das normalerweise keine Rolle mehr (wg. RAID etc.), was Tom Kyte gelegentlich erläutert hat (der hier ursprünglich angegebene AskTom-Link funktionierte nicht mehr).

REMAP_SCHEMA

Der Transfer von Daten aus einem Schema in ein anderes erfolgt beim Data Pump Import über den Parameter REMAP_SCHEMA (der an die Stelle der Parameter FromUser und ToUser des alten imp tritt). Der Befehl:

impdp hr/hr@db REMAP_SCHEMA=hr:scott

bewirkt somit den Import der hr-Objekte ins Schema Scott.

Die Verschiebung in einen anderen Tablespace erfolgt über den Parameter REMAP_TABLESPACE.