orcle常用语句

--1.创建临时表空间create temporary tablespace AUTOMONITORV5_temptempfile 'D:\ORACLE\KARL\ORADATA\ORCL\AUTOMONITORV5_temp.DBF'  size 50m    autoextend on   next 50m maxsize 10240m   extent management local; --2.创建表空间create tablespace AUTOMONITORV5   logging   datafile 'D:\ORACLE\KARL\ORADATA\ORCL\AUTOMONITORV5.DBF'  size 50m   autoextend on   next 50m maxsize 12720m   extent management local;  --3.创建用户create user automonitorv5 identified by "123456"   default tablespace AUTOMONITORV5   temporary tablespace AUTOMONITORV5_temp;  --4.授权grant connect,resource,dba to automonitorv5; --5.删除用户drop user automonitorv5 cascade;--6.删除表空间DROP TABLESPACE automonitorv5 INCLUDING CONTENTS AND DATAFILES;--7.查询表空间位置及大小select tablespace_name,       file_id,       file_name,       round(bytes / (1024 * 1024), 0) total_space  from dba_data_files order by tablespace_name; --8.表空间使用率SELECT a.tablespace_name "表空间名",       total "表空间大小",       free "表空间剩余大小",       (total - free) "表空间使用大小",       Round((total - free) / total, 4) * 100 "使用率   %"  FROM (SELECT tablespace_name, Sum(bytes) free          FROM DBA_FREE_SPACE         GROUP BY tablespace_name) a,       (SELECT tablespace_name, Sum(bytes) total          FROM DBA_DATA_FILES         GROUP BY tablespace_name) b WHERE a.tablespace_name = b.tablespace_name; --9.增大表空间大小alter database datafile 'D:\ORACLE\KARL\ORADATA\ORCL\AUTOMONITORV5.DBF' resize 4000m;--10.增加文件个数alter tablespace AUTOMONITORV5     add datafile 'D:\ORACLE\KARL\ORADATA\ORCL\AUTOMONITORV51.DBF' size 1000m;     --11.设置表空间自动增长alter database datafile 'D:\ORACLE\KARL\ORADATA\ORCL\AUTOMONITORV5.DBF'      autoextend on next 100m maxsize 10240m;--12.获取10年前的日期select to_char(sysdate-numtoyminterval(10,'year'),'yyyy-MM-dd HH24:mi:ss') from dual;--13.小数转字符补零方法1)将小数点前的第一位置为0即可(注意9的个数要大于数值的位数)select to_char(.23,'fm999999990.999999999') from dual;2)既然小于1的小数首位必然是'.'，那就判断首位是否为'.'，是则在前面加上'0'即可select decode(substr(.23,1,1),'.','0'||.23,.23) from dual; <a target=_blank href="http://www.2cto.com/database/201304/205087.html">oracle小数转字符串</a>