葵花宝典

1,用户解锁
alter user zxinsys account unlock

2，查询机器的连接数
select machine,count(1) from   v$session   group by machine

3，查询锁定的进程，并杀掉
  SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name,
        o.owner,o.object_name,o.object_type,s.sid,s.serial#
        FROM v$locked_object l,dba_objects o,v$session s
        WHERE l.object_id=o.object_id
        AND l.session_id=s.sid
        ORDER BY o.object_id,xidusn DESC;
  ---------     
      alter system kill session '318,152';

4，exists 用法
select count(trad.TRADEINDEX )from   v_mproduct_trade_record trad
where exists (select down.TRADEINDEX from v_mproduct_download_record down where  down.TRADEINDEX = trad.TRADEINDEX)


5，获取季度方法。
---所在季度的第一天（14位）
   select  to_char(trunc(to_date('20110501','yyyy-mm-dd'),'Q'),'yyyymmdd')||'000000' from dual;
---所在季度的最后一天(14位)
   select  to_char(last_day(add_months(trunc(to_date('20110501','yyyy-mm-dd'),'Q'),2)),'yyyymmdd')||'999999'  from dual;


6，查看运行的进程
--package package body  
--kept 是否常驻shared pool,依赖于对象是否利用pl/sql过程  
--locks 锁定对象的session数  
--pin
--loads 自初始被加载次数 
select v.* from v$db_object_cache v where   
type='PROCEDURE'/* and locks>0 and pins>0;   */ and owner = 'ZXDBM_REPORT'


7，查出并终止进程。
--终止procedure   
select * from v$access a where object like 'INSERT_REPORT_KFZYY_PROC%' 

select sid,owner,object,type from v$access  where  OWNER='ZXDBM_REPORT' and type='PROCEDURE';

--select * from v$session where sid in(135,157,138,141) 

alter system kill session '136,635' --'sid,serial#'


8，查看表空间相关语句
SELECT
   a.tablespace_name 表空间名,
    total 表空间大小,
    free 表空间剩余大小,
   (total-free) 表空间使用大小,
    ROUND((total-free)/total,4)*100 使用率
FROM (SELECT tablespace_name,SUM(bytes) free FROM DBA_FREE_SPACE GROUP BYtablespace_name ) a,
(SELECT tablespace_name,SUM(bytes) total FROM DBA_DATA_FILES
GROUP BY tablespace_name) b
WHERE a.tablespace_name=b.tablespace_name /*and a.tablespace_name = 'ZXIN_DATA' */
---
select * from DBA_FREE_SPACE  where tablespace_name='ZXIN_DATA';

---数据表空间 文件查询
select * from  DBA_DATA_FILES;
--- 临时表空间文件查询
select * from  dba_temp_files t;


9，创建表空间，增加表空间数据文件

create tablespace test 
datafile '/home/app/oracle/oradata/oracle8i/test01.dbf' size 8M 
autoextend on 
next 5M 
maxsize 10M; 

create tablespace sales 
datafile '/home/app/oracle/oradata/oracle8i/sales01.dbf' size 800M 
autoextend on 
next 50M 
maxsize unlimited 
----maxsize unlimited 是大小不受限制 

create tablespace sales 
datafile '/home/app/oracle/oradata/oracle8i/sales01.dbf' size 800M 
autoextend on 
next 50M 
maxsize 1000M 
extent management local uniform; 
unform表示区的大小相同，默认为1M 

create tablespace sales 
datafile '/home/app/oracle/oradata/oracle8i/sales01.dbf' size 800M 
autoextend on 
next 50M 
maxsize 1000M 
extent management local uniform size 500K; 
unform size 500K表示区的大小相同，为500K 

create tablespace sales 
datafile '/home/app/oracle/oradata/oracle8i/sales01.dbf' size 800M 
autoextend on 
next 50M 
maxsize 1000M 
extent management local autoallocate; 
autoallocate表示区的大小由随表的大小自动动态改变，大表使用大区小表使用小区 

create tablespace sales 
datafile '/home/app/oracle/oradata/oracle8i/sales01.dbf' size 800M 
autoextend on 
next 50M 
maxsize 1000M 
temporary; 
temporary创建字典管理临时表空间 

----创建临时表空间
create temporary tablespace sales 
tempfile '/home/app/oracle/oradata/oracle8i/sales01.dbf' size 800M 
autoextend on 
next 50M 
maxsize 1000M 
创建本地管理临时表空间，如果是临时表空间，所有语句中的datafile都换为tempfile
---------
alter tablespace ZXIN_DATA add datafile '/zxindata1/datafile/zxin_data6.dbf' size 20000m autoextend on next 2000M;


10,设置数据文件自动扩展
    ALTER DATABASE    DATAFILE '/home/oracle/oradata/zxin/zxin_file/zxin_data1.dbf'
     AUTOEXTEND ON NEXT 100M
     MAXSIZE 40000M;

11，取消空间数据文件 自动扩展属性。
  alter database datafile
'/home/app/oracle/oradata/oracle8i/sales01.dbf' autoextend off;


12，更改空间数据文件大小
  alter database datafile
'/home/oracle/oradata/zxin/zxin_file/zxin_data2.dbf' resize 4000m;


13，设置临时表空间自动扩展
ALTER DATABASE    tempfile '/home/oracle/oradata/zxin/zxin_temp/zxin_temp.dbf'
    AUTOEXTEND ON NEXT 100M    MAXSIZE 10000M;


14，创建一新用户，赋权语句
drop user zxdbm_report cascade;
create user zxdbm_report identified by zxin_smap default tablespace zxin_data temporary tablespace zxin_temp;
grant connect, resource, dba to zxdbm_report;
grant create public database link to zxdbm_report;
grant drop public database link to zxdbm_report;
grant unlimited tablespace to zxdbm_report;
grant select any table to zxdbm_report;
grant create any table to zxdbm_report;
grant create any view to zxdbm_report;
alter user zxdbm_report default role all;


15，创建dblink 两种方法
(1)方式一：配置本地服务的
  create public database link report_link connect to zxdbm_misp identified by zxin_smap   using 'report2app'
--注释 ：report2app  本地配置的数据的实例名 
   本例配置的文件如下：
report2app=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.46.178.155)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SERVER       = DEDICATED)
      (SERVICE_NAME = zxin)
    )
  )

(2)方式二：未配置本地服务的
  create public database link report_link connect to zxdbm_misp identified by zxin_smap         using
   '(DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 10.46.178.155)(PORT = 1522))
      )
     (CONNECT_DATA =
        (SERVER       = DEDICATED)
        (SERVICE_NAME = zxin)
      )
    )'



16,linux环境下 对oracle 数据文件的导出，导入 步骤（管道方法）
   以oracle用户登陆
  一：对数据文件的导出
  (1)通过mknod -p 建立管道
   mknod /home/exppipe p   --在目录/home下建立一个管道exppipe 注意参数p

  (2)通过exp和gzip导出数据到建立的管道并压缩
    exp  zxdbm_report/zxin_smap@zxin file=/home/exppipe
        &gzip < /home/exppipe > exp.dmp.gz
 
  (3)导出成功完成之后删除建立的管道
      rm -rf /home/exppipe

  二：对导出的数据文件进行导入
  (1)建立管道
    mknod /home/exppipe p

  (2)导入生成的压缩文件
   imp zxdbm_report/zxin_smap@zxin file=/home/exppipe fromuser=zxdbm_report
   touser=zxdbm_report
    & gunzip < exp.dmp.gz > /home/exppipe
  (3)删除管道
   rm /home/exppipe


17，管道方法导出指定的表（例如 导出用户 zxdbm_report 下的表muser_basicinfo ）
    exp zxdbm_report/zxin_smap@zxin tables = muser_basicinfo
    file = /zxinbak/exppipe &gzip</zxinbak/exppipe>
    zxdbm_report_muser_basicinfo_bak.dmp.gz
    管道方法导入指定的表文件
    imp zxdbm_report/zxin_smap@zxin tables=muser_basicinfo
     file=/zxinbak/exppipe
     &gunzip<zxdbm_report_muser_basicinfo_bak.dmp.gz> /zxinbak/exppipe


18，imp ,exp  导入导出命令
  导出：exp  zxdbm_report/zxin_smap@zxin tables=muser_basicinfo file = test.dmp  ignore = n
  ---注： ignore = y 表示向表中追加数据 ignore= n 覆盖表数据

 
  导入：imp zxdbm_report/zxin_smap@zxin tables=muser_basicinfo file = test.dmp ignore = n


19，查看数据库的 db_block_size 大小
   sql>show   parameters   db_block_size


20，统计每张表占据的大小
  select owner,table_name,NUM_ROWS,BLOCKS*8192/1024/1024
   Size_m,EMPTY_BLOCKS,LAST_ANALYZED
   from dba_tables  t where t.OWNER='ZXDBM_REPORT'

21，获得日期属于第几周
  select to_char(sysdate,'iyyyiw') from dual;

22，获得日期所在周的第一天是几号
  select trunc(sysdate,'iw') from dual;

23，获得日期所在的周的第一天是几号（14位日期格式）
select  
to_char(trunc(to_date('20120201','yyyy-mm-dd'),'IW'),'yyyymmdd')||'000000'
from  dual;
结果：20120201 所在周的第一天是 20120130 号。

24，获得日期所在的周的周末是几号（14位日期格式）
select
to_char(trunc(to_date('20120201','yyyy-mm-dd'),'IW')+6,'yyyymmdd')||'999999'
from dual
结果：20120201 所在周的周末是 20120205 号。

  注：iw 为国际标准日期 ww 为从年的第一天算起，一年的第一天算做第一周的第一天。


25，oracle 存储过程 语法
create or replace procedure insert_report_catvisdown_proc(
  i_querydate  in varchar2) is   

sql            VARCHAR2(50);  ---sql 语句变量
type           doorcursor is ref cursor; --- 声明一个游标类型 doorcursor
listcursor     doorcursor;    ----  一个游标变量

v_a               VARCHAR2(50);
v_b               VARCHAR2(50);

                            -----其他变量

begin               --- 过程体开始
while(v_todaystr >= v_reportdate)loop  ---1
  if(conditon) then
    
    sql = '
             select a, b   from table1,table2 where table1.xx = table2.xx

   '
   if(sql in not null)  then
    open listcurso for sql;
    fetch listcurso into v_a ,v_b;
     while  listcurso%found loop
         insert into table3(a,b)values(v_a,v_b);
      fetch listcurso into v_a,v_b;
      end loop;
      commit;
      close listcurso;----关闭游标
     end if;
   end if;

   exception ----出现异常执行的模块
       when others then
        rollback;--数据回滚
        erromsg :=substr(sqlerrm,1,500); ----数据库抛出的异常信息

         ----此处添加出现异常时，业务逻辑代码
    
      
   
   end;---异常模块结束
   

  end loop;  ---1 结束
end insert_report_catvisdown_proc;  ------ 过程体结束


27，linux 环境下启动数据库
   登陆oracle用户

oracle@linux:~> lsnrctl start       -----监听启动
oracle@linux:~> sqlplus "/as sysdba"
               启动  startup         ---启动数据库服务
               关闭  shutdown abort ---关闭数据库服务

28，linux 常用命令：
   vi  文件名     按i  进入编辑状态  ----编辑文件
  :wq                                -----保存编辑的文件并退出编辑状态
   echo  $CATALINA_HOME           ----查看变量代表的文件路径
   chown -R oracle:dba  /zxindata/    ---- 改变所属组。
   df -h                          --------查看硬盘空间大小 
   chmod -R  777  文件名          -------给文件赋权
29,ORACLE job
----创建job
VARIABLE jobid number;
begin
    sys.dbms_job.submit(:jobid,'jackupdate;',sysdate,'sysdate+1/144');
    commit;
end;
-----执行job
begin
  dbms_job.run(3017);
end;
---停止
begin
  dbms_job.broken(50,true);
  commit;
end; 
---查看job
  select * from dba_jobs;       所有的JOB
  select * from dba_jobs_running;  正在运行的
  select * from user_jobs;      当前用户的job
----删除JOB
begin
   dbms_job.remove(:job);--:job可以用dba_jobs.job的值代替如：1198
end;

----执行时间设置
每天午夜12点 'trunc(sysdate+1)'
每天早上8点30分 'trunc(sysdate+1)+(1*60+30)/(24*60)'
每星期二中午12点 'next_day(trunc(sysdate),"tuesday")+ 12/24'
每个月第一天的晚上11点 'trunc(last_day(sysdate)+1)'
每个季度最后一天的晚上11点 'trunc(add_months(sysdate+2/24,3),'Q')-1/24'
每星期六和日早上6点10分 'trunc(least(next_day(sysdate"SATURDAY"),NEXT_DAY(SYSDATE,"SUNDAY")))+(6*60+10)/(24*60)'


30,查看oracle 最大连接数
  show parameter processes;
--修改最大连接数
alter system set processes = 500 scope = spfile;
31,oracle 日志历史信息
select * from v$log_history ;

select * from v$archived_log;