oracle常用Sql

--查询表空间使用情况
select b.file_id　　文件ID,
       　　b.tablespace_name　　表空间,
       　　b.file_name　　　　　物理文件名,
       　　b.bytes / 1024 / 1024　　　　　　　总M,
       　　(b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024　　　已使用M,
       　　sum(nvl(a.bytes, 0)) / 1024 / 1024　　　　　　　　剩余M,
       　　sum(nvl(a.bytes, 0)) / (b.bytes) * 100　剩余百分比 　　
       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.file_id,
       b.bytes 　　order by b.tablespace_name;

--查询占用空间大的表
SELECT *
  FROM (SELECT   BYTES/1024/1024, segment_name, segment_type, owner
            FROM dba_segments
           WHERE tablespace_name = 'TBS_LOGDATA'
        ORDER BY BYTES DESC)
WHERE ROWNUM < 10 ;

--查询表空间数据文件实际使用情况
select file_name, sum(e.bytes)/1024/1024 as MB 
   from dba_extents e join dba_data_files f on e.file_id=f.file_id 
   group by file_name; 
--创建表空间
create tablespace TBS_CONFIGDATA
datafile '/db/db/oracle/oradata/LOGAUDIT/configdata01.dbf'
size 500M
autoextend on
    next 50M
    maxsize 2048M
extent management local;

--重建表空间
--

--创建回滚表空间
create undo tablespace UNDOTBS
datafile '/db/db/oracle/oradata/LOGAUDIT/undotbs01.dbf;
size 1024M;

--删除表空间,请谨慎使用
--(including contents:指删除表空间中的segments,
--including contents and datafiles :指删除segments和datafiles
--cascade constraints:删除所有与该空间相关的完整性约束条件)

drop tablespace TBS_CONFIGDATA
including contents and datafiles cascade constraints;

--修改表空间数据文件大小(加大)
alter database datafile '/db/db/oracle/oradata/LOGAUDIT/configdata01.dbf'
autoextend on next 100M maxsize 2048M;

--修改表空间数据文件大小(减小)
alter database datafile '/db/db/oracle/oradata/LOGAUDIT/configdata01.dbf'
resize 2048M;

--给表空间添加数据文件
alter tablespace TBS_CONFIGDATA
add datafile '/db/db/oracle/oradata/LOGAUDIT/configdata02.dbf'
autoextend on next 100M maxsize 2048M;

--移动表空间数据文件及控制文件
--停止数据库
--移动数据文件到新的位置
startup mount;
alter database rename file='/home/oracle/oradata/LOGAUDIT/users01.dbf' to '/opt/oracle/oradata/LOGAUDIT/users01.dbf';

--@2或者
--
alter tablespace users offline;
--移动文件
alter database rename file='/home/oracle/oradata/LOGAUDIT/users01.dbf' to '/opt/oracle/oradata/LOGAUDIT/users01.dbf';
alter tablespace users online;

--查询数据文件位置：select name from v$datafile;

--查询日志文件位置：select * from v$logfile;

--查询控制文件位置：select name from v$controlfile;

--查看临时文件位置：select * from v$tempfile;


--新建用户
create user logserver identified by logserver;

--删除用户
drop user logserver cascade;

--授权用户
grant dba to logserver;

--查询锁表
select object_name,machine,s.sid,s.serial#
from v$locked_object l,dba_objects o ,v$session s
where l.object_id　=　o.object_id and l.session_id=s.sid;

--杀死锁表进程
alter system kill session '24,111'; --(其中24,111分别是上面查询出的sid,serial#)

--通过操作系统进程杀死锁表进程
select spid, osuser, s.program
from v$session s,v$process p
where s.paddr=p.addr and s.sid=130;-- （24是上面的sid）
--此步是通过LINUX 终端执行(非SQL)
--kill -9 12345（即第3步查询出的spid）



--ORACLE安装后中文乱码解决
--查询当前的语言环境
select userenv('language') from dual;
--修改步骤
    connect system as sysdba ; 
    shutdown immediate ; 
    startup mount ; 
    alter system enable restricted session ;  
    alter system set JOB_QUEUE_PROCESSES=0;  
    alter system set AQ_TM_PROCESSES=0;  
    alter database open ;  
    alter database character set internal_use ZHS16GBK ;  
    shutdown immediate ; 
    startup ;
   
--创建ORACLE定时器
--创建存储过程
CREATE or replace procedure insertSysDate as
begin
     insert into t values(sysdate);
end;


--创建JOB
DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
  ( job       =&gt; X
   ,what      =&gt; 'insertSysDate;'
   ,next_date =&gt; sysdate
   ,interval  =&gt; 'sysdate + 1/1440'
   ,no_parse  =&gt; FALSE
  );
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;

--启动任务
begin
     dbms_job.run(30);
end;

--删除任务
dbms_job.remove(30);
 
 
 
--查询锁表
select sess.sid,
    sess.serial#,
    lo.oracle_username,
    lo.os_user_name,
    ao.object_name,
    lo.locked_mode
    from v$locked_object lo,
    dba_objects ao,
    v$session sess
where ao.object_id = lo.object_id and lo.session_id = sess.sid;

select * from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID;


select spid, osuser, s.program
from v$session s,v$process p
where s.paddr=p.addr and s.sid=24;



---ORACLE密码过期
--查询用户的profile信息
select username,profile from dba_users;
--查看该profile的密码有效期设置值
select * from dba_profile where profile='DEFAULT' and resource_name='PASSWORD_LIFE_TIME';
--将密码有效期由默认的180天修改为"无限制"
alter profile DEFAULT limit PASSWORD_LIFE_TIME unlimited;