Differences

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

--- oracle:rename_table_mini-howto [2018/04/27 08:55] – dodger
+++ dba:oracle:howtos:rename_table_mini-howto [2023/01/31 08:28] – removed - external edit (Unknown date) 127.0.0.1
@@ 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]]