创建表空间

一.查看表空间大小

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 ;

 

imp  rqrq/rqrq@orcl fromuser=rqrq touser=rqrq  ignore=y file=e:\TRUNK_WATER0620.dmplog=e:\TRUNK_WATER0620.log

impdp rqrq/rqrq@orcl directory=DATA_PUMP_DIR  dumpfile=huagang_20130401.dmp  logfile=rqrq.log

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 ;

exp TRUNK_WATER/water@ORCL   file=J:TRUNK_WATER0627.dmp  log=TRUNK_WATER0627.log  tablespaces=TRUNK_WATER ; full=y ;

 Expdp scott/tiger DIRECTORY=dump_dirDUMP FILE=full.dmp FULL=Y

 expdp TRUNK_WATER/water@ORCL DIRECTORY=dump_dir3   DUMPFILE=TRUNK_WATER0620.dmp VERSION=10.2.0.1.0;

 expdp TRUNK_WATER/water@ORCL DIRECTORY=dump_dir3   DUMPFILE=TRUNK_WATER0620.dmp  VERSION=10.2.0.1.0 ;