oracle数据库迁移
导出数据
首先测试exp命令能否使用，在cmd里输入exp，如果不报错说明可用，报错先百度解决报错！
查询环境变量
select * from nls_database_parameters t where t.parameter='NLS_CHARACTERSET';
EXP-00091报错需要修改环境变量:
NLS_LANG = AMERICAN_AMERICA.AL32UTF8
默认：SIMPLIFIED CHINESE_CHINA.ZHS16GBK
修改环境变量为查出的一样： AMERICAN_AMERICA.AL32UTF8
执行导出命令
cmd进入存放备份文件的目录
exp STD06010000/XFP@10.20.0.12:1521/ELAN11GR2 file=STD06010000.DMP owner=(STD06010000) log=STD06010000.log
exp ARCHXXXXTST/XFP@10.20.0.12:1521/ELAN11GR2 file=ARCHXXXXTST.DMP owner=(ARCHXXXXTST) log=ARCHXXXXTST.log
exp EXPXXXXTST/XFP@10.20.0.12:1521/ELAN11GR2 file=EXPXXXXTST.DMP owner=(EXPXXXXTST) log=EXPXXXXTST.log
exp LIVEXXXXTST/XFP@10.20.0.12:1521/ELAN11GR2 file=LIVEXXXXTST.DMP owner=(LIVEXXXXTST) log=LIVEXXXXTST.log
exp XSIXXXXTST/XFP@10.20.0.12:1521/ELAN11GR2 file=XSIXXXXTST.DMP owner=(XSIXXXXTST) log=XSIXXXXTST.log
完整导出（数据量小使用）：
exp SYSTEM/XFP@10.20.0.12:1521/ELAN11GR2 file=STD06010000.DMP FULL=Y log=STD06010000.log
导入数据
创建用户：
create user STD06010000 identified by XFP;
create user LIVEXXXXTST identified by XFP;
create user XSIXXXXTST identified by XFP;
create user EXPXXXXTST identified by XFP;
create user ARCHXXXXTST identified by XFP;
授权表空间：
grant resource to STD06010000 ;
grant resource to LIVEXXXXTST ;
grant resource to XSIXXXXTST ;
grant resource to EXPXXXXTST ;
grant resource to ARCHXXXXTST ;
导入数据：
imp system/oracle@IP:PORT/orcl file=STD06010000.DMP log=IMP_STD06010000.log fromuser=STD06010000 touser=STD06010000
imp system/oracle@IP:PORT/orcl file=LIVEXXXXTST.DMP log=LIVEXXXXTST.log fromuser=LIVEXXXXTST touser=LIVEXXXXTST
imp system/oracle@IP:PORT/orcl file=ARCHXXXXTST.DMP log=IMP_ARCHXXXXTST.log fromuser=ARCHXXXXTST touser=ARCHXXXXTST
imp system/oracle@IP:PORT/orcl file=XSIXXXXTST.DMP log=IMP_XSIXXXXTST.log fromuser=XSIXXXXTST touser=XSIXXXXTST
imp system/oracle@IP:PORT/orcl file=EXPXXXXTST.DMP log=IMP_EXPXXXXTST.log fromuser=EXPXXXXTST touser=EXPXXXXTST
导入数据后可能需要重新编译视图
创建授权查询视图：
CREATE OR REPLACE VIEW VW_USER_PRIVS_LHR AS SELECT D.GRANTEE, CASE WHEN D.ADMIN_OPTION = 'YES' THEN 'GRANT ' || D.PRIVILEGE || ' TO ' || D.GRANTEE || ' WITH GRANT OPTION ;' ELSE 'GRANT ' || D.PRIVILEGE || ' TO ' || D.GRANTEE || ';' END PRIV, 'SYSTEM_GRANT' TYPE, 'DBA_SYS_PRIVS' FROM_VIEW FROM DBA_SYS_PRIVS D UNION ALL SELECT D.GRANTEE, CASE WHEN D.ADMIN_OPTION = 'YES' THEN 'GRANT ' || D.GRANTED_ROLE || ' TO ' || D.GRANTEE || ' WITH GRANT OPTION;' ELSE 'GRANT ' || D.GRANTED_ROLE || ' TO ' || D.GRANTEE || ';' END PRIV, 'SYSTEM_GRANT' TYPE, 'DBA_SYS_PRIVS' FROM_VIEW FROM DBA_ROLE_PRIVS D UNION ALL SELECT D.GRANTEE, CASE WHEN D.GRANTABLE = 'YES' THEN 'GRANT ' || D.PRIVILEGE || ' ON ' || D.OWNER || '.' || D.TABLE_NAME || ' TO ' || D.GRANTEE || ' WITH GRANT OPTION ;' ELSE 'GRANT ' || D.PRIVILEGE || ' ON ' || D.OWNER || '.' || D.TABLE_NAME || ' TO ' || D.GRANTEE || ';' END PRIV, 'SYSTEM_GRANT' TYPE, 'DBA_SYS_PRIVS' FROM_VIEW FROM DBA_TAB_PRIVS D where D.TABLE_NAME not like 'BIN%' UNION ALL SELECT D.GRANTEE, CASE WHEN D.GRANTABLE = 'YES' THEN 'GRANT ' || D.PRIVILEGE || ' (' || D.COLUMN_NAME || ') ON ' || D.OWNER || '.' || D.TABLE_NAME || ' TO ' || D.GRANTEE || ' WITH GRANT OPTION ;' ELSE 'GRANT ' || D.PRIVILEGE || ' (' || D.COLUMN_NAME || ') ON ' || D.OWNER || '.' || D.TABLE_NAME || ' TO ' || D.GRANTEE || ';' END PRIV, 'COL_GRANT' TYPE, 'DBA_COL_PRIVS' FROM_VIEW FROM DBA_COL_PRIVS D where D.TABLE_NAME not like 'BIN%';
查询用户的授权：
SELECT * FROM VW_USER_PRIVS_LHR D WHERE D.GRANTEE = ‘STD06010000’;
SELECT * FROM VW_USER_PRIVS_LHR D WHERE D.GRANTEE = ‘LIVEXXXXTST’;
SELECT * FROM VW_USER_PRIVS_LHR D WHERE D.GRANTEE = ‘XSIXXXXTST’;
SELECT * FROM VW_USER_PRIVS_LHR D WHERE D.GRANTEE = ‘EXPXXXXTST’;
SELECT * FROM VW_USER_PRIVS_LHR D WHERE D.GRANTEE = ‘ARCHXXXXTST’;
需要将授权全部在新库执行：
复制授权语句，执行！
其他常用命令
删除用户：
drop user LIVEXXXXTST cascade
连接数据库：
sqlplus /nolog
conn system/oracle@IP:PORT/orcl
sqlplus system/oracle@IP:PORT/orcl as sysdba
sqlplus sys/oracle@IP:PORT/orcl as sysdba
“ORACLE_HOME”=”E:\oracle\product\10g\db_1”
“ORACLE_SID”=”oratest”
“ORACLE_BASE”=”E:\oracle\product\10g”
show parameter service_name
SELECT --B.file_name "文件名",
A.TABLESPACE_NAME "表空间名",
TOTAL "表空间大小",
FREE "表空间剩余大小",
(TOTAL - FREE) "表空间使用大小",
TOTAL / (1024 * 1024 * 1024) "表空间大小(G)",
FREE / (1024 * 1024 * 1024) "表空间剩余大小(G)",
(TOTAL - FREE) / (1024 * 1024 * 1024) "表空间使用大小(G)",
ROUND((TOTAL - FREE) / TOTAL, 4) * 100 "使用率 %"
FROM (SELECT TABLESPACE_NAME,
SUM(BYTES) FREE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) A,
(SELECT TABLESPACE_NAME,
-- file_name,
SUM(BYTES) TOTAL
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME/*,file_name*/) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME;
select tablespace_name,file_name,autoextensible from dba_data_files ;
170 条回复
[…] buy nizoral 1 percent shampoo […]
[…] buy generic ventolin […]
[…] order clonidine online […]
[…] best place buy claritin […]
[…] """"""jackpot party casino"""" """"win"""" """"real money"""" """"no deposit"""""" […]
[…] pennsylvania online real money casino 2021 […]
[…] hollywood casino online real money pa bonus offers […]
[…] best results for 5mg cialis […]
[…] how much does cialis 5mg cost? […]
[…] sildenafil 50mg coupon […]
[…] 40 mg cialis reddit […]
[…] wellbutrin generic […]
[…] generic promethazine […]
[…] aps diclofenac sodium enteric coated tablets 50mg […]
[…] generic lisinopril […]
[…] prednisone price […]
[…] avalide (irbesartan and hydrochlorothiazide)tablets […]
[…] benadryl 2023 […]
[…] wild casino online real money […]
[…] t 20 pill cialis […]
[…] online casino real money no deposit bonus malaysia […]
[…] free online registration real money casino […]
[…] buspar 10mg […]
[…] pantoprazole […]
[…] best place to buy seroquel […]
[…] buy flonase cheap […]
[…] where can i buy zyrtec eye drops […]
[…] can i buy zofran over the counter […]
[…] cheapest place to get claritin […]
[…] buy cheap allopurinol […]
[…] what do clonidine pills do […]
[…] how to get lipitor cheap […]
[…] methotrexate buy uk […]
[…] celebrex […]
[…] how to buy naltrexone online […]
[…] 20 mg tadalafil pulmonary […]
[…] wait time cialis 20mg before sex […]
[…] topiramate online […]
[…] tadalafil 20mg price cvs […]
[…] amlodipine price […]
[…] buy lasix online cheap […]
[…] allegra discount card […]
[…] divalproex generic […]
[…] diltiazem […]
[…] cheap nexium tablets […]
[…] flomax sale […]
[…] tadalafil (adcirca) 20 mg weekender […]
[…] buy synthroid australia […]
[…] buy singulair tablets […]
[…] cialis coupon discounts […]
[…] cheapest viagra sale uk […]
[…] cialis 80 mg dosage […]
[…] vidalista tadalafil 20 […]
[…] is 20 mg of tadalafil safe […]
[…] order viagra with mastercard […]
cialis canada sale
cialis canada sale
amoxicillin and mucinex
amoxicillin and mucinex
why shouldn t you take tramadol with cymbalta
why shouldn t you take tramadol with cymbalta
cephalexin for sinus infection
cephalexin for sinus infection
what happens when you stop taking lexapro
what happens when you stop taking lexapro
how long fluoxetine take to work
how long fluoxetine take to work
gabapentin lorazepam
gabapentin lorazepam
duloxetine missed dose side effects
duloxetine missed dose side effects
sildenafil 200mg price
sildenafil 200mg price
西门子ebr数据迁移数据备份
https://shaneslcul.mpeblog.com/47432549/5-essential-elements-for-blue-frenchie-puppies
西门子ebr数据迁移数据备份
https://dekconnect.com/hostingsite/index.php/2020/03/14/pramote-sand-co-ltd/
西门子ebr数据迁移数据备份
https://audacecourtage.fr/faites-le-point-sur-vos-contrats-de-retraite/
西门子ebr数据迁移数据备份
https://www.loretojerez.com/peregrinacion-mariana-a-la-parroquia-castrense-de-ntra-sra-de-loreto-en-tablada/
is cephalexin a strong antibiotic?
is cephalexin a strong antibiotic?
西门子ebr数据迁移数据备份
https://www.merolifestyle.com/archives/20667
flomax inactive ingredients
flomax inactive ingredients
stopping effexor after 5 days
stopping effexor after 5 days
contrave before and after
contrave before and after
switching from citalopram to lexapro
switching from citalopram to lexapro
is augmentin the same as amoxicillin
is augmentin the same as amoxicillin
ddavp treatment nocturnal enuresis
ddavp treatment nocturnal enuresis
depakote indications
depakote indications
cozaar vs diovan
cozaar vs diovan
medication allopurinol
medication allopurinol
augmentin and pregnancy
augmentin and pregnancy
buspar mechanism of action
buspar mechanism of action
side effects of celecoxib 200 mg
side effects of celecoxib 200 mg
how does diltiazem work
how does diltiazem work
semaglutide oral for weight loss
semaglutide oral for weight loss
protonix and pregnancy
protonix and pregnancy
actos pepa
actos pepa
西门子ebr数据迁移数据备份
http://печник-томск.рф/баня-№6-г-томск-запустили-в-мужском-одо/
tamsulosin hydrochloride urimax 0.4mg
tamsulosin hydrochloride urimax 0.4mg
ivermectin otc
ivermectin otc
robaxin dosage for dogs
robaxin dosage for dogs
half life of sitagliptin
half life of sitagliptin
acarbose marcas
acarbose marcas
how long does it take for venlafaxine to work
how long does it take for venlafaxine to work
tizanidine and flexeril together
tizanidine and flexeril together
西门子ebr数据迁移数据备份
https://devotodigital.com.ar/2022/05/02/caritas-cambia-el-dia-de-atencion/
西门子ebr数据迁移数据备份
https://noveljk.com/the-enticing-ceos-chosen-bride-chapter-1054/
西门子ebr数据迁移数据备份
https://goksanreklam.com/adanada-kartivizit-uretimi/
西门子ebr数据迁移数据备份
https://www.walkingbyfaith.com.ng/numbers-chapter-16-explained/
cialis online pills
cialis online pills
西门子ebr数据迁移数据备份
https://www.adrizalia.com/publicacion-del-estudio-nuevas-tendencias-en-logistica/
avanafil vs tadalafil
avanafil vs tadalafil
vardenafil pulmonary hypertension
vardenafil pulmonary hypertension
ivermectin 90 mg
ivermectin 90 mg
vardenafil cialis
vardenafil cialis
price of stromectol
price of stromectol
sildenafil 20 mg coupon
sildenafil 20 mg coupon
西门子ebr数据迁移数据备份
https://www.cermes.net/au-revoir-jean-testa/
西门子ebr数据迁移数据备份
https://blackpearllimousine.com/black-pearl-limousine-happy-schleppers/
西门子ebr数据迁移数据备份
https://texttv.app/upp-pa-fotterna/
西门子ebr数据迁移数据备份
https://alivemedia.com/jimmy-kimmel-time-travels/
amoxil polymox trimox
amoxil polymox trimox
keflex dosage 500 mg 4 times a day for cellulitis
keflex dosage 500 mg 4 times a day for cellulitis
is trazodone for dogs the same as for humans
is trazodone for dogs the same as for humans
conditions treated by doxycycline
conditions treated by doxycycline
pregabalin dosage for back pain
pregabalin dosage for back pain
provigil and similar drugs
provigil and similar drugs
how long does cephalexin take to work
how long does cephalexin take to work
[…] meilleur casino en ligne […]
side effects of lisinopril hctz
side effects of lisinopril hctz
西门子ebr数据迁移数据备份
http://pbc.org.ph/index.php/component/k2/item/11/11?start=40
西门子ebr数据迁移数据备份
https://farmeraid.agssbd.org/2021/04/09/টবে-ফুল-বেশি-সময়-টিকিয়ে-রা/
西门子ebr数据迁移数据备份
https://matchamusing.com/trail-of-tears/
西门子ebr数据迁移数据备份
https://www.cciarmenie.fr/bonjour-tout-le-monde/
[…] propranolol 20mg […]
西门子ebr数据迁移数据备份
http://kunstskolenrammen.dk/handicaporganisationernes-hus/
西门子ebr数据迁移数据备份
https://innovativedentalcenter.com/hello-world/
[…] fml forte price […]
[…] budesonide 100mcg […]
[…] generic diphenhydramine […]
西门子ebr数据迁移数据备份
https://arutesolutions.com/an-analysis-on-the-change-of-atm-cash-transactions-between-2019-january-2021-september-in-turkey/
西门子ebr数据迁移数据备份
https://events-verleih.de/2017/02/27/es-werde-licht/
西门子ebr数据迁移数据备份
https://sixfigureconsultancy.com/strategies/failure-to-tighten-up-on-the-niche-or-the-message-results-in-the-brand-becoming-invisible/
西门子ebr数据迁移数据备份
https://www.berkes.sk/hello-world/
西门子ebr数据迁移数据备份
https://loancoach.info/personal-finance/e-shram-card-naamaankan-laabh-bhugataan-sthiti/
西门子ebr数据迁移数据备份
https://japan-takara.com/20240403-2/
西门子ebr数据迁移数据备份
https://tehnotrafic.ro/10-french-wine-regions-to-visit-for-amazing-views-and-delicious-vinos/
西门子ebr数据迁移数据备份
https://www.desiblitz.com/filmfusion/eerie-soundtracks-by-the-great-l-eina/
西门子ebr数据迁移数据备份
https://mynextme.co.uk/2020/06/25/dont-just-ride-it/
西门子ebr数据迁移数据备份
https://unitedshuttles.nl/trainen/
西门子ebr数据迁移数据备份
https://www.one-breath-away.com/my-personal-review-of-2023/
西门子ebr数据迁移数据备份
http://elysiumsalon.com/hair-tips/charlotte-belk-hair-and-makeup-for-fashion-editorial-in-mykonos-greece/comment-page-2
西门子ebr数据迁移数据备份
https://cambralocker.com/2019/08/30/hello-world/
西门子ebr数据迁移数据备份
https://laboutiquebleue.com/this-is-a-post-with-post-type-link/
西门子ebr数据迁移数据备份
https://www.redquanta.com/blog/our-team-structure-what-everyone-does/
西门子ebr数据迁移数据备份
https://middletonsfuneralservices.co.uk/navigating-hindu-funeral-customs-in-the-uk-a-comprehensive-guide/
西门子ebr数据迁移数据备份
https://appel-lorientvietnam.com/contactez-nous/
西门子ebr数据迁移数据备份
https://greeneos.it/pulito-benessere-ristorazione/
西门子ebr数据迁移数据备份
https://www.innova.co.ke/innova-signs-on-african-financial-services-limited/
西门子ebr数据迁移数据备份
https://fullloop.fr/les-reseaux-sociaux-en-2023-ce-quil-va-se-passer
西门子ebr数据迁移数据备份
https://www.yuyiii.com/blog/goa-reopens/
西门子ebr数据迁移数据备份
https://mijnlab.nl/wijzigingen-in-wet-en-regelgeving/
西门子ebr数据迁移数据备份
https://hc-hokutosei.com/archives/605
track location by phone number
Simple but effective mobile number tracker. Great for keeping tabs on company devices.
Konya SEO Uzmanı
Konya SEO uzmanı, Çatalhöyük’ü ilk sayfalara taşımaya ant içti.
buy instagram followers
The followers look decent enough, better than cheaper alternatives I’ve seen.
Web Tasarım Fiyatları
E-ticaret sitesi kurma konusunda cok iyiler. Siparislerim hizla artiyor.
football skills
football skills hello i am a football player
cristiano ronaldo skills
cristiano ronaldo skills hello i am a football player
西门子ebr数据迁移数据备份
https://rimafakih.com/2021/03/18/launched-new-balance-breast-cancer-awareness-apparel-line-in-boston-ma/
西门子ebr数据迁移数据备份
https://jamiegold.com/2018/09/variety-charity-poker-night-2018/
西门子ebr数据迁移数据备份
https://vadstudio.live/finance/the-week-ahead-busiest-week-of-earnings-season/
… [Trackback]
https://binhhutloc.vn/cach-dat-binh-hut-tai-loc/
西门子ebr数据迁移数据备份
https://www.fmd-be.be/november-29-2019-fmd-scad-patient-day-in-utrecht/
西门子ebr数据迁移数据备份
https://terrassareformas.net/audio-post/
西门子ebr数据迁移数据备份
https://www.tourdecartes.com/tour-de-cartes-explique-le-kiss-trick/
西门子ebr数据迁移数据备份
https://joriekephilippi.nl/daphne-ivo/
西门子ebr数据迁移数据备份
https://alincor.com/2023/02/10/future-proofing-hospitals/
西门子ebr数据迁移数据备份
https://educampus.it/educational-technology-and-mobile-learning/
西门子ebr数据迁移数据备份
https://www.centraalaziereizen.nl/handige-vluchten-naar-of-van-oezbekistan-vanuit-urgench/
西门子ebr数据迁移数据备份
https://www.giacomolayet.com/2016/04/18/blog-post-left-sidebar-8/