*创建用户：CREATE USER username IDENTIFIED BY password DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON users;
*角色授权给用户：GRANT CONNECT TO username;
*创建角色：CREATE ROLE DEVEPROLE NOT IDENTIFIED;
*授权给角色：GRANT CREATE SESSION, CREATE ANY TABLE, CREATE ANY INDEX, CREATE PROCEDURE,CREATE SEQUENCE TO DEVEPROLE;
*修改用户表空间：alter user 'username' default tablespace users temporary tablespace temp ;
alter user 'username' QUOTA UNLIMITED ON users;
*查询用户被授予的角色：
select granted_role,default_role from dba_role_privs where grantee='DEVEP';
*查询用户被授予的系统权限：
select * from user_sys_privs;
select * from role_sys_privs;
#e.g.
CREATE ROLE SINGLEROLE NOT IDENTIFIED;
CREATE USER testuser IDENTIFIED BY testuser DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON users;
GRANT SINGLEROLE TO testuser;
GRANT CREATE SESSION,
CREATE PROCEDURE,
CREATE SEQUENCE,
CREATE TABLE,
CREATE VIEW,
CREATE TYPE,
CREATE LIBRARY TO SINGLEROLE;
*创建表空间：
#建非标准的表空间需要先设置db_2k_cache_size等的参数：ALTER SYSTEM SET DB_4K_CACHE_SIZE=32M SCOPE=SPFILE;
然后重启数据库
#查看db_2k_cache_size等的参数：SHOW PARAMETER DB;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
#查询表空间使用情况：
SELECT UPPER(F.TABLESPACE_NAME) "TBS_NAME",
D.TOT_GROOTTE_MB "TBS_SIZE(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "TBS_USED_SIZE(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "USED_PERSENT",
F.TOTAL_BYTES "FREE_SPACE(M)",
F.MAX_BYTES "MAX_BLOCK(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1;
#查看表空间的freespace:
select tablespace_name,
count(*) as extends,
round(sum(bytes) / 1024 / 1024, 2) as MB,
sum(blocks) as blocks
from dba_free_space
group by tablespace_name;
#查询表空间的总容量：
select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_data_files
group by tablespace_name;
#查询表空间使用率：
select total.tablespace_name,
round(total.MB, 2) as Total_MB,
round(total.MB - free.MB, 2) as Used_MB,
round((1 - free.MB / total.MB) * 100, 2) || '%' as Used_Pct
from (select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_free_space
group by tablespace_name) free,
(select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_data_files
group by tablespace_name) total
where free.tablespace_name = total.tablespace_name;
#4K,用于read only的表空间:（这里没有指定read only mode,可通过ALTER TABLESPACE TBSX2RO4K READ ONLY;转换状态）
CREATE SMALLFILE TABLESPACE TBSX2NSRO4K
DATAFILE '/u02/app/oracle/oradata/orcl/TBSX2NSRO4K01.dbf' SIZE 200M REUSE
AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED
LOGGING ONLINE
PERMANENT
BLOCKSIZE 4096
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
#4K,用于insert多的表空间：
CREATE SMALLFILE TABLESPACE TBSX2NSI4K
DATAFILE '/u02/app/oracle/oradata/orcl/TBSX2NSI4K01.dbf' SIZE 1G REUSE
AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED
LOGGING ONLINE
PERMANENT
BLOCKSIZE 4096
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
#4K,用于增删改的表空间：
CREATE SMALLFILE TABLESPACE TBSX2NSN4K
DATAFILE '/u02/app/oracle/oradata/orcl/TBSX2NSN4K01.dbf' SIZE 1G REUSE
AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED
LOGGING ONLINE
PERMANENT
BLOCKSIZE 4096
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
#用于NS默认的表空间：
CREATE SMALLFILE TABLESPACE TBSX2NSDF
DATAFILE '/u02/app/oracle/oradata/orcl/TBSX2NSDF01.dbf' SIZE 1G REUSE
AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED
LOGGING ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
*创建大文件表空间：
#一个大文件表空间可以容纳32TB~128TB之间，BlockSize从8K~64K间,具有4G个Block.
#4K,用于普通增删改的大文件表空间:
#(BNS-Bigfile,NetSpider)
CREATE TABLESPACE TBSX2BNS4K01
DATAFILE '/u02/app/oracle/oradata/orcl/TBSX2BNS4K01.dbf' SIZE 4G REUSE
AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED
LOGGING ONLINE
PERMANENT
BLOCKSIZE 4096 ;
--以下可以不指定,默认即是这样的了
--EXTENT MANAGEMENT LOCAL AUTOALLOCATE
--SEGMENT SPACE MANAGEMENT AUTO;
#查看当前默认表空间种类(BIGFILE/SMALLFILE):
SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_TBS_TYPE';
*创建临时表空间：
#查看默认临时表空间：
SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE
PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
#查看临时表空间的使用情况：
SELECT * from DBA_TEMP_FREE_SPACE;
SELECT tablespace_name,tablespace_size/1024/1024,allocated_space/1024/1024,free_space/1024/1024
from DBA_TEMP_FREE_SPACE;
#创建（普通）临时表空间：
#命名规则:TBSX2(NS-NetSpider)(TMP-temp)
CREATE TEMPORARY TABLESPACE TBSX2NSTMP TEMPFILE '/u02/app/oracle/oradata/orcl/TBSX2NSTMP01.dbf'
SIZE 200M REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 50M;
--所有临时表空间都是UNIFORM扩展方式，并且扩展空间为1M，这里指定了自定义的扩展空间大小
#创建（大）临时表空间：
#一个大文件表空间可以容纳32TB~128TB之间，BlockSize从8K~32K间,具有4G个Block.
#命名规则:TBSX2(BNS-Bigfile NetSpider)(TMP-temp)
CREATE BIGFILE TEMPORARY TABLESPACE TBSX2BNSTMP01
DATAFILE '/u02/app/oracle/oradata/orcl/TBSX2BNSTMP01.dbf' SIZE 4G REUSE
AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED
LOGGING ONLINE ;
##ORA-01652:unable to extend temp segment by num in tablespace name 的检查方法:
#临时表空间不足,增加新的临时表空间文件:
alter tablespace TBSX2NSTMP add tempfile '/u02/app/oracle/oradata/orcl/TBSX2NSTMP02.dbf' size 1024M;
*创建UNDO表空间：
CREATE UNDO TABLESPACE undotbs02
DATAFILE '/u02/app/oracle/oradata/orcl/undotbs02.DBF' SIZE 2G;
#确认当前例程正在使用的UNDO表空间：
SHOW PARAMETER UNDO_TABLESPACE;
#显示数据库的所有UNDO表空间：
SELECT TABLESPACE_NAME FROM DBA_TABLESPACE WHERE CONTENTS='UNDO';
#切换UNDO表空间：
ALTER SYSTEM SET undo_tablespace=undotbs02;
*查看表所占空间大小：
Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents where segment_name like 'SEEDCONTENT%' Group By Segment_Name;
*闪回：
flashback table devep.classifiedcontent to before drop
*查看数据库所有datapump作业进度：
select owner_name,job_name,operation,state from dba_datapump_jobs;
*Data Pump,数据泵(beng4)
#创建Data Pump目录对象：
create directory nsDumpDir as '/u01/nsDumpDir';
#对目录授权：
grant read,write on directory nsDumpDir to public;
grant read,write on directory nsDumpDir to sina;
#查看Data Pump目录视图：
select * from dba_directories;
#删除目录对象：
drop directory nsDumpDir;