Ciberterminal Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.

====== [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]]