常用oracle操作(二)

-----------------------------------查看存储过程的情况-----------------------------------------------------------
查看存储过程内容：select text from user_source where name =upper('sp_kr_pd_markplan_info')
查看是否调用存储过程：select * from dgdm_dw.tb_dw_sys_log where table_name='TB_DW_MD_WORKF_MARKPLAN_CUR';
查看分区的命令：从视图user_tab_partitions  和  user_segments 中可以查询到
删除分区的命令:
     单个删：alter table table_name drop partition partition_name
     删除一个表下所有的分区：
        SELECT 'alter table '||owner||'.'||segment_name||' drop partition '||partition_name||';'
        from dba_segments where
        segment_name like 'TB_MK_LS_USER_CELL_DTAL_MID'
        order by partition_name
增加新分区命令：alter table table_name add partition partition_name values()
                alter table dgdm_mk.TB_MK_SC_CALL_FEE_DAY  add partition CALL_FEE_DAY_20070930 values(to_date(20070930,'yyyymmdd') ) tablespace tbs_mk_list_data_3;
查看存储过程中含有tb_kr_kpi_nuser_day：select * from user_source t where t.type like '%tb_kr_kpi_nuser_day%'
查看表结构命令 select dbms_metadata.get_ddl('TABLE','TB_KR_SC_VIP_MON','dgdm_kr') from dual;

查看存储过程命令 select text from user_source where name =upper('sp_kr_pd_markplan_sta_qqt')；
编译存储过程 alter procedure sp_kr_pd_markplan_sta_qqt COMPILE；
压缩分区或者表数据
execute immediate 'alter table tb_mk_user_opp_last_call_day move partition OPP_LAST_CALL_DAY_'||iv_date||' compress';

-------------------------------------批量执行存储过程-----------------------------------------
run.sql内容：
var hhh number;
exec dgdm_mk.SP_DWMK_OPP_USER_CELL_MON('20070815',:hhh);
/
exec dgdm_mk.SP_DWMK_OPP_USER_INFO('20070815',:hhh);
/
exec dgdm_mk.SP_DWMK_OPP_USER_INFO_WEEK('20070815',:hhh);
/
exit;
登陆sqlplus后执行@run.sql
或者在unix用户下执行sqlplus user/password @run.sql

nohup sqlplus user/password @test.sql&  放到后台执行并写入日志文件 nohup.out文件中
cat nohup.out;  可查看日志

-------------------------------------权限问题-------------------------------------------------------------------------

grant all on test.hxytmp to public;

------------------------------------查看进程情况----------------------------------------------------------
select   p.spid,c.object_name,a.sid,a.serial#,b.session_id,b.oracle_username,b.os_user_name  
from   v$process   p,v$session   a,   v$locked_object   b,all_objects   c  
where   p.addr=a.paddr   and   a.process=b.process   and   c.object_id=b.object_id 

-----------------------------------查看表和基本信息的情况--------------------------------------------------------------
显示表结构：
select dbms_metadata.get_ddl('TABLE','TB_MK_SC_USER_WEEK','DGDM_MK') FROM DUAL;

1、查看表空间的名称及大小
column db_name format a10
select SYS_CONTEXT ('USERENV', 'db_name') db_name,t.tablespace_name,round(sum(bytes/(1024*1024)),0) ts_size
from sys.dba_tablespaces t, sys.dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name
order by 2;

6、查看表空间的使用情况
select tablespace_name,round(sum(bytes)/1024/1024/1024) as free_space
from dba_free_space
group by tablespace_name
order by 1;

移动表分区到指定的表空间中
select 'alter table dgdm_mk.'||table_name
 ||' move partition '||partition_name
 ||' tablespace tbs_mk_list_data_'||case mod(substr(partition_name,-4,2),6) when 0 then 6 else mod(substr(partition_name,-4,2),6) end
 ||';'
from all_tab_partitions where table_name = 'TB_MK_SC_CALLLIST_FEE_DAY'
;

--表空间使用情况
SELECT A.TABLESPACE_NAME,round(A.BYTES/1024/1024/1024) TOTAL,round(B.BYTES/1024/1024/1024) USED, round(C.BYTES/1024/1024/1024) FREE,
round((B.BYTES*100)/A.BYTES,2) "% USED",round((C.BYTES*100)/A.BYTES,2) "% FREE"
FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME
--and round((B.BYTES*100)/A.BYTES,2) >90
order by 1;

--表空间内对象
column segment_name format a30
column partition_name format a30
select segment_name,partition_name,bytes/1024/1024 from dba_segments where tablespace_name ='TBS_DW_LIST_DATA_5' order by 1,2;

压缩分区
alter table dgdm_dw.tb_dw_ls_cdr_gsm move partition ls_cdr_gsm_20070501 compress parallel 30;
--对象有效性
Select owner,object_Name,status From all_objects
Where owner In('GDDM_CB','DGDM_ODS','DGDM_DW','DGDM_KR','DGDM_MK')
 And object_type In ('PROCEDURE','FUNCTION')
Order By 1,2

select dbms_metadata.get_ddl('TABLE','TB_KR_SC_VIP_MON','dgdm_kr') from dual;

----------------------------------------临时表空间------------------------------------------------------
--v$sort_segment字典可以记载temp的比较详细的使用情况
select tablespace_name,current_users,total_blocks,used_blocks,free_blocks from v$sort_segment;
--v$sort_usage将会告诉我们是谁在做什么
select username,session_addr,sqladdr,sqlhash from v$sort_usage;
--更详细的操作
select se.username,se.sid,su.extents,su.blocks*to_number(rtrim(p.value)) as Space,tablespace,segtype,sql_text from v$sort_usage su,v$parameter p,v$session se,v$sql s
where p.name='db_block_size' and su.session_addr=se.saddr and s.hash_value=su.sqlhash and s.address=su.sqladdr order by se.username,se.sid;

---------------------------------------oracle导入导出数据(备份)----------------------------------------

oracle数据库备份有两种方法：物理备份(数据库运行在归档模式下)和
 逻辑备份(数据库不需要运行在归档模式下，通常业务数据库采用逻辑备份，它备份比较简单)
逻辑备份数据库：
 oracle数据库的逻辑备份有三种模式：表备份，用户备份，完全备份
 表备份：备份某个用户下指定的对象，备份到本地文件命令：
  exp icdmain/icd rows=y indexs=n compress=n buffer=65536 feedback=100000 volsize=0
  file=test.dmp log=test.log tables=icdmain.table1,icdmain.table2 STATISTICS=none
 用户备份：备份某个用户模式下所有的对象，备份到本地文件命令：
  exp icdmain/icd owner=icdmain rows=y indexs=n compress=n buffer=65536 feedback=100000 volsize=0
  file=test.dmp log=test.log STATISTICS=none
 完全备份：备份完整的数据库，备份到本地的命令如下：
  exp icdmain/icd
恢复方案：
 数据库逻辑恢复分为表恢复、用户恢复、完全恢复三种模式
 表恢复：
  A:恢复备份数据的全部内容,转向文件所在的目录,从本地文件恢复
   imp icdmain/icd fromuser=icdmain touser=icdmain rows=y indexs=n commit=y buffer=65536 feedback=100000 ignore=n volsize=0
   file=test.dmp log=test.log STATISTICS=none
  B:恢复备份数据
   imp icdmain/icd fromuser=icdmain touser=icdmain rows=y indexs=n commit=y buffer=65536 feedback=100000 ignore=n volsize=0
   file=test.dmp log=test.log tables=table1,table2
 用户恢复：
  imp icdmain/icd fromuser=icdmain touser=icdmain rows=y indexes=n
  commit=y buffer=65536 feedback=100000 ignore=n volsize=0
  file=exp_icdmain_yyyymmdd.dmp log=imp_icdmain_yyyymmdd.log
 完全模式：
  imp system/manager rows=y indexes=n commit=y buffer=65536
  feedback=100000 ignore=y volsize=0 full=y
  file=exp_icdmain_yyyymmdd.dmp log=imp_icdmain_yyyymmdd.log  
参数说明
1. ignore参数
Oracle在恢复数据的过程中，当恢复某个表时，该表已经存在，就要根据ignore参数的设置来决定如何操作。
若ignore=y，Oracle不执行CREATE TABLE语句，直接将数据插入到表中，如果插入的记录违背了约束条件，比如主键约束，则出错的记录不会插入，但合法的记录会添加到表中。
若ignore=n，Oracle不执行CREATE TABLE语句，同时也不会将数据插入到表中，而是忽略该表的错误，继续恢复下一个表。
2. indexes参数
在恢复数据的过程中，若indexes=n，则表上的索引不会被恢复，但是主键对应的唯一索引将无条件恢复，这是为了保证数据的完整性。  
恢复方法
业务数据库采用表恢复方案。在用IMP进行恢复前，先在SYS用户下运行CATEXP.SQL文件（如果以前已运行该文件，则不要执行这个脚本），然后执行下列命令：
IMP ICDMAIN/ICD FILE=文件名 LOG=LOG文件名 ROWS=Y
COMMIT=Y BUFFER=Y IGNORE=Y TABLES=表名
注：要恢复的表名参照备份的表名
　 。恢复是在原表基础上累加数据
　 。没有特殊说明，不允许在客户端执行恢复命令
恢复时注意事项：如果存在分区表，目标分区表中的分区必须存在源分区表中的分区，否则会提示分区不存在  
  
从oracle数据库导出数据命令：
exp dgdm_ods/dgdm_ods file=XXXX0615 log=XXXX0615 direct=y tables=dgdm_dw.ab1,tab2,tab3:part1,tab4:part2,
 
表的话直接写表名,可以加上用户名
direct=y：从直接路径倒出数据，比常规方式更快速;

导入数据命令：
imp dgdm_kr/dgdm_kr file=kr0615.dmp full=y  log=imp.kr0615.dmp.log ignore=y

导出表结构和存储过程
exp dgdm_ods/dgdm_ods file=dgdm_ods_struct rows=no log=dgdm_ods
...
关键是rows=no不导入数据

---------------------------------------oracle数据库启动和关闭----------------------------------------
ORA-01033: ORACLE initialization or shutdown in progress
原因：可能是起了一个sqlplus来关闭数据库，但数据库还没有完全关闭之前，从操作系统里把刚才那个sqlplus的进程给杀掉了
      另一种可能是oracle的一些核心后台进程，被人从操作系统里杀掉了，那么在关闭数据库是就出现了问题
解决方法：
1.在dos下执行sqlplus /nolog
2.以sysdba连接conn / as sysdba
3.startup
4.shutdown immediate
5.startup
数据库成功装载成功

conn bim as sysdba
----------------------------------常用UNIX命令-------------------------------------------------
compress      压缩文件
df            查看文件包下的空间使用情况
rm -f *.*        删除所有文件
rm -r hxy     删除文件夹hxy
mdel or del   ftp时删除文件命令
get filename  取得文件
mget *.*      取得多个文件
put filename  放入文件
mput *.*      放入多个文件

!ls,!pwd   ftp或者telnet时!用来切换目录

SCO命令--df
出自：http://www.fanqiang.com 2001年10月07日 11:29
df命令（disk free的缩写），其功能是显示磁盘可用空间数目信息及空间i结
　　　　点信息。换句话说，就是报告在任何安装的设备或目录中，还剩多少自由的空
　　　　 间。
　　　　 命令格式：df [-f]　[-i]　[-t]　[-v]　[filesystem list]
　　　　 df命令选项说明：
　　　　　 -f 报告文件系统空闲磁盘空间中的自由块数　
　　　　　 -i 分别报告i结点使用数量、自由块数、总数以及所使用的i结点占总数的
　　　　　　 百分比　
　　　　　 -t 报告文件系统占用的全部块数、i结点以及未使用数量
　　　　　 -v 分别报告文件系统拥有的总块数、已使用块数、自由块数以自由块数所占
　　　　　　　的百分比

 

declare
 vd date;
 vd_date date;
 x number;
 vmsg varchar(128);
begin
 vd :=to_date(20070801,'yyyymmdd');
 vd_date := to_date(20070929,'yyyymmdd');
 while vd< vd_date loop
  dgdm_mk.SP_MK_SC_CALL_FEE_DISC_DTAL(to_char(vd,'yyyymmdd'),x);
  vmsg := 'SP_MK_SC_CALL_FEE_DISC_DTAL:'||to_char(vd,'yyyymmdd');
  insert into test.hxytmp values(vmsg);
  commit;
  vd := vd+1;
 end loop;
end;
/

select partition_name,bytes/1024/1024 from user_segments where segment_name = 'TB_MK_USER_LAST_CALLLIST_DAY' order by partition_name