数据库常用操作

数据库的编码问题:
select * from nls_database_parameters where parameter ='NLS_CHARACTERSET';
udpate nls_database_parameters set value='AL32UTF8' where parameter='NLS_CHARACTERSET'    --ZHS16GBK
SELECT
  A.OWNER,                        --OBJECT所属用户
  A.OBJECT_NAME,                  --OBJECT名称（表名）
  B.XIDUSN,
  B.XIDSLOT,
  B.XIDSQN,
  B.SESSION_ID,                   --锁表用户的session
  B.ORACLE_USERNAME,              --锁表用户的Oracle用户名
  B.OS_USER_NAME,                 --锁表用户的操作系统登陆用户名
  B.PROCESS,
  B.LOCKED_MODE,
  C.MACHINE,                      --锁表用户的计算机名称（例如：WORKGROUP\UserName）
  C.STATUS,                       --锁表状态
  C.SERVER,
  C.SID,
  C.SERIAL#,
  C.PROGRAM                       --锁表用户所用的数据库管理工具（例如：ob9.exe）
FROM
  ALL_OBJECTS A,
  V$LOCKED_OBJECT B,
  SYS.GV_$SESSION C
WHERE
  A.OBJECT_ID = B.OBJECT_ID
  AND B.PROCESS = C.PROCESS
ORDER BY 1,2

select A.OBJECT_NAME, b.SESSION_ID,C.SERIAL# from ALL_OBJECTS A, V$LOCKED_OBJECT B, SYS.GV_$SESSION C
WHERE A.OBJECT_ID = B.OBJECT_ID AND B.PROCESS = C.PROCESS

alter system kill session '122,470'

同时可用如下命令来kill掉当前锁表的项：

alter system kill session 'sid, serial#'
--例如：alter system kill session '57, 10325

SELECT DISTINCT  'alter system kill session '''||a.sid||','||a.serial#||''';'
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
AND C.object_id =73744

查数据库版本号:
select * from v$version;

declare v_msg varchar2(100);
begin 
  v_msg := '11:36';
  insert into test(sid, sname) values(1402, v_msg);
commit;
end;

oracle存储过程生成数据：
create or replace procedure chjf_insert(i integer) is
begin
declare h varchar2(20);

begin
 for i in 10000000..50000000 loop
 h:=to_char('001012705230243579'+i);
 insert into call_M_5000W(SOURCE_ID, PROD_INST_ID, ACC_NBR, MCALL_SERV_AREA_CODE, MCALL_SERV_VISIT_CODE, MCALL_SERV_OPERATOR, OPP_NBR, MCALL_OPP_AREA_CODE, MCALL_OPP_VISIT_CODE, MCALL_OPP_OPERATOR, MCALL_IP_FLAG, IP_NUM, BEGIN_TIME, END_TIME, MCALLTIME_DURATION, MCALL_USER_TYPE, MCALL_ROAM_TYPE, MCALL_CALL_TYPE, MCALL_CALL_FLAG, DURATION, BILLING_DURATION, ORI_CHARGE, DISCT_CHARGE, IMSI, CELL_ID, LAC_ID, SOURCE_TABLE, SOURCE_SYS, LOAD_TIME, ROW_CNT, LOCAL_CODE, MONTH_NO, ID) values (h, '1062896764', '5752077', '0551', '', '1', '18688955576', '0130', '', '-1', '1', '', '2010-09-24 01:17:33', '2010-09-24 02:02:45', '2', '-1', '', '20', '1', '2712', '2760', '1830', '-1151', '', '', '', 'CALL_CDR_D', 'HB', '2010-10-13 14:36:54.496548', '', '551', '201009', i+1);
 
if(mod(i,1000)=0) then commit;
 end if;
 end loop;
end;
end;

查表空间：
select b.file_name 物理文件名,
       b.tablespace_name 表空间,
       b.bytes / 1024 / 1024 大小M,
       (b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024 已使用M,
       substr((b.bytes - sum(nvl(a.bytes, 0))) / (b.bytes) * 100, 1, 5) 利用率 from dba_free_space a,
       dba_data_files b where a.file_id = b.file_id
       group by b.tablespace_name,
       b.file_name,
       b.bytes
       order by b.tablespace_name

select b.file_name wenjianm,b.tablespace_name biaokongjian,b.bytes / 1024 / 1024 daxiaoM,(b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024 yishiyongM,substr((b.bytes - sum(nvl(a.bytes, 0))) / (b.bytes) * 100, 1, 5) liyonglv from dba_free_space a,dba_data_files b where a.file_id = b.file_id group by b.tablespace_name,b.file_name,b.bytes order by b.tablespace_name

查数据库文件放在哪里：
select name from v$datafile

数据库创建用户:
SQL> select name from v$datafile;
 
NAME
--------------------------------------------------------------------------------
/home/oracle/oradata/orcl/system01.dbf
/home/oracle/oradata/orcl/undotbs01.dbf
/home/oracle/oradata/orcl/sysaux01.dbf
/home/oracle/oradata/orcl/users01.dbf
/home/oracle/oradata/orcl/example01.dbf
/home/oracle/oradata/orcl/users02.dbf
/home/oracle/oradata/orcl/users03.dbf
/home/oracle/oradata/orcl/users04.dbf
 
8 rows selected.

数据库扩容:
alter tablespace USERS add datafile '/home/oracle/app/oradata/orcl/users02.dbf' size 25000m；
alter tablespace USERS add datafile '/home/oracle/app/oradata/orcl/users03.dbf' size 25000m reuse;

 
SQL> create user gdyd identified by gdyd default tablespace users;
 
User created.
 
SQL> grant dba,connect to gdyd;
 
Grant succeeded.

SQL> grant debug any procedure to gdyd ;
 
Grant succeeded
 
SQL> grant debug connect session to gdyd;
 
Grant succeeded

创建目录:
create or replace directory DIR_DMP
  as '/home/oracle/dmp';

导入dmp文件：
impdp zw/zw directory=DIR_DMP dumpfile=addr20130709.dmp remap_schema=PINSHAN:ZW remap_tablespace=DBO:USERS
logfile=impdp.log

expdp zw/zw directory=DIR_DMP dumpfile=zw.dmp  tables=RKFR_BUILDINGINFO_ADDRESS_BZH,RKFR_COMMUNITYINFO_ADDRESS_BZH,RKFR_DEATHSINFOTABLE_XX_BZH,RKFR_ENTERPRISEDATA_ADDR_BZH,RKFR_FOOD_CY_DATA_OPLOC_BZH,RKFR_FOOD_LT_DATA_OPLOC_BZH,RKFR_FOOD_SC_DATA_ADDRESS_BZH,RKFR_HOUSEINFO_ADDRESS_CER_BZH,RKFR_HOUSEINFO_ADDRESS_HOU_BZH,RKFR_LEGALPERSONINFO_JGDZ_BZH,RKFR_NONCOMMUNITYINFO_ADDR_BZH,RKFR_RESIDENTCOLLECTION_AD_BZH,RKFR_RESIDENTNORMALBASE_SJ_BZH,RKFR_SCHOOLINFO_XXDZ_BZH,RKFR_TZSBDATA_ASADRESS_BZH