Oracle常用操作笔记

select name from v$datafile;
create dataspace tablespace datafile '/oracle/oradata/orcl/data01.dbf' size 1024m,'/oracle/oradata/orcl/data02.dbf' size 1024m autoextend on next 100m maxsize unlimited;
alter tablespace dataspace add datafile '/oracle/oradata/orcl/data03.dbf' size 1024m autoextend on next 100m maxsize unlimited;
alter database datafile '/oracle/oradata/orcl/data01.dbf' size 2048m autoextend on next 200m maxsize unlimited;
===========================================================
create user username identified by password default tablespace dataspace;
grant connect,resource to username;
drop user username cascade;
===========================================================
select a.tablespace_name "表空间名",
       total "表空间大小",
       free "表空间剩余大小",
       (total - free) "表空间使用大小",
       round((total - free) / total, 4) * 100 "使用率 %"
  from (select tablespace_name, sum(bytes) free
          from dba_free_space
         group by tablespace_name) a,
       (select tablespace_name, sum(bytes) total
          from dba_data_files
         group by tablespace_name) b
 where a.tablespace_name = b.tablespace_name;
===========================================================
exp username/password file=expdata.dmp log=expdata.log buffer=409600
imp username/password file=impdata.dmp log=impdata.log buffer=409600 full=y
===========================================================
create directory dump_dir as 'd:\bakdata';
select * from dba_directories;
grant read, write on directory dump_dir to username;
expdp username/password directory=dump_dir dumpfile=expdata.dmp logfi