Oracle部署常用命令/脚本（运维版）

windows

备份

本地完全备份

#本地全库备份
exp system/123456 file=E:\xiangmu\beifen\test.dmp full=y

导出某个用户

#windowsshell需要加英文双引号 
exp test/123456 file=E:\BackupDatabase\test.dmp owner=test log=E:\BackupDatabase\test.log

本地同用户导入，登录账号和用户相同

imp test/123456  file=E:\xiangmu\beifen\test.dmp log=E:\xiangmu\beifen\test.log full=y

本地不同用户导入，登录账号和用户相同

imp system/test@orcl fromuser=test touser=test_new file=E:\xiangmu\beifen\test.dmp log=E:\xiangmu\beifen\test.log;

简单备份脚本

@echo off

::set curdate=%date:~5,2%
set backuppath=D:\app\backup\exp
md %backuppath%

FOR /F "TOKENS=1,2,3 DELIMS=/ " %%i IN ('DATE /T') DO SET d=%%i-%%j-%%k
FOR /F "TOKENS=1,2,3 DELIMS=: " %%i IN ('TIME /T') DO SET t=%%i%%j%%k

SET curdate=%d%_%t%.sql
exp userid=system/123456 file=%backuppath%\%curdate%.dmp log=%backuppath%\%curdate%.log 
      
forfiles /p "D:\app\buckup\exp" /s /m *.dmp /d -7 /c "cmd /c del @file"

pause

关闭

正常关闭

	shutdown normal

立即关闭

	shutdown immediate

事务关闭

	shutdown transactional

暴力关闭

shutdown abort

Sql

查看哪些用户拥有SYSDBA、SYSOPER权限:

select * from V_$PWFILE_USERS;

增加数据库用户：(test11/test)

create user test11 identified by test default tablespace users Temporary TABLESPACE Temp;

用户授权:

grant connect,resource,dba to test11;

grant sysdba to test11;

commit;

更改数据库用户的密码：(将sys与system的密码改为test.)

alter user sys indentified by test;

附赠个人用的用户创建模板

--创建数据库 
CREATE TABLESPACE kt_dtest_bz LOGGING DATAFILE 'E:\app\Administrator\virtual\oradata\orcl\kt_dtest_bz.dbf' 
SIZE 200M 
AUTOEXTEND ON NEXT 100M 
--MAXSIZE 500M EXTENT MANAGEMENT LOCAL;
permanent online; 
--创建临时库 
create temporary tablespace kt_dtest_bz_temp tempfile 'E:\app\Administrator\virtual\oradata\orcl\kt_dtest_bz_temp.dbf' 
size 100m 
autoextend on next 50m 
--maxsize 500m extent management local;
EXTENT MANAGEMENT LOCAL; 
--设置账号密码并绑定两库 
CREATE USER kt_dtest_bz 
IDENTIFIED BY 123456 
DEFAULT TABLESPACE kt_dtest_bz TEMPORARY TABLESPACE kt_dtest_bz_temp; 
--给用户添加权限 
grant connect,resource,dba to kt_dtest_bz; 
grant create session to kt_dtest_bz;



select username,default_tablespace from kt_dtest_bz
--1、删除用户 
drop user kt_dtest_bz cascade; 
--2、删除表空间 
drop tablespace kt_dtest_bz including contents and datafiles; 
drop tablespace kt_dtest_bz_temp including contents and datafiles; 
--3、删除*.dbf文件。（*.bdf路径）