oracle表空间相关

drop tablespace TMP_test including contents and datafiles;

CREATE TABLESPACE test_2018_2
nologging
DATAFILE
'H:\2018_2\test01.dat' size 4096m autoextend off,
'I:\2018_2\test02.dat' size 4096m autoextend off,
'J:\2018_2\test03.dat' size 4096m autoextend off,
'K:\2018_2\test04.dat' size 4096m autoextend off,
'H:\2018_2\test05.dat' size 4096m autoextend off,
'I:\2018_2\test06.dat' size 4096m autoextend off,
'J:\2018_2\test07.dat' size 4096m autoextend off,
'K:\2018_2\test08.dat' size 4096m autoextend off,
'H:\2018_2\test09.dat' size 4096m autoextend off,
'I:\2018_2\test10.dat' size 4096m autoextend off,
'J:\2018_2\test11.dat' size 4096m autoextend off,
'K:\2018_2\test12.dat' size 4096m autoextend off,
'H:\2018_2\test13.dat' size 4096m autoextend off,
'I:\2018_2\test14.dat' size 4096m autoextend off,
'J:\2018_2\test15.dat' size 4096m autoextend off,
'K:\2018_2\test16.dat' size 4096m autoextend off,
'H:\2018_2\test17.dat' size 4096m autoextend off,
'I:\2018_2\test18.dat' size 4096m autoextend off,
'J:\2018_2\test19.dat' size 4096m autoextend off,
'K:\2018_2\test20.dat' size 4096m autoextend off,
'H:\2018_2\test21.dat' size 4096m autoextend off,
'I:\2018_2\test22.dat' size 4096m autoextend off,
'J:\2018_2\test23.dat' size 4096m autoextend off,
'K:\2018_2\test24.dat' size 4096m autoextend off,
'H:\2018_2\test25.dat' size 4096m autoextend off,
'I:\2018_2\test26.dat' size 4096m autoextend off,
'J:\2018_2\test27.dat' size 4096m autoextend off,
'K:\2018_2\test28.dat' size 4096m autoextend off,
'K:\2018_2\test29.dat' size 512m autoextend on next 512m maxsize 10240m,
'K:\2018_2\tecdo30.dat' size 512m autoextend on next 512m maxsize 10240m
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

CREATE TEMPORARY TABLESPACE TMP_test_2018_2
tempfile
'H:\2018_2\tmp_test01.dbf' size 4096m autoextend off,
'I:\2018_2\tmp_test02.dbf' size 4096m autoextend off,
'J:\2018_2\tmp_test03.dbf' size 4096m autoextend off,
'K:\2018_2\tmp_test04.dbf' size 4096m autoextend off,
'K:\2018_2\tmp_test05.dbf' size 512m autoextend on next 512m maxsize 10240m,
'K:\2018_2\tmp_test06.dbf' size 512m autoextend on next 512m maxsize 10240m
extent management local

SELECT tablespace_name,
file_id,
file_name,
round(bytes / (1024 * 1024), 0) total_space
FROM dba_data_files
where tablespace_name='UNDOTBS1'
ORDER BY tablespace_name;

ALTER TABLESPACE UNDOTBS1 ADD DATAFILE  
'I:\UNDOTBS1\UNDOTBS02.DBF' SIZE 4096M;  
ALTER TABLESPACE UNDOTBS1 ADD DATAFILE  
'I:\UNDOTBS1\UNDOTBS03.DBF' size 512m autoextend on next 512m maxsize 10240m

//删除表空间
drop tablespace TECDOC including contents and datafiles;

//创建用户并指定表空间
create user yiUser091 identified by pass123456
default tablespace TECDOC
temporary tablespace TMP_TECDOC;

//给用户授予普通权限
grant connect,resource to yiUser091;

//给用户授予DBA权限
GRANT CREATE USER,DROP USER,ALTER USER ,CREATE ANY VIEW ,
   DROP ANY VIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE,
      DBA,CONNECT,RESOURCE,CREATE SESSION TO yiUser091

创建只读权限oracle账户
1.创建角色
CREATE ROLE READ_ROLE
2.给角色分配权限

grant CREATE VIEW to READ_ROLE;
grant CREATE SYNONYM to READ_ROLE;

grant CONNECT TO READ_ROLE;
grant SELECT ANY DICTIONARY to READ_ROLE;
grant SELECT ANY TABLE to READ_ROLE;
grant SELECT ANY TRANSACTION to READ_ROLE;
grant SELECT ANY SEQUENCE to READ_ROLE;


3.把角色赋予指定账户

CREATE USER user IDENTIFIED BY user DEFAULT TABLESPACE xxx;
grant READ_ROLE to user;
4.删除角色
drop role READ_ROLE;

5.检查角色的权限
select * from dba_sys_privs where grantee='READ_ROLE'

测试权限的时候需要加上用户的对象例如
select count(*) from hr.ex_user;

查看表空间大小

SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024 * 1024)), 0) ts_size
FROM dba_tablespaces t, dba_data_files d
WHERE t.tablespace_name = d.tablespace_name
GROUP BY t.tablespace_name;

1.查看表空间使用率（包括临时表空间）
select * from (
Select a.tablespace_name,
to_char(a.bytes/1024/1024,'99,999.999') total_bytes,
to_char(b.bytes/1024/1024,'99,999.999') free_bytes,
to_char(a.bytes/1024/1024 - b.bytes/1024/1024,'99,999.999') use_bytes,
to_char((1 - b.bytes/a.bytes)*100,'99.99') || '%' use
from (select tablespace_name,
sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name,
sum(bytes) bytes
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
union all
select c.tablespace_name,
to_char(c.bytes/1024/1024,'99,999.999') total_bytes,
to_char( (c.bytes-d.bytes_used)/1024/1024,'99,999.999') free_bytes,
to_char(d.bytes_used/1024/1024,'99,999.999') use_bytes,
to_char(d.bytes_used*100/c.bytes,'99.99') || '%' use
from
(select tablespace_name,sum(bytes) bytes
from dba_temp_files group by tablespace_name) c,
(select tablespace_name,sum(bytes_cached) bytes_used
from v$temp_extent_pool group by tablespace_name) d
where c.tablespace_name = d.tablespace_name
)
order by tablespace_name
2.查看文件是否自动扩展
select d.file_name,d.tablespace_name,d.autoextensible from dba_data_files d
如果想查看临时表空间文件是否自动扩展
select d.file_name,d.tablespace_name,d.autoextensible from dba_temp_files d;
3.在检查表文件时 发现 表空间escalade使用率接近100%，且不可扩展
修改文件自动可扩展性
alter database datafile 'E:xxxxxxESCALADE.ORA' autoextend on;

4.重建索引就可以了

5、修改表空间中数据文件的状态
    数据文件的状态主要有三种，onlne，offline和offline drop。设置数据文件状态的语法

如下：
alter database datafile file_name online|offline|offline drop

增加表空间

ALTER TABLESPACE app_data ADD DATAFILE
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP04.DBF' SIZE 50M
AUTOEXTEND ON NEXT 512M MAXSIZE 4096M;

修改数据文件大小

ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF' AUTOEXTEND ON NEXT 5M MAXSIZE 100M;

增加临时表空间

ALTER TABLESPACE app_data ADD TEMPFILE
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP04.DBF' SIZE 50M
AUTOEXTEND ON NEXT 512M MAXSIZE 4096M;