oracle

--oracle debug权限
GRANT debug any procedure, debug connect session TO username;

alter user user01 identified by user10;

DROP USER bci_s cascade;

drop tablespace cbci_s INCLUDING CONTENTS AND DATAFILES ;

select a.JOB,
to_char(a.NEXT_DATE, 'yyyy-mm-dd hh24:mi:ss') NEXT_DATE,
a.INTERVAL,
a.WHAT
from user_jobs a;


--kill session
SELECT A.*
FROM V$PROCESS A, V$SESSION B
WHERE A.ADDR = B.PADDR
AND B.STATUS = 'INACTIVE'
AND B.USERNAME = 'CI_SJ'
UNION
SELECT C.*
FROM V$PROCESS C
WHERE NOT EXISTS (SELECT 1 FROM V$SESSION D WHERE C.ADDR = D.PADDR)
AND C.SPID <> 1;


select 'alter table '||a.table_name||' move tablespace TBS_LSMP_ENTERPRISE;' from user_tables a where a.tablespace_name='TBS_LP_dATA';


select 'alter index ' || ind.index_name || ' rebuild ;'
from user_indexes ind
where ind.status = 'UNUSABLE';


select a.table_name, a.partition_name
from USER_TAB_PARTITIONS a
where a.table_name = 'RECORD_TAB'
order by a.partition_name;


--------------------------------------------
oracle中create了tablespace,产生了相应的dbf文件,然后误删了dbf文件,再drop tablespace的时候报错说找不到该DBF文件.改如何删除这个tablespace文件呢?


connect / as sysdba;
startup mount;
alter database datafile '丢失的DBF数据文件' offline drop;
alter database open;
drop tablespace...
------------------------------------------------


grant IMP_FULL_DATABASE to cbci_cbci;
增加导入权限:grant IMP_FULL_DATABASE to usernamexxx;

增加导入权限:grant IMP_FULL_DATABASE to sms;

----------------------------------------------------------------------------------------------
--数据泵
1、expdp user/pass@orcl DIRECTORY=DUMP_DIR1 dumpfile=20120113.dmp LOGFILE=20120113.log tables=RECORD_TAB;



CBCI_XW/CBCI_XW：要导入的用户名/密码
dump_dir2：导入文件存在的目录名
logfile：导入文件产生的log日志
REMAP_SCHEMA：原用户名：现用户名
REMAP_TABLESPACE=原表空间名:新表空间名

impdp user/pass@orcl dumpfile=20111215.dmp DIRECTORY=TEST_EXPDP logfile=0811.log REMAP_SCHEMA=USER:USER_1008 REMAP_TABLESPACE=USER_TABS:USER_TABS

--1、创建DIRECTORY
create directory dir_dp as D:oracledir_dp;
--2、授权
Grant read,write on directory dir_dp to CBCI_1008;
--3、查看DIRECTORY路径
SELECT privilege, directory_name, DIRECTORY_PATH
FROM user_tab_privs t, all_directories d
WHERE t.table_name(+) = d.directory_name
ORDER BY 2, 1;

--------------------------------------------------------------------------------------------