Differences

This shows you the differences between two versions of the page.

--- dba:oracle:howtos:rename_table_mini-howto [2023/01/31 08:28] – removed - external edit (Unknown date) 127.0.0.1
+++ dba:oracle:howtos:rename_table_mini-howto [2023/01/31 08:28] (current) – ↷ Page moved from dba:oracle:rename_table_mini-howto to dba:oracle:howtos:rename_table_mini-howto dodger
@@ Line 1: / Line 1: @@
+====== [HOWTO] Rename a table in oracle ======
+====== Descripción ======
+Este procedimiento permite renombrar una tabla cuando:
+<code>RENAME TABLE1 TO TABLE2 ;</code>
+Falla, por ejemplo con ''ORA-03001: unimplemented feature''.
+====== Instrucciones ======
+===== ALTER TABLE =====
+Mediante un ''ALTER TABLE'' se puede hacer:
+<code SQL>
+ALTER TABLE T1 RENAME TO T2 ;
+</code>
+Debería funcionar.
+===== Crear el siguiente procedure =====
+<code>
+CREATE OR REPLACE PROCEDURE RENAME_OTHERS_TAB (ORIG_TAB_NAME IN VARCHAR2, NEW_TAB_NAME IN VARCHAR2)
+AS
+	LV_SQL_STR VARCHAR2(100);
+	LV_EXISTS NUMBER(1);
+	BEGIN
+		SELECT COUNT(*) INTO LV_EXISTS FROM USER_TABLES WHERE TABLE_NAME=ORIG_TAB_NAME;
+		IF LV_EXISTS = 1 THEN
+			LV_SQL_STR := 'RENAME '|| ORIG_TAB_NAME ||' TO '||NEW_TAB_NAME;
+			DBMS_OUTPUT.PUT_LINE (LV_SQL_STR);
+			EXECUTE IMMEDIATE LV_SQL_STR;
+		ELSE
+			DBMS_OUTPUT.PUT_LINE('ERROR:'||ORIG_TAB_NAME||' DOES NOT EXISTS!');
+		END IF;
+	END;
+/
+</code>
+===== Ejecutar el procedimiento =====
+<code>
+exec rename_others_tab('TABLE1','TABLE2');
+</code>
+====== thanks to ======
+[[http://chandrapabba.blogspot.com.es/2009/09/ora-03001-while-trying-rename-tables.html|chandra]]