我的學習備份
1、檢視TX 鎖 及事物ID
select username, v$lock.sid, v$lock.TYPE,
trunc(id1/power(2,16)) rbs,
bitand(id1,to_number('ffff','xxxx'))+0 slot,
id2 seq,
lmode,
request
from v$lock, v$session
where v$lock.type in ('TX','TM')
and v$lock.sid = v$session.sid;
select username, v$lock.sid, v$lock.TYPE,
trunc(id1/power(2,16)) rbs,
bitand(id1,to_number('ffff','xxxx'))+0 slot,
id2 seq,
lmode,
request
from v$lock, v$session
where v$lock.type in ('TX','TM')
and v$lock.sid = v$session.sid;
SELECT a.XIDUSN,a.XIDSLOT,a.SESSION_ID,
a.ORACLE_USERNAME,a.OS_USER_NAME,a.LOCKED_MODE
FROM v$locked_object a;
a.ORACLE_USERNAME,a.OS_USER_NAME,a.LOCKED_MODE
FROM v$locked_object a;
select * from v$transaction;
lmode
0 --- none
1 --- null
2 --- row-s SS
3 --- row-X SX
4 ---- share S
5 --- S/Row-X (SSX)
6 --- exclusive
lmode
0 --- none
1 --- null
2 --- row-s SS
3 --- row-X SX
4 ---- share S
5 --- S/Row-X (SSX)
6 --- exclusive
2、常用的匯入匯出
exp schema
exp staffnet/prostaff statistics=none file=staffpro.dmp log=log.log
imp gimsuser/oracle fromuser=gimsgimspuat touser=gimsuser file=gims.dmp log=gims.log
exp schema
exp staffnet/prostaff statistics=none file=staffpro.dmp log=log.log
imp gimsuser/oracle fromuser=gimsgimspuat touser=gimsuser file=gims.dmp log=gims.log
exp table
imp GELC_PROD/gelc123 tables=t_bank_text file=file2.dmp log=impt.log
imp GELC_PROD/gelc123 tables=t_bank_text file=file2.dmp log=impt.log
exp full=y file=/home/oracle/20100629bipsbak/am/bips.bak log=/home/oracle/20100629bipsbak/am/bips.log
expdp schema
expdp gbimsprod/ias10g dumpfile=TEMP_DIR:gbims20091224.dmp logfile=TEMP_DIR:gbims20091224.log
expdp admin/gaopeng SCHEMAS=cordysdev,cordystest dumpfile=bak:cord.dmp logfile=bak:log.log
expdp gbimsprod/ias10g dumpfile=TEMP_DIR:gbims20091224.dmp logfile=TEMP_DIR:gbims20091224.log
expdp admin/gaopeng SCHEMAS=cordysdev,cordystest dumpfile=bak:cord.dmp logfile=bak:log.log
impdp pomspatchdev/gelc123 remap_schema=gelctestcirc:pomspatchdev dumpfile=DATA_PUMP_DIR:215sit.dmp logfile=DATA_PUMP_DIR:215sitimp.log
expdp full
expdp backup/Backupexp full=y dumpfile=DATA_PUMP_DIR:back.dmp logfile=DATA_PUMP_DIR:impdp.log
expdp system/gelc123 PARALLEL=2 JOB_NAME=full_bak_job full=y dumpfile=exptest:back.dmp logfile=exptest:back.log
impdp pomscp/gelc123 EXCLUDE=STATISTICS schemas=gelc_prod remap_schema=gelc_prod:pomscp dumpfile=TEMP_DIR:back.dmp logfile=TEMP_DIR:backlog.log;
impdp ppuser1/woaini schemas=pp remap_schema=pp:ppuser1 dumpfile=DATA_PUMP_DIR:back.dmp logfile=DATA_PUMP_DIR:backlog.log;
impdp GIMSNEWUAT/ebao123 remap_schema=gbimsprod:GIMSNEWUAT dumpfile=TEMP_DIR:gbims_20100509.dmp logfile=TEMP_DIR:gbims_20100509.log exclude=(TABLE_STATISTICS,INDEX_STATISTICS);
1、並行匯出EXPDP
expdp system/*** PARALLEL=6 full=y dumpfile=test:expdp_20130104.dmp logfile=test:imp.log;
耗時1小時40分鐘,匯出檔案大小180G
2、FTP傳輸
50M/S 耗時1小時10分左右
3、並行匯入IMPDP
impdp system/gelc123 PARALLEL=6 EXCLUDE=STATISTICS JOB_NAME=full_imp cluster=no full=y dumpfile=expdir:back_%U.dmp logfile=expdir:back_imp.log;
expdp backup/Backupexp full=y dumpfile=DATA_PUMP_DIR:back.dmp logfile=DATA_PUMP_DIR:impdp.log
expdp system/gelc123 PARALLEL=2 JOB_NAME=full_bak_job full=y dumpfile=exptest:back.dmp logfile=exptest:back.log
impdp pomscp/gelc123 EXCLUDE=STATISTICS schemas=gelc_prod remap_schema=gelc_prod:pomscp dumpfile=TEMP_DIR:back.dmp logfile=TEMP_DIR:backlog.log;
impdp ppuser1/woaini schemas=pp remap_schema=pp:ppuser1 dumpfile=DATA_PUMP_DIR:back.dmp logfile=DATA_PUMP_DIR:backlog.log;
impdp GIMSNEWUAT/ebao123 remap_schema=gbimsprod:GIMSNEWUAT dumpfile=TEMP_DIR:gbims_20100509.dmp logfile=TEMP_DIR:gbims_20100509.log exclude=(TABLE_STATISTICS,INDEX_STATISTICS);
1、並行匯出EXPDP
expdp system/*** PARALLEL=6 full=y dumpfile=test:expdp_20130104.dmp logfile=test:imp.log;
耗時1小時40分鐘,匯出檔案大小180G
2、FTP傳輸
50M/S 耗時1小時10分左右
3、並行匯入IMPDP
impdp system/gelc123 PARALLEL=6 EXCLUDE=STATISTICS JOB_NAME=full_imp cluster=no full=y dumpfile=expdir:back_%U.dmp logfile=expdir:back_imp.log;
3、
select * from t_product_life where internal_id='LE111';
4、
資料提取
user:timesheet
passwd:ias10g
216開發環境
select * from t_product_life where internal_id='LE111';
4、
資料提取
user:timesheet
passwd:ias10g
216開發環境
--提取指定時間段的所有記錄
select t.staff_code as "Staff Code",
t.guy_name as "Name",
t.department as "Department",
t.section_name as "Team",
t.task_date as "Date",
t.task_project as "Project",
t.task_system as "System",
t.task_phase as "Project Phase",
t.activity as "Activity",
t.refer_no as "Reference No.",
t.service_dept as "User Dept.",
t.fund as "Fund",
t.task_cost as "No.of Hrs",
t.task_desc as "Remark"
from t_timelog_task t
where t.task_date >= to_date('20081215','yyyyMMdd') and
t.task_date <= to_date('20081221','yyyyMMdd')
order by 2,5;
select t.staff_code as "Staff Code",
t.guy_name as "Name",
t.department as "Department",
t.section_name as "Team",
t.task_date as "Date",
t.task_project as "Project",
t.task_system as "System",
t.task_phase as "Project Phase",
t.activity as "Activity",
t.refer_no as "Reference No.",
t.service_dept as "User Dept.",
t.fund as "Fund",
t.task_cost as "No.of Hrs",
t.task_desc as "Remark"
from t_timelog_task t
where t.task_date >= to_date('20081215','yyyyMMdd') and
t.task_date <= to_date('20081221','yyyyMMdd')
order by 2,5;
--把每週的記錄按員工統計工作時間。
select t.guy_name as "Name",
sum(t.task_cost) as "No.of Hrs"
from t_timelog_task t
where t.task_date >= to_date('20081215','yyyyMMdd') and
t.task_date <= to_date('20081221','yyyyMMdd')
group by t.guy_name
5、LINUX 常用命令 du -hs 檢視目錄大小
tar zcvf /tmp tmp.tar.zip 壓縮資料夾
cpio -idmv < xxx.cpio
select t.guy_name as "Name",
sum(t.task_cost) as "No.of Hrs"
from t_timelog_task t
where t.task_date >= to_date('20081215','yyyyMMdd') and
t.task_date <= to_date('20081221','yyyyMMdd')
group by t.guy_name
5、LINUX 常用命令 du -hs 檢視目錄大小
tar zcvf /tmp tmp.tar.zip 壓縮資料夾
cpio -idmv < xxx.cpio
6、
update t_user set LATEST_LOGIN_OLD=sysdate
,LATEST_LOGIN_TIME=sysdate
,LATEST_LOGOUT_TIME=sysdate
,LATEST_ACCESS_TIME=sysdate
,PASSWORD_CHANGE=sysdate
,user_disable='N'
,disable_cause=''
,INVALID_LOGIN=0
,NEED_CHANGE_PASS='N'
update t_user set user_disable='N'
,disable_cause=''
,INVALID_LOGIN=0
,NEED_CHANGE_PASS='N'
where user_name='CAD016';
update t_user set LATEST_LOGIN_OLD=sysdate
,LATEST_LOGIN_TIME=sysdate
,LATEST_LOGOUT_TIME=sysdate
,LATEST_ACCESS_TIME=sysdate
,PASSWORD_CHANGE=sysdate
,user_disable='N'
,disable_cause=''
,INVALID_LOGIN=0
,NEED_CHANGE_PASS='N'
where user_name='linyan' or user_name='hulin'
or user_name='limlili' or user_name='wangdiankun'
or user_name='yangyiqin' or user_name='root'
or user_name='xiaotang' or user_name='lianglingxi'
or user_name='guoxingyu' or user_name='Act005'
or user_name='hanxueping' or USER_NAME='CAD016'
or user_name='youzhuquan' or user_name='BA005'
or user_name='RN-001' or user_name='RN-002'
or user_name='RN-071' or user_name='RN-072'
or user_name='RN-00101' or user_name='RN-00102'
or user_name='RN-00121' or user_name='RN-00122';
update t_user set LATEST_LOGIN_OLD=sysdate
,LATEST_LOGIN_TIME=sysdate
,LATEST_LOGOUT_TIME=sysdate
,LATEST_ACCESS_TIME=sysdate
,PASSWORD_CHANGE=sysdate
,user_disable='N'
,disable_cause=''
,INVALID_LOGIN=0
,NEED_CHANGE_PASS='N'
update t_user set user_disable='N'
,disable_cause=''
,INVALID_LOGIN=0
,NEED_CHANGE_PASS='N'
where user_name='CAD016';
update t_user set LATEST_LOGIN_OLD=sysdate
,LATEST_LOGIN_TIME=sysdate
,LATEST_LOGOUT_TIME=sysdate
,LATEST_ACCESS_TIME=sysdate
,PASSWORD_CHANGE=sysdate
,user_disable='N'
,disable_cause=''
,INVALID_LOGIN=0
,NEED_CHANGE_PASS='N'
where user_name='linyan' or user_name='hulin'
or user_name='limlili' or user_name='wangdiankun'
or user_name='yangyiqin' or user_name='root'
or user_name='xiaotang' or user_name='lianglingxi'
or user_name='guoxingyu' or user_name='Act005'
or user_name='hanxueping' or USER_NAME='CAD016'
or user_name='youzhuquan' or user_name='BA005'
or user_name='RN-001' or user_name='RN-002'
or user_name='RN-071' or user_name='RN-072'
or user_name='RN-00101' or user_name='RN-00102'
or user_name='RN-00121' or user_name='RN-00122';
7、修改時間
date -s 07/01/2008
date -s 20:06:00
8、檢視當前SCN
SQL> select dbms_flashback.get_system_change_number from dual;
date -s 07/01/2008
date -s 20:06:00
8、檢視當前SCN
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
1069934
------------------------
1069934
SQL> select current_scn,to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS') from v$database;
CURRENT_SCN TO_CHAR(SYSTIMESTAMP,'YYYY-MM-
----------- ------------------------------
1069936 2009-02-08 16:13:30
9、oin 目錄指定 /etc/oraInst.loc
----------- ------------------------------
1069936 2009-02-08 16:13:30
9、oin 目錄指定 /etc/oraInst.loc
10、/oradata/ias10g/product/j2ee/home/application-deployments/life/home_default_island_1
11、debug connect session
12、為FTP增加磁碟配額,從而避免惡意使用者用垃圾資料塞滿你的硬碟
我首先要說的是這個功能是系統自帶的,而不是vsftp 的功能之一,千萬別搞混了。好了,我們先假設我們的系統使用者ftpd的主目錄是/home/ftpd,它是建立在/home分割槽中,那麼如果我們要對ftpd使用者進行磁碟限額,那我們需要修改/etc/fstab中根分割槽的記錄,將/home分割槽的第4個欄位改成defaults,usrquota,如下:
LABEL=/home /home ext3 defaults,usrquota 1 2
# reboot //重新啟動系統使設定生效
也可以用
# mount -o remount /dev/sda6 ///dev/sda6的掛接點就是/home,這樣可以不用啟動系統。
這裡我還要說明一下,如果我們對一個組進行磁配額,那我們需要增加引數grpquota,例如
LABEL=/home /home ext3 defaults,grpquota 1 2
也可以
LABEL=/home /home ext3 defaults,usrquota,grpquota 1 2
你想怎麼限制都可以,自己組合引數吧。
# quotacheck -avu
說明:a-自動開啟掛載檔案系統的配額,v-顯示資訊,u-啟用使用者配額or g-啟用組配額
# edquota ftpd //為使用者ftpd設定磁碟配額
OR
# edquota -g grp //為組grp設定磁碟配額
系統會自動開啟配額檔案,如下:
Disk quotas for user ftpd (uid 502):
Filesystem blocks soft hard inodes soft hard
/dev/sda6 424 0 0 13 0 0
第一列是啟用了配額的檔案系統的名稱。第二列顯示了使用者當前使用的塊數,單位為KB。隨後的兩列用來設定使用者在該檔案系統上的軟硬塊限度。inodes 列顯示了使用者當前使用的i節點數量。最後兩列用來設定使用者在該檔案系統上的軟硬i節點限度.硬限是使用者或組群可以使用的磁碟空間的絕對最大值。達到了該限度後,磁碟空間就不能再被使用者或組群使用了。軟限定義可被使用的最大磁碟空間量。和硬限不同的是,軟限可以在一段時期內被超過。這段時期被稱為過渡期(grace period),預設七天的超越。過渡期可以用秒鐘、分鐘、小時、天數、週數、或月數表示。如果以上值中的任何一個被設定為 0,那個限度就不會被設定。我設定了硬塊限度為1KB,是為了測試方便。
# quotaon -avu //開啟磁碟配額監控程式,u是使用者g是組,這裡我沒設定g引數
要校驗使用者的配額是否被設定,我們可以使用以下命令:
# quota ftpd
Disk quotas for user ftpd (uid 502):
Filesystem blocks quota limit grace files quota limit grace
/dev/sda6 424* 0 1 13 0 0
# edquota –t(-g)來設定過渡期(grace period) //當然只針對軟限制而言
和另一個 edquota 命令相似,這個命令也會在文字編輯器中開啟當前的檔案系統配額:
Grace period before enforcing soft limits for users:
Time units may be: days, hours, minutes, or seconds
Filesystem Block grace period Inode grace period
/dev/sda6 7days 7days
按你的需要修改後存檔退出
用以下命令顯示磁碟配額使用狀態
# repquota -a 或 repquota /dev/sda6(使用者配額)
# repquota -g -a 或 repquota -a /dev/sda6 (組的配額)
如果一切按照你的意思實施了,那麼我們就進行測試了!如下圖4
我們傳了一個>1k的檔案,沒有成功,這樣我們就成功的為使用者ftpd增添了磁碟配額,要是哪一天你不想加磁碟配額了,怎麼辦?參看如下命令:
取消某個檔案系統的配額限制
#quotaoff -vug /dev/sda6 //刪除home分割槽的磁碟限額
#刪除/etc/fstab中設定配額的部分
修改軟配額的最大超越時間
注意:
/,/boot/,/proc,/mnt/cdrom等不要使用配額,沒用。而且磁碟配額不適合FAT和FAT32系統。
以後當新設定了某個使用者的配額,可以使用如下命令,馬上生效。
# quotacheck -auvgm --是不嘗試重新掛載檔案系統
13 rpm -ivh /**.rpm
14
FTP 192.168.31.126
poms Poms782QA
amls Amlsccu8912A
staffnet Staff98nets
website Websi897aQc
ims IIms89jsaqcs
gims Gims89712cd
bips Bips7812cdac
bacs Bacs98812cd
amls Amlsccu8912A
staffnet Staff98nets
website Websi897aQc
ims IIms89jsaqcs
gims Gims89712cd
bips Bips7812cdac
bacs Bacs98812cd
oc4jadmin
15 檢視所有包
[root@b000-lnxdb07 local]# rpm -q binutils compat-db control-center gcc gcc-c++ glibc glibc-common gnome-libs libstdc++ libstdc++-devel make pdksh sysstat xscreensaver libaio openmotif21
[root@b000-lnxdb07 local]# rpm -q binutils compat-db control-center gcc gcc-c++ glibc glibc-common gnome-libs libstdc++ libstdc++-devel make pdksh sysstat xscreensaver libaio openmotif21
binutils-2.15.92.0.2-21
compat-db-4.1.25-9
control-center-2.8.0-12.rhel4.5
gcc-3.4.6-3
gcc-c++-3.4.6-3
glibc-2.3.4-2.25
glibc-common-2.3.4-2.25
gnome-libs-1.4.1.2.90-44.1
libstdc++-3.4.6-3
libstdc++-devel-3.4.6-3
make-3.80-6.EL4
pdksh-5.2.14-30.3
package sysstat is not installed
xscreensaver-4.18-5.rhel4.11
libaio-0.3.105-2
openmotif21-2.1.30-11.RHEL4.6
compat-db-4.1.25-9
control-center-2.8.0-12.rhel4.5
gcc-3.4.6-3
gcc-c++-3.4.6-3
glibc-2.3.4-2.25
glibc-common-2.3.4-2.25
gnome-libs-1.4.1.2.90-44.1
libstdc++-3.4.6-3
libstdc++-devel-3.4.6-3
make-3.80-6.EL4
pdksh-5.2.14-30.3
package sysstat is not installed
xscreensaver-4.18-5.rhel4.11
libaio-0.3.105-2
openmotif21-2.1.30-11.RHEL4.6
16 DB_LINK
create public database link 123
connect to userName identified by password
using '(
)';
create public database link 123
connect to userName identified by password
using '(
)';
17 createinstance -instanceName OC4J_instanceName
18 ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN'
19 檢視未建立同義詞的物件
SELECT OBJECT_NAME,OBJECT_TYPE FROM DBA_OBJECTS WHERE OBJECT_TYPE IN ('SEQUENCE','PROCEDURE','PACKAGE','PACKAGE BODY','TABLE','FUNCTION')AND WNER='GELCTEST216' AND OBJECT_NAME NOT IN
(select SYNONYM_NAME from dba_synonyms where table_owner='GELCTEST216' and wner='GELCTEST216APP');
SELECT OBJECT_NAME,OBJECT_TYPE FROM DBA_OBJECTS WHERE OBJECT_TYPE IN ('SEQUENCE','PROCEDURE','PACKAGE','PACKAGE BODY','TABLE','FUNCTION')AND WNER='GELCTEST216' AND OBJECT_NAME NOT IN
(select SYNONYM_NAME from dba_synonyms where table_owner='GELCTEST216' and wner='GELCTEST216APP');
20
imp GELC_PROD/gelc123 tables=t_bank_text file=file2.dmp log=impt.log
21 DSCC 釋出流程 1、複製正確部署指令碼 2、修改應用程式的名稱為DCSS
22 反洗錢使用者 查詢 相關
select a.user_name,c.dept_name,b.emp_name
from t_mls_user a, t_mls_employee b,t_mls_department c
where a.emp_id = b.emp_id
and c.dept_id=b.dept_id
and b.is_principal = 'Y';
select a.USER_ID, a.USER_NAME, b.IS_PRINCIPAL, b.emp_name, c.dept_name
from t_mls_user a, t_mls_employee b, T_MLS_DEPARTMENT C
where a.emp_id = b.emp_id
AND B.DEPT_ID = C.DEPT_ID
and c.dept_name = '新契約部';
22 反洗錢使用者 查詢 相關
select a.user_name,c.dept_name,b.emp_name
from t_mls_user a, t_mls_employee b,t_mls_department c
where a.emp_id = b.emp_id
and c.dept_id=b.dept_id
and b.is_principal = 'Y';
select a.USER_ID, a.USER_NAME, b.IS_PRINCIPAL, b.emp_name, c.dept_name
from t_mls_user a, t_mls_employee b, T_MLS_DEPARTMENT C
where a.emp_id = b.emp_id
AND B.DEPT_ID = C.DEPT_ID
and c.dept_name = '新契約部';
23 格式化PLAN_TABLE
select * from table(dbms_xplan.display)
select * from table(dbms_xplan.display(null,null,' advanced'));
24 metalink.oracle.com
user: password:test200217
25 vnc 處理
比如先開了一個session, vncserver :1,把最後一行 twm& 改成 gnome-session& or kde&。之後先用vncserver -kill :1 ,執行完後再vncserver :1,就可以見到桌面了.
26 AMLS change password
#password:Test12345
user: password:test200217
25 vnc 處理
比如先開了一個session, vncserver :1,把最後一行 twm& 改成 gnome-session& or kde&。之後先用vncserver -kill :1 ,執行完後再vncserver :1,就可以見到桌面了.
26 AMLS change password
#password:Test12345
#update t_user usr set usr.password='o/M67gx7cHph1V07yuSfjBsYSG8T1rcqduVlaJX8WuBVHGbL1F5uS0DlxKG3Y+Qy',usr.failure_times=0,usr.USER_STATUS=3 where usr.user_name='?';
210.72.145.44
27
建立快照
execute sys.dbms_workload_repository.create_snapshot();
select * from DBA_HIST_SNAPSHOT order by snap_id desc; 可以檢視
28 檢視許可權日誌
select t.type_name, a.update_time,g.group_name, d.*
from t_admin_act_detail D, t_Admin_Act_Type t, t_admin_act a,t_group g
WHERE d.act_type = t.type_id
and d.group_id=g.group_id
and D.USER_ID = 10013
and d.act_id = a.act_id
order by act_detail_id;
29 app log
/usr/local/oas10g6/product/j2ee/home/application-deployments/life/home_default_island_1
29 cat /proc/cpuinfo|grep physical
30
select /*+ gather_plan_statistics */ count(*) from pp;
select * from table(dbms_xplan.display_cursor(null,null,'iostats last')); (I/O)
SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'memstats last'));(PGA)
SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'runstats_last'));
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
select * from table(dbms_xplan.display_cursor(null,null,'last'));(執行計劃)
31
反洗錢預設密碼
R7SSBs934Za/F+O72EuxI/MNKaPzDRWsku/naZEU/36XCYSJ+fh8I/Qzz9WB2pFN
27
建立快照
execute sys.dbms_workload_repository.create_snapshot();
select * from DBA_HIST_SNAPSHOT order by snap_id desc; 可以檢視
28 檢視許可權日誌
select t.type_name, a.update_time,g.group_name, d.*
from t_admin_act_detail D, t_Admin_Act_Type t, t_admin_act a,t_group g
WHERE d.act_type = t.type_id
and d.group_id=g.group_id
and D.USER_ID = 10013
and d.act_id = a.act_id
order by act_detail_id;
29 app log
/usr/local/oas10g6/product/j2ee/home/application-deployments/life/home_default_island_1
29 cat /proc/cpuinfo|grep physical
30
select /*+ gather_plan_statistics */ count(*) from pp;
select * from table(dbms_xplan.display_cursor(null,null,'iostats last')); (I/O)
SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'memstats last'));(PGA)
SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'runstats_last'));
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
select * from table(dbms_xplan.display_cursor(null,null,'last'));(執行計劃)
31
反洗錢預設密碼
R7SSBs934Za/F+O72EuxI/MNKaPzDRWsku/naZEU/36XCYSJ+fh8I/Qzz9WB2pFN
明文:Test12345
32 10046 event
32 10046 event
alter session set events '10046 trace name context forever, level 8';
33 update amls 使用者密碼
update t_mls_user set USER_STATUS=3,FAILURE_TIMES=0,
PASSWORD='R7SSBs934Za/F+O72EuxI/MNKaPzDRWsku/naZEU/36XCYSJ+fh8I/Qzz9WB2pFN',
LAST_LOGIN_TIME=sysdate,FORCE_CHANGE_PASSWORD='N',
LAST_PASSWORD_CHANGE_DATE=sysdate where USER_NAME in ('xiewei','zhangjian');
33 update amls 使用者密碼
update t_mls_user set USER_STATUS=3,FAILURE_TIMES=0,
PASSWORD='R7SSBs934Za/F+O72EuxI/MNKaPzDRWsku/naZEU/36XCYSJ+fh8I/Qzz9WB2pFN',
LAST_LOGIN_TIME=sysdate,FORCE_CHANGE_PASSWORD='N',
LAST_PASSWORD_CHANGE_DATE=sysdate where USER_NAME in ('xiewei','zhangjian');
SELECT * from pp where pp.owner='SYSTEM';
34 GRANT READ,WRITE ON DIRECTORY EXPDBDATA to backup;
35 使用管道EXP
mknod /home/oracle/test_pipe p
mknod /home/oracle/test_pipe p
cd /home/oracle
exp system/gelc123 full=y file=/home/oracle/test_pipe & gzip < /home/oracle/test_pipe > exp.dmp.gz
exp system/gelc123 full=y file=/home/oracle/test_pipe & gzip < /home/oracle/test_pipe > exp.dmp.gz
ORACLE_SID=zbjdb;export ORACLE_SID
ORACLE_BASE=/oracle;export ORACLE_BASE
ORACLE_TERM=vt100;export ORACLE_TERM
ORACLE_HOME=/oracle/product/10.2.0;export ORACLE_HOME
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK;export NLS_LANG
export PATH=$HOME/bin:$ORACLE_HOME/bin:$PATH
DATE_TAG=`date +%Y%m%d`
find /backup -ctime +7 -exec rm {} \;
exp buffer=10240000 file=/backup/zbjdb parfile=/oracle/backup/exp.txt log=/backup/zbjdb_${DATE_TAG}.log & gzip < /home/oracle/zbjdb > zbjdb_${DATE_TAG}.dmp.gz
#scp /backup/zbjdb* 172.16.1.40:/archive/backup/zbjdb
rcp /backup/zbjdb* 172.16.1.18:/data/nc73_backup/
36 刪除一天前的歸檔或者N天前的歸檔
delete archivelog all COMPLETED before 'SYSDATE-1';
delete archivelog all COMPLETED before 'SYSDATE-1';
rman target sys/acicacrun09 <run {
delete noprompt archivelog until time '(SYSDATE - 30)';
}
exit
EOF
delete noprompt archivelog until time '(SYSDATE - 30)';
}
exit
EOF
37 繫結變數
var n number
exec :n := 123
select * from test where id=:n;
var n number
exec :n := 123
select * from test where id=:n;
38 job broken
exec dbms_job.broken(22,true);
exec dbms_job.broken(22,true);
commit
39 AIX vnc 登陸的啟動DBCA
root下 xhost+
su - oracle
export DISPLAY=127.0.0.1:1.0
xhost+
40 10053 trace
alter session set tracefile_identifier=10053;
alter session set events='10053 trace name context forever,level 2';
select /*+ index(a IDX_TITLE_REASONID) */ level,a.*,substr(reason_id,1,length(reason_id)-4) fa_reason_id ,CONNECT_BY_ISLEAF "IsLeaf"
from nxtdw.tb_dic_cr_title a
where CONNECT_BY_ISLEAF=1
start with length(reason_id)=4
connect BY PRIOR reason_id=substr(reason_id,1,length(reason_id)-4)
order by reason_id;
alter session set events='10053 trace name context off';
alter session set events='10053 trace name context forever,level 2';
select /*+ index(a IDX_TITLE_REASONID) */ level,a.*,substr(reason_id,1,length(reason_id)-4) fa_reason_id ,CONNECT_BY_ISLEAF "IsLeaf"
from nxtdw.tb_dic_cr_title a
where CONNECT_BY_ISLEAF=1
start with length(reason_id)=4
connect BY PRIOR reason_id=substr(reason_id,1,length(reason_id)-4)
order by reason_id;
alter session set events='10053 trace name context off';
41 監控索引使用和停止監控
alter index gaopengtest4 monitoring usage;
alter index gaopengtest4 nomonitoring usage;
alter index gaopengtest4 monitoring usage;
alter index gaopengtest4 nomonitoring usage;
42 修改隱含引數強制開啟資料庫
1、alter system set "_allow_resetlogs_corruption"=false scope=spfile;
2、如果能夠開啟進行匯出
43 IBM X3650 管理埠
MGMT-----192.168.70.125
USERID : PASSW0RD
網口上寫的MGMT
44、
修改ORACLE 預設隔離及級別alter session set isolation_level=serializable;
45、
取指定行和上一行。
awk '/network/{print a"\n"$0}{a=$0}' FILE
46 下一行
awk '/關鍵字/{getline v;print $0"\n"v}'
47 軟連線
ln -s 源頭 連線
ln -s test.conf test1.conf
48 pin S wait on X 檢視HOLD SESSION
修改ORACLE 預設隔離及級別alter session set isolation_level=serializable;
45、
取指定行和上一行。
awk '/network/{print a"\n"$0}{a=$0}' FILE
46 下一行
awk '/關鍵字/{getline v;print $0"\n"v}'
47 軟連線
ln -s 源頭 連線
ln -s test.conf test1.conf
48 pin S wait on X 檢視HOLD SESSION
How to Determine the Blocking Session for Event: 'cursor: pin S wait on X' [ID 786507.1]
SQL> select p2raw,to_number(substr(to_char(rawtohex(p2raw)),1,8),'XXXXXXXX') sid
from v$session
where event = 'cursor: pin S wait on X';
from v$session
where event = 'cursor: pin S wait on X';
P2RAW SID
---------------- ---
0000001F00000000 31
49 使用EXPDP IMPDP可以進入互動模式
nohup expdp system/gelc123 PARALLEL=2 JOB_NAME=full_bak_job full=y dumpfile=exptest:back.dmp logfile=exptest:back.log &
可以透過select * from dba_datapump_jobs; 找到EXPDP OR IMPDP NAME,同時可以看到其狀態STATE
透過expdp system/gelc123 ATTACH=full_bak_job連線到job
help 可以看到幫助
continue_client進入日誌模式
exit_client退出互動模式並且繼續執行JOB
KILL_JOB殺死當前連線JOB
PARALLEL 可以修改並行
start_job啟動STOP的job
stop_job停止當前連線的JOB
---------------- ---
0000001F00000000 31
49 使用EXPDP IMPDP可以進入互動模式
nohup expdp system/gelc123 PARALLEL=2 JOB_NAME=full_bak_job full=y dumpfile=exptest:back.dmp logfile=exptest:back.log &
可以透過select * from dba_datapump_jobs; 找到EXPDP OR IMPDP NAME,同時可以看到其狀態STATE
透過expdp system/gelc123 ATTACH=full_bak_job連線到job
help 可以看到幫助
continue_client進入日誌模式
exit_client退出互動模式並且繼續執行JOB
KILL_JOB殺死當前連線JOB
PARALLEL 可以修改並行
start_job啟動STOP的job
stop_job停止當前連線的JOB
50、 flashback query
SELECT * FROM employees AS OF TIMESTAMP
TO_TIMESTAMP('2004-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS');
TO_TIMESTAMP('2004-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS');
WHERE last_name = 'Chung'
SELECT versions_startscn, versions_starttime,
versions_endscn, versions_endtime,
versions_xid, versions_operation,
name, salary
FROM employees
VERSIONS BETWEEN TIMESTAMP
TO_TIMESTAMP('2003-07-18 14:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND TO_TIMESTAMP('2003-07-18 17:00:00', 'YYYY-MM-DD HH24:MI:SS')
WHERE name = 'JOE';
SELECT versions_startscn, versions_starttime,
versions_endscn, versions_endtime,
versions_xid, versions_operation,
name, salary
FROM employees
VERSIONS BETWEEN TIMESTAMP
TO_TIMESTAMP('2003-07-18 14:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND TO_TIMESTAMP('2003-07-18 17:00:00', 'YYYY-MM-DD HH24:MI:SS')
WHERE name = 'JOE';
SELECT xid, logon_user FROM flashback_transaction_query
WHERE xid IN (SELECT versions_xid FROM employees VERSIONS BETWEEN TIMESTAMP
TO_TIMESTAMP('2003-07-18 14:00:00', 'YYYY-MM-DD HH24:MI:SS') AND
TO_TIMESTAMP('2003-07-18 17:00:00', 'YYYY-MM-DD HH24:MI:SS'));
51、何時增加session_cached_cursors
select max(VALUE) from v$sesstat where STATISTIC#=280; 當值接近session_cached_cursors的時候應該增加
也就是session cursor cache count 和session_cached_cursors接近的時候。
52、修改ORACLE11GR2.3 ASM SPFILE的2種方法
1、spcopy -u
然後重啟 crs
2、
先建立pfile然後關閉crs
然後啟動crsctl start crs -excl -nocrs
然後shutdown asm
然後如下:
WHERE xid IN (SELECT versions_xid FROM employees VERSIONS BETWEEN TIMESTAMP
TO_TIMESTAMP('2003-07-18 14:00:00', 'YYYY-MM-DD HH24:MI:SS') AND
TO_TIMESTAMP('2003-07-18 17:00:00', 'YYYY-MM-DD HH24:MI:SS'));
51、何時增加session_cached_cursors
select max(VALUE) from v$sesstat where STATISTIC#=280; 當值接近session_cached_cursors的時候應該增加
也就是session cursor cache count 和session_cached_cursors接近的時候。
52、修改ORACLE11GR2.3 ASM SPFILE的2種方法
1、spcopy -u
然後重啟 crs
2、
先建立pfile然後關閉crs
然後啟動crsctl start crs -excl -nocrs
然後shutdown asm
然後如下:
SQL> startup pfile='/oracle/app/grid/product/11.2.0/dbs/init+ASM1.ora';
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
SQL> create spfile='+DATA2' from pfile;
------------------------------------ ----------- ------------------------------
spfile string
SQL> create spfile='+DATA2' from pfile;
File created.
SQL> shutdown immediate
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA2/rac-cluster/asmparamete
rfile/registry.253.804902307
------------------------------------ ----------- ------------------------------
spfile string +DATA2/rac-cluster/asmparamete
rfile/registry.253.804902307
53、如何找到窺視的值
select * from table(dbms_xplan.display_cursor('5yrn1s0pzh9k5','0','ADVANCED'));
select * from table(dbms_xplan.display_cursor('5yrn1s0pzh9k5','0','ADVANCED'));
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/7728585/viewspace-756606/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- rman備份學習
- Oracle學習系列—資料庫備份—熱備份Oracle資料庫
- 【原】Oracle學習系列—資料庫備份—RMAN備份Oracle資料庫
- 【原】Oracle學習系列—資料庫備份—離線備份Oracle資料庫
- INNOBACKUPEX的全備和增量備份恢復學習筆記筆記
- RMAN說,我能備份(9)--RMAN增量備份與備份保留策略
- 我常用的4個備份工具
- RMAN說,我能備份(18)--制訂備份策略
- RMAN說,我能備份(14)--實戰RMAN備份
- RMAN說,我能備份(11)--RMAN中的備份集屬性
- [學習筆記]SQL server完全備份指南筆記SQLServer
- DG學習筆記(9)_備份與RAC筆記
- RMAN說,我能備份(3)--RMAN全庫備份和表空間備份
- RMAN說,我能備份(18)--RMAN中的加密備份和CATALOG加密
- RMAN說,我能備份(13)--RMAN中的備份壓縮和加密加密
- mysql學習筆記之備份與恢復MySql筆記
- LINUX學習(二)備份與壓縮命令Linux
- rman資料備份恢復學習筆記筆記
- RMAN說,我能備份(5)--RMAN備份歸檔檔案
- 學習 第2章:備份與恢復選項
- Oracle冷備份練習Oracle
- RMAN說,我能備份(12)--RMAN中的恢復目錄和備份優化優化
- 我的學習方法
- 我的學習觀
- Backup And Recovery User's Guide-備份RMAN備份-備份保留期對備份的備份的影響GUIIDE
- 完全備份、差異備份、增量備份的區別
- Backup And Recovery User's Guide-RMAN備份概念-增量備份-累積增量備份-練習GUIIDE
- SQL學習-資料庫的備份和還原 分離和附加SQL資料庫
- Backup And Recovery User's Guide-備份RMAN備份-關於備份的備份GUIIDE
- 2 Day DBA-管理方案物件-執行備份和恢復-管理備份-練習:刪除失效的備份物件
- 我的學習方法論
- RMAN說,我能備份(16)--RMAN中的SET命令
- RMAN說,我能備份(4)--RMAN備份資料檔案和控制檔案
- mysql的冷備份與熱備份MySql
- oracle的熱備份和冷備份Oracle
- Mysql 企業級備份與恢復(學習筆記七)MySql筆記
- Oracle裡邏輯備份、物理備份、Rman備份的區別Oracle
- RMAN說,我能備份(2)--熟悉RMAN操作~