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
pp/gelc123@8.4bips 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: gaopeng@lifeisgreat.com.cn password:test200217
25 vnc 处理
比如先开了一个session, vncserver :1,把最后一行 twm& 改成 gnome-session& or kde&。之后先用vncserver -kill :1 ,执行完后再vncserver :1,就可以见到桌面了.
26 AMLS change password
#password:Test12345
user: gaopeng@lifeisgreat.com.cn 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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7728585/viewspace-756606/