September 6th, 2010 By hoekstra Categories: Oracle

Seit Oracle 11g kann man aus der Datenbank nicht mehr ungehindert auf andere Rechner im Netzwerk zugreifen. Der Zugriff auf Netzwerkressourcen wird jetzt mittels ACL (Access Control Lists) verwaltet. Der nachfolgende Code ertsellt eine universelle ACL, mit der wieder überall zugegriffen werden kann. Der Parameter “Principal” gibt an, für wen diese ACL gilt. Verwendet man hier PUBLIC, dann ist wieder alles so, wie es in Oracle 10g war. Natürlich kann hier aber jeder USER oder jede ROLLE angegeben werden. Außerdem kann man natürlich beliebig viele ACL’s für unterschiedliche USER definieren.

begin

if dbms_db_version.ver_le_10_2 then null; else begin dbms_network_acl_admin.drop_acl(acl => 'all-network-PUBLIC.xml'); exception when others then null; end; dbms_network_acl_admin.create_acl(acl => 'all-network-PUBLIC.xml', description => 'Netzwerk-Connects fuer ALLE', principal => 'PUBLIC', is_grant => true, privilege => 'connect'); DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'all-network-PUBLIC.xml', principal => 'PUBLIC', is_grant => true, privilege => 'resolve'); dbms_network_acl_admin.assign_acl(acl => 'all-network-PUBLIC.xml', host => '*'); end if; end;
September 6th, 2010 By hoekstra 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;
September 6th, 2010 By hoekstra Categories: Oracle
Eigentlich ist es ja nicht supported, und in einer produktiven Umgebung würde ich dazu auch nicht raten, aber in einer Test- oder Entwicklungsdatenbank kann es schon mal sinnvoll sein,
einem Oracle Schema einen neuen Namen zu geben.

Die folgende Prozedur habe ich dazu vor einiger Zeit entwickelt, und sie hat sich bisher bewährt:

create or replace procedure RenameUser(OldName varchar2, NewName varchar2, NewPassword varchar2) is
sessions number;
USER_LOGGED_IN exception;
user_id number;
pragma exception_init(USER_LOGGED_IN , -20001);
begin
  select count(*) into sessions from v$session where username = OldName and status != 'KILLED';
  if (sessions > 0) then
     raise_application_error(-20001, 'Can''t rename a user while he is logged in!');
  end if;
  select user# into user_id from user$ where name = OldName;
  update user$ set name = NewName where user# = user_id and name = OldName;
  commit;
  execute immediate 'alter system flush shared_pool';
  execute immediate 'alter user ' || NewName ||' identified by ' || NewPassword ;
end RenameUser; 

Die Neuvergabe das Passwortes ist erforderlich, da bei der Verschlüsselung des Passwortes unter Anderem auch der Benutzername verwendet wird.


September 6th, 2010 By hoekstra Categories: Oracle

ORA-01552: System-Rollback-Segment nicht für Nicht-System-Tablespace ‘XXX’ verwendbar

Dieser Fehler tritt meistens auf, wenn das UNDO_TABLESPACE nicht online ist oder es beschädigt wurde.

Diese Abfrage bringt Klarheit:

Code:

SELECT * FROM DBA_ROLLBACK_SEGS;

Wenn hier der Status ‘NEEDS RECOVERY’ gezeigt wird, ist meistens das Datafile des UNDO_TABLESPACE offline.

In diesem Fall ist die Lösung einfach:

Code:

SELECT * FROM v$datafile;

Die Dateinummer ermitteln, die offline ist, meistens ’2′.

Dann als SYSDBA:

Code:

ALTER DATABASE DATAFILE 2 online;

Und dann ein Recovery durchführen.

Entweder online:
Code:

RECOVER DATAFILE 2;

oder implizit bei einem Neustart:
Code:

SHUTDOWN IMMEDIATE;
STARTUP;

Code:

SELECT * FROM DBA_ROLLBACK_SEGS;

Zeigt jetzt keine Recovery Anforderung mehr, der Fehler ist behoben.

Bei einem Beschädigten UNDO_TABLESPACE startet die Datenbank nicht mehr. Wenn der Fehler also bemerkt wird, solange die Datenbank noch läuft, ALS ERSTES ALLE DATEN EXPORTIEREN.

Dann am besten die Instanz neu aufbauen.

August 28th, 2010 By hoekstra Categories: Allgemein

Herzlich willkommen!

Diese Seite wird gerade überarbeitet.

Meine Photos gibt es ab sofort hier.

Hier wird es zukünftig wieder um die Themen Oracle und .Net gehen. Außerdem werde ich hier ausgewählte Photografien zeigen.

Umfangreiche Sammlungen von Bildern gibt es wie gewohnt unter der alten Adresse: www.ThomasHoekstra.eu/Index.php