db2中的schema如何重命名?
发布网友
发布时间：2022-04-09 08:00
共2个回答
懂视网
时间：2022-04-09 12:21
set echo on feedback on
spool ./log/01_change_schema.log
-- -- schema change to v_idocdata_un --
connect &v_testdata_un/&v_testdata_pw@&v_conn_str
show user
prompt &v_conn_str prompt &v_conn_str
----------------------------- ---Developer Section place the code here ----------------------------- @./rollout/01_schema_rollout.sql
----
connect &v_testdata_un/&v_testdata_pw@&v_conn_str
show user
prompt &v_conn_str
@call_grant_dml &v_testusr_un
@call_grant_dml &v_testpatch_un
@call_grant_sel &v_testusr_un
@call_grant_sel &v_testpatch_un
@call_grant_sel &v_testquery_un
@call_grant_exec &v_testusr_un
@call_grant_exec &v_testpatch_un
disc
-- parameters
-- define v_input_un = &1
-- define v_input_pw = &2
-- define v_input_conn_str = &3
-- define v_input_owner = &4
-- define v_reconnect_un = &5
-- define v_reconnect_pw = &6
@call_create_syn &v_testusr_un &v_testusr_pw &v_conn_str &v_testdata_un &v_testdata_un &v_testdata_pw
@call_create_syn &v_testpatch_un &v_testpatch_pw &v_conn_str &v_testdata_un &v_testdata_un &v_testdata_pw
@call_create_syn &v_testquery_un &v_testquery_pw &v_conn_str &v_testdata_un &v_testdata_un &v_testdata_pw
@call_compile &v_testdata_un &v_testdata_pw &v_conn_str &v_testdata_un &v_testdata_pw
@call_compile &v_testusr_un &v_testusr_pw &v_conn_str &v_testdata_un &v_testdata_pw
@call_compile &v_testpatch_un &v_testpatch_pw &v_conn_str &v_testdata_un &v_testdata_pw
@call_compile &v_testquery_un &v_testquery_pw &v_conn_str &v_testdata_un &v_testdata_pw
spool off;
prompt ================================================================================
prompt Checking logs in the following directory ...
prompt
host pwd
prompt
prompt ======================================== grep -i ‘ora-‘
host grep -i ‘ora-‘ ./log/*.log | sort -u
prompt
prompt ======================================== grep -i ‘sp2-‘
host grep -i ‘sp2-‘ ./log/*.log | sort -u
prompt
prompt ======================================== grep -i ‘^Warning:‘
host grep -i ‘^Warning:‘ ./log/*.log | sort -u
prompt
01_change_schema.sql
标签：
热心网友
时间：2022-04-09 09:29
重命名schema ，相当于要重建schema，然后做表迁移。
具体步骤：参考下面的link
网页链接
1) Issue "db2look -d <DBNAME> -e -o <outputfile.sql>"
2) In the outputfile.sql file, find and change the schema to your new schema name. You can use UNIX text editor Vi and edit the file by use the following command ":1,$s/<SEARCH>/<REPLACE>/g" to search and replace or use any text editor that you desire.
3) Issue "db2move <dbname> export". This will create several files, including a db2move.lst file, in your current directory.
4) You will also need to edit the generated db2move.lst file and change the schema names to the new desired name.
5) Drop existing user tables (this step is for renaming the schema within the same database)
6) Issue "db2 -tvf outputfile.sql" (this runs the output file generated by the db2look command to recreate the tables with the new schema name)
7) Issue "db2move <dbname> import" (to import the data into the tables.)
