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;