ORACLE實(shí)用操作手冊(cè)

1、中國(guó)民生銀行網(wǎng)管運(yùn)維人員日常運(yùn)維手冊(cè) （oracle 部分） 中國(guó)民生銀行科技開(kāi)發(fā)部項(xiàng)目名稱中國(guó)民生銀行全行網(wǎng)絡(luò)管理系統(tǒng)建設(shè)項(xiàng)目文檔編號(hào)文檔撰寫項(xiàng)目階段文檔密級(jí)文檔名稱編寫日期文檔主送文檔抄送審閱審閱日期編號(hào)描述版本作者審核發(fā)布日期001創(chuàng)建V1.0王永002 修改 V1.2魏緒浪常用oracle操作Sqlplus 登陸一、用戶操作1. 用戶的創(chuàng)建create user itrm identified by itrm default tablespace itpfm;2. 用戶授權(quán)及回收grant resource to itplatform;revoke resource from itpl

2、atform;3. 用戶的解鎖alter user itplatform account unlock;4. 用戶的密碼過(guò)期-查詢用戶用的哪個(gè)profileSELECT USERNAME,PROFILE FROM DBA_USERS;-查詢profile 用戶密碼生命周期SELECT * FROM dba_profiles s where file=DEFAULT AND RESOURCE_NAME=PASSWORD_LIFE_TIME;-修改profile,將用戶密碼的生命周期設(shè)置為無(wú)限ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME U

3、NLIMITED;沒(méi)有提示如下信息的，以后不會(huì)提示該錯(cuò)誤UDE-28002: 操作產(chǎn)生了 ORACLE 錯(cuò)誤 28002ORA-28002: the password will expire within 6 days5. 用戶的刪除drop user itplatform cascade;A) cascade:會(huì)將用戶下所有的對(duì)象一并刪除B) 如果當(dāng)C) 前用戶正在使用,則會(huì)報(bào)錯(cuò),cant drop user itplatform that current user.D) 解決問(wèn)題B，應(yīng)該先將該用戶的會(huì)話連接殺死，詳情請(qǐng)參考:六問(wèn)題排查二、表空間操作1.表空間的創(chuàng)建create tables

4、pace ITRM datafile /oracledata/ITRM.DBF logging /oracledata/ITRM.DBF size 50m on next 300m 30000m extent management local;2.表空間的擴(kuò)容alter SNMP add /oracledata1/ITUMP1/ IFU2. size 200m on next 300m 10000m extent management local;3.表空間利用率SELECT a.tablespace_name, a.bytes/1024/1024 Sum(MB), (a.bytes-b.by

5、tes)/1024/1024 used(MB), b.bytes/1024/1024 free(MB), round(a.bytes-b.bytes)/a.bytes)*100,2) percent_used FROM ( SELECT tablespace_name, SUM(bytes) bytes FROM dba_data_files GROUP BY tablespace_name ) a, ( SELECT tablespace_name, SUM(bytes) bytes, MAX(bytes) largest FROM dba_free_space GROUP BY table

6、space_name ) b WHERE a.tablespace_name=b.tablespace_name ORDER BY (a.bytes-b.bytes)/a.bytes) DESC;4.數(shù)據(jù)文件查看select * from dba_data_files;三、分區(qū)操作1.增加分區(qū)alter table KIS_SNMP add partition part_1302 values less than (1131025000000000);2.刪除分區(qū)表alter table KIS_SNMP drop PARTITION part_1302;3.修改字段alter table k

7、is_snmp modify(description varchar2(254 byte);5.清空表數(shù)據(jù)truncate table KIS_SNMP reuse storage;備注:truncate 清空表所有數(shù)據(jù)，生成最少量日志與undo數(shù)據(jù)delete 不帶where條件，清空表所有數(shù)據(jù)，生成大量日志undo數(shù)據(jù)drop 在刪除數(shù)據(jù)的同時(shí)刪除表結(jié)構(gòu)6.查詢某分區(qū)表中的數(shù)據(jù)select * from kis_snmp partition(part_151302);7.將原有的表進(jìn)行備份create table bb as select * from aa;8.創(chuàng)建視圖create vi

8、ew v_t2 as select * from kis_snmp where HOST=;四、索引操作1.對(duì)表創(chuàng)建本地分區(qū)索引create index N_KISSNMP_IDX on KIS_SNMP(Host,OidGroup) local tablespace N_DEX1;2.索引生效 alter index itmuserdc.NL_KISSNMP_IDX rebuild;3.查詢分區(qū)索引狀態(tài)select partition_name,status from dba_ind_partitions where index_name=N_KISSNMP_IDX;4.索引

9、失效alter index itmuserdc.N_KISSNMP_IDX unusable;五、導(dǎo)入導(dǎo)出操作1.查看oracle目錄select * from dba_directories;2.exp/imp導(dǎo)入導(dǎo)出整個(gè)用戶導(dǎo)出exp itrm/itrm owner=itrm file=itrm.dmp log=exp_itrm_20160120.log;導(dǎo)入imp itrm/itrm file=itrm.dmp log=imp_itrm20160120.log 3. exp/imp導(dǎo)入導(dǎo)出某些表導(dǎo)出exp itrm/itrm GRANTS=Y TABLES=(EMP,DEPT,MGR)導(dǎo)

10、入imp F5ITRM/F5ITRM TABLES=(EMP,DEPT,MGR)4.expdp/impdp導(dǎo)入導(dǎo)出整個(gè)用戶導(dǎo)入導(dǎo)出整個(gè)用戶時(shí)，建議先創(chuàng)建一個(gè)目錄，方便管理，否則不知道導(dǎo)出的文件放在哪里創(chuàng)建目錄:create or replace directory dumpdirectory as /orcldata/dbbackup/;給目錄賦予權(quán)限grant read,write on directory dumpdirectory to itmuserdc;導(dǎo)出命令expdp itmuserdc/itmuserdc direcoty=dumpdirectory dumpfile=kis_

11、snmp.dmp logfile=kis_snmp.log導(dǎo)入命令impdp itmuserdc/itmuserdc directory=dumpdirectory dumpfile=kis_snmp.dmp logfile=kis_snmp.log full=y備注：如果兩個(gè)庫(kù)的用戶不一致：impdp itmuser/itmuser directory=itmuserdc_dmp dumpfile=N_snmp_session.dmp logfile=imp_kis_snmp_20140811.log remap_schema=itmuserdc:itmuser6. expdp/expdp

12、導(dǎo)入導(dǎo)出某些表DATA_PUMP_DIRexpdp itrm/itrm DIRECTORY=DATA_PUMP_DIRDUMPFILE=itrm_20160503.dmp LOGFILE=itrm_20160503.log TABLES=(ADMIN_CI_CAT)impdp itmuser/itmuser directory=itmuserdc_dmp dumpfile=N_snmp_session.dmp logfile=imp_kis_snmp_20140811.log remap_schema=itmuserdc:itmuser六、問(wèn)題排查操作 1.查看會(huì)話select sid,ser

13、ial#,event,sql_id,state,machine,program from v$session;2.殺死會(huì)話alter system kill session 709,64584;備注：順序（SID SERIAL#）7. 查看進(jìn)程中的sqlset long 1000;select sql_fulltext from v$sqlarea where sql_id=4d84vdqk4jaxq;4.查看對(duì)象是否有鎖select owner,object_id from dba_objects where object_name=RS_INDEX;select * from v$locked_object where object_id=76731;5.殺不死的會(huì)話通過(guò)殺死系統(tǒng)進(jìn)程解決select sid,serial#,paddr? from v$session where username is not null and wait_class Idle;select spid from v$process where addr =07000005022501C0;6.查看表約束和被參照的表查詢表的約束名SELECT * FROM user_constraints e where e.TABLE_NAME=CC_SERIES查詢所有