diff options
author | Pierre Schmitz <pierre@archlinux.de> | 2011-12-03 13:29:22 +0100 |
---|---|---|
committer | Pierre Schmitz <pierre@archlinux.de> | 2011-12-03 13:29:22 +0100 |
commit | ca32f08966f1b51fcb19460f0996bb0c4048e6fe (patch) | |
tree | ec04cc15b867bc21eedca904cea9af0254531a11 /maintenance/oracle/archives/patch_rebuild_dupfunc.sql | |
parent | a22fbfc60f36f5f7ee10d5ae6fe347340c2ee67c (diff) |
Update to MediaWiki 1.18.0
* also update ArchLinux skin to chagnes in MonoBook
* Use only css to hide our menu bar when printing
Diffstat (limited to 'maintenance/oracle/archives/patch_rebuild_dupfunc.sql')
-rw-r--r-- | maintenance/oracle/archives/patch_rebuild_dupfunc.sql | 146 |
1 files changed, 146 insertions, 0 deletions
diff --git a/maintenance/oracle/archives/patch_rebuild_dupfunc.sql b/maintenance/oracle/archives/patch_rebuild_dupfunc.sql new file mode 100644 index 00000000..0a232dbc --- /dev/null +++ b/maintenance/oracle/archives/patch_rebuild_dupfunc.sql @@ -0,0 +1,146 @@ +/*$mw$*/ +CREATE OR REPLACE PROCEDURE duplicate_table(p_tabname IN VARCHAR2, + p_oldprefix IN VARCHAR2, + p_newprefix IN VARCHAR2, + p_temporary IN BOOLEAN) IS + e_table_not_exist EXCEPTION; + PRAGMA EXCEPTION_INIT(e_table_not_exist, -00942); + l_temp_ei_sql VARCHAR2(2000); + l_temporary BOOLEAN := p_temporary; +BEGIN + BEGIN + EXECUTE IMMEDIATE 'DROP TABLE ' || p_newprefix || p_tabname || + ' CASCADE CONSTRAINTS'; + EXCEPTION + WHEN e_table_not_exist THEN + NULL; + END; + IF (p_tabname = 'SEARCHINDEX') THEN + l_temporary := FALSE; + END IF; + IF (l_temporary) THEN + EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE ' || p_newprefix || + p_tabname || ' AS SELECT * FROM ' || p_oldprefix || + p_tabname || ' WHERE ROWNUM = 0'; + ELSE + EXECUTE IMMEDIATE 'CREATE TABLE ' || p_newprefix || p_tabname || + ' AS SELECT * FROM ' || p_oldprefix || p_tabname || + ' WHERE ROWNUM = 0'; + END IF; + FOR rc IN (SELECT column_name, data_default + FROM user_tab_columns + WHERE table_name = p_oldprefix || p_tabname + AND data_default IS NOT NULL) LOOP + EXECUTE IMMEDIATE 'ALTER TABLE ' || p_newprefix || p_tabname || + ' MODIFY ' || rc.column_name || ' DEFAULT ' || + SUBSTR(rc.data_default, 1, 2000); + END LOOP; + FOR rc IN (SELECT REPLACE(REPLACE(DBMS_LOB.SUBSTR(DBMS_METADATA.get_ddl('CONSTRAINT', + constraint_name), + 32767, + 1), + USER || '"."' || p_oldprefix, + USER || '"."' || p_newprefix), + '"' || constraint_name || '"', + '"' || p_newprefix || constraint_name || '"') DDLVC2, + constraint_name + FROM user_constraints uc + WHERE table_name = p_oldprefix || p_tabname + AND constraint_type = 'P') LOOP + l_temp_ei_sql := SUBSTR(rc.ddlvc2, 1, INSTR(rc.ddlvc2, 'PCTFREE') - 1); + l_temp_ei_sql := SUBSTR(l_temp_ei_sql, + 1, + INSTR(l_temp_ei_sql, + ')', + INSTR(l_temp_ei_sql, 'PRIMARY KEY') + 1) + 1); + IF nvl(length(l_temp_ei_sql), 0) > 0 THEN + EXECUTE IMMEDIATE l_temp_ei_sql; + END IF; + END LOOP; + IF (NOT l_temporary) THEN + FOR rc IN (SELECT REPLACE(DBMS_LOB.SUBSTR(DBMS_METADATA.get_ddl('REF_CONSTRAINT', + constraint_name), + 32767, + 1), + USER || '"."' || p_oldprefix, + USER || '"."' || p_newprefix) DDLVC2, + constraint_name + FROM user_constraints uc + WHERE table_name = p_oldprefix || p_tabname + AND constraint_type = 'R') LOOP + IF nvl(length(l_temp_ei_sql), 0) > 0 THEN + EXECUTE IMMEDIATE l_temp_ei_sql; + END IF; + END LOOP; + END IF; + FOR rc IN (SELECT REPLACE(REPLACE(DBMS_LOB.SUBSTR(DBMS_METADATA.get_ddl('INDEX', + index_name), + 32767, + 1), + USER || '"."' || p_oldprefix, + USER || '"."' || p_newprefix), + '"' || index_name || '"', + '"' || p_newprefix || index_name || '"') DDLVC2, + index_name, + index_type + FROM user_indexes ui + WHERE table_name = p_oldprefix || p_tabname + AND index_type NOT IN ('LOB', 'DOMAIN') + AND NOT EXISTS + (SELECT NULL + FROM user_constraints + WHERE table_name = ui.table_name + AND constraint_name = ui.index_name)) LOOP + l_temp_ei_sql := SUBSTR(rc.ddlvc2, 1, INSTR(rc.ddlvc2, 'PCTFREE') - 1); + l_temp_ei_sql := SUBSTR(l_temp_ei_sql, + 1, + INSTR(l_temp_ei_sql, + ')', + INSTR(l_temp_ei_sql, + '"' || USER || '"."' || p_newprefix || '"') + 1) + 1); + IF nvl(length(l_temp_ei_sql), 0) > 0 THEN + EXECUTE IMMEDIATE l_temp_ei_sql; + END IF; + END LOOP; + FOR rc IN (SELECT REPLACE(REPLACE(DBMS_LOB.SUBSTR(DBMS_METADATA.get_ddl('INDEX', + index_name), + 32767, + 1), + USER || '"."' || p_oldprefix, + USER || '"."' || p_newprefix), + '"' || index_name || '"', + '"' || p_newprefix || index_name || '"') DDLVC2, + index_name, + index_type + FROM user_indexes ui + WHERE table_name = p_oldprefix || p_tabname + AND index_type = 'DOMAIN' + AND NOT EXISTS + (SELECT NULL + FROM user_constraints + WHERE table_name = ui.table_name + AND constraint_name = ui.index_name)) LOOP + l_temp_ei_sql := rc.ddlvc2; + IF nvl(length(l_temp_ei_sql), 0) > 0 THEN + EXECUTE IMMEDIATE l_temp_ei_sql; + END IF; + END LOOP; + FOR rc IN (SELECT REPLACE(REPLACE(UPPER(DBMS_LOB.SUBSTR(DBMS_METADATA.get_ddl('TRIGGER', + trigger_name), + 32767, + 1)), + USER || '"."' || p_oldprefix, + USER || '"."' || p_newprefix), + ' ON ' || p_oldprefix || p_tabname, + ' ON ' || p_newprefix || p_tabname) DDLVC2, + trigger_name + FROM user_triggers + WHERE table_name = p_oldprefix || p_tabname) LOOP + l_temp_ei_sql := SUBSTR(rc.ddlvc2, 1, INSTR(rc.ddlvc2, 'ALTER ') - 1); + IF nvl(length(l_temp_ei_sql), 0) > 0 THEN + EXECUTE IMMEDIATE l_temp_ei_sql; + END IF; + END LOOP; +END; +/*$mw$*/ + |