oracle 有用的SQL语句与oracle的 基本命令

select sysdate - interval '2' day from dual

select to_date('2013-03-22 15:43:51','yyyy-mm-dd hh24:mi:ss') - interval '1' day from dual;

   select sysdate,
           sysdate + interval '1-1' year to month col01,
           sysdate + interval '1 1:11' day to minute col02,
           sysdate + interval '1-1' year to month + interval '1 1:1:1' day to second col03
   from dual;
select tablespace_name,sum(bytes)/1024/1024, sum(blocks) from dba_free_space group by tablespace_name

select * from dba_snapshots;

select * from dba_db_links;

select df.tablespace_name name,df.file_name "file",f.phyrds pyr,
f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbw
from v$filestat f, dba_data_files df
where f.file# = df.file_id
order by df.tablespace_name;

select tablespace_name,count(tablespace_name) from dba_free_space group by tablespace_name
having count(tablespace_name)>10;

select * from tab;

 

select dbf.tablespace_name,
       dbf.totalspace "总量(M)",
       dbf.totalblocks as 总块数,
       dfs.freespace "剩余总量(M)",
       dfs.freeblocks "剩余块数",
       (dfs.freespace / dbf.totalspace) * 100 "空闲比例"
  from (select t.tablespace_name,
               sum(t.bytes) / 1024 / 1024 totalspace,
               sum(t.blocks) totalblocks from dba_data_files t group by t.tablespace_name) dbf,
       (select tt.tablespace_name,
               sum(tt.bytes) / 1024 / 1024 freespace,
               sum(tt.blocks) freeblocks from dba_free_space tt group by tt.tablespace_name) dfs where trim(dbf.tablespace_name) = trim(dfs.tablespace_name);

SELECT Total.name "Tablespace Name",Free_space,(total_space - Free_space) Used_space,total_space FROM (select tablespace_name,sum(bytes / 1024 / 1024) Free_Space from sys.dba_free_space group by tablespace_name) Free,
(select b.name,sum(bytes / 1024 / 1024) TOTAL_SPACE from sys.v_$datafile a,
sys.v_$tablespace B where a.ts# = b.ts# group by b.name) Total WHERE Free.Tablespace_name = Total.name;

 

--解锁：
select t2.username,t2.sid,t2.serial#,t2.logon_time,t1.Os_User_Name
from v$locked_object t1,v$session t2
where t1.session_id=t2.sid order by t2.logon_time;

Select p.Spid,c.Object_Name,b.Session_Id,a.serial#,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
And c.object_name =UPPER( 'T_CMS_ENABLER');

 

 

alter system kill session '868,772';

 

 

 

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

oracle通过命令查看错误码 命令：oerr ora 30012
设置hostname newhostName

oracle的启动文件路径:/opt/oracle/product/11gR2/db/dbs/spfileSID.ora

查看spfile命令:show parameter spfile;