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 a.XIDUSN,a.XIDSLOT,a.SESSION_ID,
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

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 table
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

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;

3、
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.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';

 7、修改时间
  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

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

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

 

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

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

16 DB_LINK
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');

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 = '新契约部';

  
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

#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

明文：Test12345
  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');

   
SELECT * from pp where pp.owner='SYSTEM';

34 GRANT READ,WRITE ON DIRECTORY EXPDBDATA to backup;

35 使用管道EXP
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

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';

rman target sys/acicacrun09  <run {
delete noprompt archivelog until time '(SYSDATE - 30)';
}
exit
EOF

37 绑定变量
   var n number
   exec :n := 123
select * from test where id=:n;

38 job broken
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';

41 监控索引使用和停止监控
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

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';

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

50、 flashback query

SELECT * FROM employees AS OF TIMESTAMP
     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 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
然后如下：

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;

File created.

SQL> shutdown immediate

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA2/rac-cluster/asmparamete
                                                 rfile/registry.253.804902307

53、如何找到窥视的值
select * from table(dbms_xplan.display_cursor('5yrn1s0pzh9k5','0','ADVANCED'));