oracle 实例管理常用脚本

oracle 实例管理常用脚本

select * from v$version; --查看数据库版本

drop user SJZX cascade --删除用户及用户数据

select * from v$session t where t.USERNAME = ‘用户名’；–查询指定用户的连接

alter system kill session ‘49,61198’;–杀死连接的用户

drop tablespace sjzx including contents and datafiles;–删除表空间及表空间下的数据文件

select * from dba_directories;

select * from dba_data_files;–查看表空间文件

–创建表空间
CREATE TABLESPACE “PDWDSJ”
LOGGING
DATAFILE ‘/u02/app/oracle/oradata/orcl/PDWDSJ.ORA’ SIZE 1024m
AUTOEXTEND
ON NEXT 512m MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

–添加表空间数据文件
alter tablespace PDWDSJ add datafile ‘/u02/app/oracle/oradata/orcl/PDWDSJ1.ORA’ size 1024m
AUTOEXTEND ON NEXT 512M;

alter tablespace PDWDSJ add datafile ‘/u02/app/oracle/oradata/orcl/PDWDSJ2.ORA’ size 1024m
AUTOEXTEND ON NEXT 512M;

alter tablespace PDWDSJ add datafile ‘/u02/app/oracle/oradata/orcl/PDWDSJ3.ORA’ size 1024m
AUTOEXTEND ON NEXT 512M;

alter tablespace PDWDSJ add datafile ‘/u02/app/oracle/oradata/orcl/PDWDSJ4.ORA’ size 1024m
AUTOEXTEND ON NEXT 512M;

–创建用户
create user pwjk_model identified by sys default tablespace “PDWDSJ”;

–配置oracle12.2支持低版本 ojdbc jar包访问
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8

–修改用户密码
ALTER USER pwjk_model IDENTIFIED BY sys;

–解锁用户
alter user pwjk_model account unlock;

–停止oracle
shutdown immediate
lsnrctl stop

–开启oracle
lsnrctl start
startup

–查看监听器状态
lsnrctl status

参考：https://www.cnblogs.com/jackliu2013/p/8298287.html

–授予用户dba权限
grant DBA to sjzx with admin option;
grant DBA to PWJK_MODEL with admin option;
grant execute on dbms_lock to PWJK_MODEL;
–授予用户创建dblink的权限
grant CREATE PUBLIC DATABASE LINK,DROP PUBLIC DATABASE LINK to pwjk_model;

–查看用户的dblink
select * from ALL_DB_LINKS;

–删除dblink
drop /* public */ database link SYYJY;

–查看用户是否有dblink的权限
select * from user_sys_privs where privilege like upper(’%DATABASE LINK%’);

–创建 dblink
create database link sc_new
connect to SJZX identified by sys
using ‘(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.43.99)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))’;

–设置环境变量
cd /data/cdata/app/oracle/product/11.2.0/db_1/bin
export ORACLE_HOME=/data/cdata/app/oracle/product/11.2.0/db_1
export PATH=$PATH:$ORACLE_HOME/bin
echo $PATH

–dmp导入
impdp pwjk_model/sys@orcl directory=DATA_PUMP_DIR schemas=pwjk_model dumpfile=pwjk_model.dmp logfile=pwjk_model_imp.log

–指定表导入
impdp pwjk_model/sys@192.168.43.99:1521/orcl directory=DATA_PUMP_DIR dumpfile=pwjk_model.dmp logfile=pwjk_model.log
tables=pwjk_model.tg_day_curve_load,
pwjk_model.tg_day_curve_load_no_nt,
pwjk_model.tg_day_curve_load_yes_nt,
pwjk_model.e_mp_power_curve
table_exists_action=replace

–dmp导出
expdp xstjfx/xstjfx@orcl directory=DATA_PUMP_DIR schemas=xstjfx dumpfile=xstjfx1128.dmp logfile=xstjfx1128.log

SELECT a.tablespace_name “表空间名”,

   total/1024/1024  "表空间大小单位M",

   free/1024/1024 "表空间剩余大小单位M",

   (total - free)/1024/1024 "表空间使用大小单位M",

   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;