一.檢視表空間大小
select dbf.tablespace_name,
dbf.totalspace "總量(G)",
dbf.totalblocks as 總塊數,
dfs.freespace "剩餘總量(G)",
dfs.freeblocks "剩餘塊數",
(dfs.freespace / dbf.totalspace) * 100 "空閑比例"
from (select t.tablespace_name,
sum(t.bytes) / 1024 / 1024 / 1024 totalspace,
sum(t.blocks) totalblocks
from dba_data_files t
group by t.tablespace_name) dbf,
(select tt.tablespace_name,
sum(tt.bytes) / 1024 / 1024 / 1024 freespace,
sum(tt.blocks) freeblocks
from dba_free_space tt
group by tt.tablespace_name) dfs
where trim(dbf.tablespace_name) = trim(dfs.tablespace_name);
--檢視表空間是否自動增長
--建立臨時表空間
create temporary tablespace temp_rqrq
tempfile 'F:\oracle11g\oradata\orcl\temp_rqrq.dbf'
size 3024m
autoextend on
next 50m maxsize 5480m
extent management local;
--建立表空間
CREATE TABLESPACE rqrq
LOGGING
DATAFILE 'F:\oracle11g\oradata\orcl\rqrq.dbf' SIZE 50M
AUTOEXTEND on;
Oracle 建立表空間、使用者、授權
--建立使用者
CREATE USER rqrq IDENTIFIED BY rqrq PROFILE DEFAULT DEFAULT TABLESPACE rqrq TEMPORARY TABLESPACE temp ACCOUNT UNLOCK;
--授權建立會話的權限
grant create session to rqrq ;
--授權連接配接,操作表權限
grant connect,resource,dba to rqrq ;
--授予系統特權
GRANT CREATE USER,ALTER USER,DROP USER to rqrq ;
--授權
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 rqrq ;
--授予導入導出權限
GRANT EXP_FULL_DATABASE TO rqrq ;
GRANT IMP_FULL_DATABASE TO rqrq ;
P.S.
--删除使用者指令
drop user trunk cascade;
--删除表空間
drop tablespace ZHONGSHANGAS including contents and datafiles;
Expdp TRUNK_WATER/water DIRECTORY=dump_dir3 DUMPFILE=TRUNK_WATER0622.dmp tablespaces=TRUNK_WATER ;
Expdp scott/tiger DIRECTORY=dump_dirDUMP FILE=full.dmp FULL=Y
本文轉自楊海龍的部落格部落格51CTO部落格,原文連結http://blog.51cto.com/7218743/1437538如需轉載請自行聯系原作者
IT達仁