Oracle 常用命令

------------------生产oracle 数据导出、导入dmp----------------

sqlplus /as sysdba --进入系统

alter user system identified by system;

createdirectory exp_shengchan as '/home/oracle/'

grantread,write on directory exp_shengchan to system;

操作系统下执行
导出：
expdp userid=system/system directory=exp_shengchan schemas=shkcdumpfile=expdp_shkc01.dmp logfile=expdp_mcrmuser_shkc01.log
导入：
impdp userid=system/system directory=exp_shengchan schemas=shkcdumpfile=expdp_shkc01.dmp logfile=expdp_mcrmuser_shkc01.log

---------------用户表空间创建--------------------------------------

如果导入的库没用 shkc用户和表空间SHKC_DATA就会出现错误，需要现在目标库里面创建用户和表空间
sqlplus / as sysdba
创建表空间
CREATE TABLESPACE SHKC_DATA 
DATAFILE '/u01/app/oracle/oradata/shkorg/tbs_shkc_data_01.dbf size 800M 
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; 
创建用户：
create user shkc identified by shkc 
default tablespace SHKC_DATA;
授权限：
grant create any table to shkc; 
grant delete any table to shkc; 
grant insert any table to shkc; 
grant select any table to shkc; 
grant unlimited tablespace to shkc; 
grant execute any procedure to shkc; 
grant update any table to shkc; 
grant create any view to shkc;
grant create session to shkc;

--------------命令--------------
查看表空间：
SELECT file_name, tablespace_name FROM dba_data_files;
删除表空间：
drop tablespace SHKC_DATA including contents and datafiles

查看表空间剩余大小
select a.tablespace_name,a.bytes/1024/1024 "SumMB",(a.bytes-b.bytes)/1024/1024 "used MB",b.bytes/1024/1024"free MB",round(((a.bytes-b.bytes)/a.bytes)*100,2)"percent_used"
from 
(select tablespace_name,sum(bytes) bytes from dba_data_files group bytablespace_name) a,
(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_spacegroup by tablespace_name) b
where a.tablespace_name=b.tablespace_name
order by ((a.bytes-b.bytes)/a.bytes) desc