收集的一些oracle使用，备份

收集的一些oracle使用，备份

 

1、建别名
 create synonym tx_user_dbms for param.tx_user_dbms ;
2、删除别名
 drop synonym tx_group_dbms;
3、赋权限
 a.表
  grant [select][,insert][,update] on TD_WEEK to public;
 b.存储过程
  grant EXECUTE on p_sett_sum_norcdrs to stat;
4、收回权限
 a.表
  revoke [select][,insert][,update] on td_week from public;
 b.存储过程
  revoke EXECUTE on p_sett_sum_norcdrs from stat;
5、切换数据库
 connect nn/nn7897@toll;

6、执行SQL文件
 SQL>spool /sqlscripts/Install.log;
 SQL>@/sqlscripts/Install.sql;

 当脚本运行完后，然后键入以下命令：
 SQL>spool off

7、从一个表创建同样的另一个表（注意：主键和索引不会创建）
 create table xxx as select */xxx,xxx from xxx
   根据分区表创建分区表，目标分区表必须和源表分区定义一样
 create table test1
 partition by range(a)
 (partition part1 values less than(10),
  partition part2 values less than(20),
  partition partother values less than(maxvalue)
 ) as select * from test

 建list分区表:
create table TG_OmsisdnUP
(
  PCODE     CHAR(3),
  AREA_CODE CHAR(3),
  FEE_CODE  CHAR(5) not null,
  H0H1H2H3  VARCHAR2(8),
  NET_CODE  VARCHAR2(6)
)
tablespace BILL_G_PARAM
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 1040K
    minextents 1
    maxextents unlimited
  )
--130,131,132,133,153,134,135,136,137,138,139,159
PARTITION BY list(NET_CODE)
(PARTITION p130 VALUES ('130'),
PARTITION p131 VALUES ('131'),
PARTITION p132 VALUES ('132'),
PARTITION p133 VALUES ('133'),
PARTITION p134 VALUES ('134'),
PARTITION p135 VALUES ('135'),
PARTITION p136 VALUES ('136'),
PARTITION p137 VALUES ('137'),
PARTITION p138 VALUES ('138'),
PARTITION p139 VALUES ('139'),
PARTITION p153 VALUES ('153'),
PARTITION p159 VALUES ('159'),
PARTITION PMAX VALUES(DEFAULT)
);

8、查看是否有无效的存储过程
 select object_type,owner,object_name
 from  all_objects
 where  status='INVALID' and object_type='PROCEDURE'

   查看对象
 select object_type,owner,object_name
 from  all_objects
 where object_type='TABLE'

9、重新编译存储过程
 alter PROCEDURE 存储过程名 compile

10、从带表指定的分区上检索数据
 select * from 表名 partition (分区名) where 条件

11、重建索引
 alter index 索引名 rebuild online

12、备份表数据：
 exp nn/nn7897 file=rate_nn.dmp tables=t1[,t2...,tn] direct=Y indexes=n
    或
 exp parfile=/oracle9i/expparam.sql 1>>1.log 2>>2.log
    恢复表数据：
 imp nn/nn7897 file=rate_nn.dmp tables=t1[,t2...,tn] ignore=y
    恢复表分区数据：
 imp nn/nn7897 file=rate_nn.dmp tables=TC_RATE_NORCDRS:m02 ignore=y

   建立par文件，如：
userid=param/param7897@gxjf
file=param_toll.dmp
grants=n
direct=n
indexes=n
tables=(
MESSAGES,
TD_ALERT_CODE
)
   执行exp命令：
exp parfile=param.par

    恢复由dba权限的用户备份的带用户信息的数据：
 SQL>grant dba to cdma_param;
 $imp cdma_param/cdma_param123@gsjf file=param_bak_0705.dmp IGNORE=y fromuser=param touser=cdma_param
 SQL> revoke dba from cdma_param;

13、查看表空间
 col 物理文件名 format a35;
 col 表空间 format a20;
 col 剩余 format 99,999,999,999;

  select a.tablespace_name　　表空间,
　　a.bytes　　　　　　　总字节数,
　　a.bytes-b.bytes　　　已使用,
　　b.bytes　　　　　　　　剩余,
　　b.bytes/a.bytes*100　剩余百分比
　　from (select tablespace_name,sum(BYTES) as bytes from dba_data_files group by tablespace_name ) a,
         (select tablespace_name,sum(BYTES) as bytes from dba_free_space group by tablespace_name ) b
　　where a.tablespace_name=b.tablespace_name
      and a.tablespace_name like 'TBS%'
　　  and b.bytes<4000000000

14、起停监听： 
 lsnrctl start[stop][status]

15、登陆主机修改用户口令
 >sqlplus /nolog
 SQL>connect / as sysdba;
 SQL>alter user system identified by system321;

16、查看对象定义语句
 存储PL/SQL块包括存储过程、函数、包和触发器，它们都存放在SYSTEM表空间上，相关参数信息可以从all_arguments,user_arguments,dba_arguments中取得，源码可以从all_source,user_source,dba_source中取得（加密的除外）。
 如：select text from all_source where owner='NN' and name='P_LOCAL_STAT_NORCDRS';

17、清除表分区数据
 alter table table_name truncate partition table_partition_name;

18、oracle中读写文件
 函数定义：
 UTL_FILE.FOPEN (location IN VARCHAR2,filename IN VARCHAR2,open_mode IN VARCHAR2,max_linesize IN BINARY_INTEGER) RETURN file_type;
 参数open_mode：open_mode Open mode (r, w, a).

 例如：
 declare
  file_handle UTL_FILE.FILE_TYPE;
 begin
  file_handle := UTL_FILE.FOPEN('/tmp', '文件名', 'w');
  UTL_FILE.PUTF(file_handle, '写入的信息/n');
  UTL_FILE.FCLOSE(file_handle);
 exception
  WHEN utl_file.invalid_path THEN
  raise_application_error(-20000, 'ERROR: Invalid path for file or path not in INIT.ORA.');
 end;

 需要在INIT.ORA中配置utl_file_dir，方法：用SHOW PARAMETER SPFILE 能看到SPFILE 的所在位置，在相同目录下有INIT实例名.ORA文件，通过它找到INIT.ORA，修改后，用STARTUP PFILE=INIT.ORA(完整路径).CREATE SPFILE FROM PFILE，重新STARTUP即可
 #UTL_FILE_DIR=/leinew/oradata/utlfile/
 #UTL_FILE_DIR=/leinew/export/zhdoc/public/infdbf/inf/
 #UTL_FILE_DIR=/leinew/export/zhdoc/public/infdbf/tide/
 #UTL_FILE_DIR=/leinew/export/zhdoc/public/infdbf/life/
 UTL_FILE_DIR= *

19、执行外部存储过程
 a.新建外部DLL，如：p_link.c
   #include <string.h>
   void p_link(char *src,char *des)
   {
     int ret;
     ret=link(src,des);
   }
   编译：
   >cc +Z
   >dl

 b.创建library
   create or replace library P_LINK as '/bea/nmbill/c_link/p_link.sl';

 c.创建存储过程
   create or replace procedure p_link_file(f1 varchar2,f2 varchar2)
   as external library p_link name "p_link" language C;

 d.在listener.ora中加入ENVS项
   SID_LIST_LISTENER =
    (SID_LIST =
     (SID_DESC =
       (SID_NAME = PLSExtProc)
       (ORACLE_HOME = /oracle/app/oracle/product/9.2.0)
       (PROGRAM = extproc)
       (ENVS=EXTPROC_DLLS=ANY) #增加项

 e.调用：exec p_link_file('/aaa.aaa','/dd/aaa.aaa');

20、更改用户口令
 alter user 用户 identified by 新密码;

21、起停数据库
 8i
 $svrmgrl
 svrmgrl>connect internal
 svrmgrl>shutdown normal/immediate  --正常/快速
 svrmgrl>startup

 9i
 $sqlplus /nolog
 SQL>connect /as sysdba
 SQL>shutdown normal/immediate  --正常/快速
 SQL>startup

22、存储过程里执行动态SQL
 a.
 v_cursor         number;
 v_rows           number;

 v_cursor := dbms_sql.open_cursor;
 dbms_sql.parse(v_cursor,v_sql,dbms_sql.v7);
 v_rows := dbms_sql.execute(v_cursor);
 dbms_sql.close_cursor(v_cursor);

 b.
 execute immediate v_sql;

23、锁定表

　　LOCK语句常常用于锁定整个表。当表被锁定后，大多数DML语言不能在该表上使用。LOCK语法如下：
 LOCK schema table IN lock_mode

　　其中lock_mode有两个选项：
　　　share 共享方式
　　　exclusive 唯一方式

　　例：LOCK TABLE intentory IN EXCLUSIVE MODE

24、起停触发器
 alter table TD_BILLING_CYCLE disable all triggers;
 alter table TD_BILLING_CYCLE enable all triggers;

25、动态调用存储过程
 v_cc := 'begin stat.p_test(:1,:2);end;';
   execute immediate v_cc using in out v_aa,in out v_bb;

26、如何查看SGA区剩余可用内存？
 select name,
　　　　　　sgasize/1024/1024　　　　　　　　"Allocated(M)",
　　　　　　round(bytes/1024/1024,2)　　　　　　　　　　　 "自由空间(M)",
　　　　　　round(bytes/sgasize*100, 2)　　　"自由空间百分比(%)"
　　 from   (select sum(bytes) sgasize from sys.v_$sgastat) s, sys.v_$sgastat f
　　 where  f.name = 'free memory';

27、日志文件
 数据库启动、关闭的日志记录在alert_SID.log; (sid为具体实例名，如alert_gxjf.log)
 最近（比如昨天）的数据库表操作，如增、删、改需用到LOGMINER;
 回闪查询：select * from v$logfile

28、查看版本信息
 select banner from sys.v_$version;

29、更改表的表空间
 ALTER TABLE t1 MOVE TABLESPACE new_tsp;
 ALTER TABLE t1 MOVE PARTITION p01 TABLESPACE new_tsp;

30、数据对象所占用的字节数
 select sum(bytes) from dba_extents where owner='kong' and segment_name ='table_name';

31、删除重复数据
 delete from t1 where rowid in
 (select row_id from
  (select rowid as row_id,row_number() over (partition by c1 order by rowid) rn
   from t1
  )
  where rn>1
 )

32、添加表空间文件
 alter tablespace act01 add datafile '/disk/data1/act01_02.dbf' size 4094m;

分区表索引的使用：
分区表和一般表一样可以建立索引，分区表可以创建局部索引和全局索引。当分区中出现许多事务并且要保证所有分区中的数据记录的唯一性时采用全局索引。

33、局部索引分区的建立：
 create index dinya_idx_t on dinya_test(item_id)
 local
 (
   partition idx_1 tablespace dinya_space01,
   partition idx_2 tablespace dinya_space02,
   partition idx_3 tablespace dinya_space03
 );

34、全局索引分区的建立。
 全局索引建立时global 子句允许指定索引的范围值，这个范围值为索引字段的范围值：
 create index dinya_idx_t on dinya_test(item_id)
 global partition by range(item_id)
 (
   partition idx_1 values less than (1000) tablespace dinya_space01,
   partition idx_2 values less than (10000) tablespace dinya_space02,
   partition idx_3 values less than (maxvalue) tablespace dinya_space03
 );

35、动态SQL绑定变量
 EXECUTE IMMEDIATE 'SELECT :1-:2,200 FROM DuaL' INTO b,c USING IN a,b ;

36、intersect和minus运算 
   
 intersect运算
 返回查询结果中相同的部分
 exp:各个部门中有哪些相同的工种
  selectjob fromaccount
  intersect
  selectjob fromresearch
  intersect
  selectjob fromsales;
 minus运算
 返回在第一个查询结果中与第二个查询结果不相同的那部分行记录。
 有哪些工种在财会部中有，而在销售部中没有？
 exp: selectjobfromaccount
  minus
  selectjobfromsales;
 
37、定义使用动态游标
 type cur_type is ref cursor;
 myCur cur_type;
 v_sql string(100);

 begin
   v_sql:='select ...';
   open myCur for v_sql;
   ...

38、动态执行存储过程
 execute immediate 'begin NX.PG_STAT_CDRS;end;';
 execute immediate 'begin NX.PG_STAT_CDRS(...);end;';

39、查询表空间对应文件名
 select a.name,b.name from v$tablespace a, v$datafile b where a.ts#=b.ts#;

40、删除表空间
 ALTER TABLESPACE 表空间 OFFLINE;
 DROP TABLESPACE 表空间;

41、Shell 批调用 Sqlplus 文件内容：
 sqlplus -S stat/stat123<<!
 set heading off;
 select to_char(sysdate,'yyyymmdd hh24miss')||' begin ---------' from dual;
 exec ptt('');
 select to_char(sysdate,'yyyymmdd hh24miss')||' end.' from dual;
 exit;

42、执行命令时关闭结果输出
 set feedback off
 set define off
 ...
 set feedback on
 set define on

43、判断是否全是数字
 select   nvl2(translate('1224312321','/1234567890','/'),'is   not   number','is   number')   from   dual;