【Oracle相关】Oracle常用tip

Oracle Tip

–表空间创建
create tablespace itreasury datafile ‘/app/oracle/oradata/XX.dbf’ size 6000M autoextend on next 512M;

–导入时CtrlC中断后，处理方法
select job_name,state from dba_datapump_jobs
impdp XX/XX ATTACH=job_name
stop_job=immediate

–挂起情况下（状态未DEFINING）
select job_name,state from dba_datapump_jobs;

SELECT o.status, o.object_id, o.object_type,o.owner||‘.’||object_name “OWNER.OBJECT”
FROM dba_objects o, dba_datapump_jobs j
WHERE o.owner=j.owner_name AND o.object_name=j.job_name
AND j.job_name NOT LIKE ‘BIN$%’ ORDER BY 4,2;

drop table XX.job_name purge;

–删除用户时用户已连接无法删除用户

1. 先锁定
2. 删除已有会话
3. 再解除锁定
   select username,sid,serial# from v$session where username=‘XX’;
   alter user XX account lock;
   alter system kill session ‘sid,serial#’
   alter user XX account unlock;

–账户被锁定
select * from dba_users where username=‘XX’
alter user XX account unlock;

–查看oracle版本
select * from v$version;selectaddressfromv$sql where rownum < 2;

–查看剩余表空间
select f.TABLESPACE_NAME, sum(f.BYTES) / 1024 / 1024 mb from dba_free_space f group by f.TABLESPACE_NAME;

–查询数据库所有表
select * from user_tables;

– 查询dblink
select * from dba_db_links;

– 删除dblink
drop database link dblink_finance;

–Oracle中long类型数据与date类型数据相互转换

1. date转long :
   当前时间转成long :select sysdate,
   (sysdate-to_date(‘1970-01-01 08:00:00’,‘yyyy-mm-dd hh24:mi:ss’))* 246060*1000
   current_milli from dual;
2. long转date
   select to_date(‘1970-01-01 08:00:00’,‘yyyy-mm-dd hh24:mi:ss’)+ 1657681800000/1000/24/60/60 from dual;