创建表空间

一.查看表空间大小

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达仁