创建用户及表空间分配权限

创建表空间
windows下

--创建表空间
create tablespace test_data 
logging  
datafile 'D:\APP\luxuefeng\ORADATA\orcl\test_data.dbf' 
size 50m  
autoextend on  
next 50m maxsize 600m  
extent management local; 

linux下

--创建表空间
create tablespace TEST_DB datafile '/oracle/product/fstest/testdb.dbf' 
size 500M reuse autoextend on 
next 40M maxsize unlimited default storage(initial 128k next 128k minextents 2 maxextents unlimited);

建用户

--2.建用户  
create user test identified by test   
default tablespace test_data;  

赋权

--3.赋权  
grant connect,resource to test;  
grant create any sequence to test;  
grant create any table to test;  
grant delete any table to test;  
grant insert any table to test;  
grant select any table to test;  
grant unlimited tablespace to test;  
grant execute any procedure to test;  
grant update any table to test;  
grant create any view to test; 

--赋权DBA
grant dba to test;

--表空间改名

alter tablespace  scott_data rename to luffy_data;

表空间查询

--查询表及所属表空间
SELECT TABLE_NAME,TABLESPACE_NAME FROM USER_TABLES;
--查看表空间物理文件的名称及大小
SELECT tablespace_name, 
file_id, 
file_name, 
round(bytes / (1024 * 1024), 0) total_space 
FROM dba_data_files 
ORDER BY tablespace_name;


--查看表空间详细情况
SELECT a.tablespace_name "表空间名", 
total "表空间大小", 
free "表空间剩余大小", 
(total - free) "表空间使用大小", 
total / (1024 * 1024 * 1024) "表空间大小(G)", 
free / (1024 * 1024 * 1024) "表空间剩余大小(G)", 
(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)", 
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; 

扩充表空间方法

--Oracle增加表空间大小的四种方法
--Meathod1：给表空间增加数据文件
ALTER TABLESPACE app_data ADD DATAFILE
 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF' SIZE 50M;
  
-- Meathod2：新增数据文件，并且允许数据文件自动增长
ALTER TABLESPACE app_data ADD DATAFILE
 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP04.DBF' SIZE 50M
 AUTOEXTEND ON NEXT 5M MAXSIZE 100M;
  
-- Meathod3：允许已存在的数据文件自动增长
ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF'
 AUTOEXTEND ON NEXT 5M MAXSIZE 100M;
  
 --Meathod4：手工改变已存在数据文件的大小
ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP02.DBF'
 RESIZE 100M;