Oracle 常用命令

逃离我推掉我的手 2022-08-21 08:44 188阅读 0赞

\------------------生产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