orcle常用語句

orcle常用語句

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