Oralce新建用户及表空间维护

--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,CREATE SESSION to automonitorv5;
 
--4.1 导入数据
imp TEST/123456@orcl file=E:\CDP\cdpnew_20160706\backup\oracle11g_dmp\cdp\cdpnew_20160706.dmp full=y

--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;