--1.创建暂时表空间

create temporary tablespace AUTOMONITORV5_temp

tempfile '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>