Oracle User bzw. Schema in anderes Tablespace verschieben

September 6th, 2010 Categories: Oracle
Die folgende Prozedur verschiebt alle Datensegmente eines Users in ein anderes (ggf. neues) Tablespace.

Eventuelle Fehler werden über DBMS_ALERT gemeldet. Alert Name: MOVE_{User Name}. Diese Meldungen können z.B. mit dem PL/SQL Developer unter Tools->Event Monitor gelesen werden.

Parameter:

  • USERNAME: Der Name des Users
  • NEW_TS: Der Name des neuen Tablespaces.
  • TS_SIZE: Die Größe für das neue Tablespace (z.B. 2G, 150M, …)

Das Tablespace wird automatisch angelegt, falls es nicht existiert.
Wenn es existiert, wird TS_SIZE nicht ausgewertet.

Die Datendatei wird im Standardpfad angelegt, Oracle Managed Files wird dringend empfohlen. Sonst den Tablespace besser selbst anlegen.

create or replace procedure MOVE_USER(USERNAME VARCHAR2,
						  NEW_TS   VARCHAR2,
						  TS_SIZE  VARCHAR2) is
  i INTEGER;
BEGIN
  SELECT COUNT(*)
    INTO i
    FROM dba_tablespaces ts
   WHERE ts.tablespace_name = upper(NEW_TS);
  IF (i = 0) THEN
    EXECUTE IMMEDIATE 'CREATE TABLESPACE ' || NEW_TS || ' DATAFILE SIZE ' ||
			    TS_SIZE || ' AUTOEXTEND ON MAXSIZE UNLIMITED';
  END IF;

  FOR tab IN (SELECT table_name
		    FROM all_tables
		   WHERE owner = upper(username)) LOOP
    BEGIN
	EXECUTE IMMEDIATE 'ALTER TABLE ' || tab.table_name ||
				' MOVE TABLESPACE ' || NEW_TS;
	FOR IDX IN (SELECT index_name
			  FROM All_Indexes t
			 WHERE owner = upper(username)
			   AND table_name = tab.table_name) LOOP
	  BEGIN
	    EXECUTE IMMEDIATE 'ALTER INDEX ' || idx.index_name ||
				    ' REBUILD TABLESPACE ' || NEW_TS;
	  EXCEPTION
	    WHEN OTHERS THEN
		dbms_alert.signal('MOVE_' || USERNAME,
					'Error moving Index ' || idx.index_name || ':' ||
					SQLERRM);
		COMMIT;
	  END;
	END LOOP;
	for lob in (select column_name
			  from user_tab_columns
			 where data_type like '%LOB%'
			   and table_name = tab.table_name) loop
	  begin
	    execute immediate 'alter table ' || tab.table_name ||
				    ' move lob(' || lob.column_name ||
				    ') store as (tablespace ' || NEW_TS || ')';
	  EXCEPTION
	    WHEN OTHERS THEN
		dbms_alert.signal('MOVE_' || USERNAME,
					'Error moving Lob '|| lob.column_name || ' in Table ' ||
					tab.table_name || ':' || SQLERRM);
		COMMIT;
	  END;
	end loop;
    EXCEPTION
	WHEN OTHERS THEN
	  dbms_alert.signal('MOVE_' || USERNAME,
				  'Error moving Table ' || tab.table_name || ':' ||
				  SQLERRM);
	  COMMIT;
    END;
  END LOOP;

EXCEPTION
  WHEN OTHERS THEN
    dbms_alert.signal('MOVE_' || USERNAME, 'Generic error:' || SQLERRM);
    COMMIT;
end MOVE_USER;
Tags:

One Response to “Oracle User bzw. Schema in anderes Tablespace verschieben”

  1. März 6th, 2016 at 07:03
    1

    Our limousine service in Fort Lauderdale, West Palm Beach limo and Miami gives you this
    great opportunity to express your feelings. As a social creature every
    human being has some social commitment for their
    family. The clasp on top of the handbag is a silver-toned push lock.

Leave a Comment