oracle数据库（十一）_脚本杂烩

1 ，修改字符集

set ORACLE_SID=orcl
sqlplus /nolog; 
--sqlplus / as sysdba 
--sqlplus system/z123@10.10.10.20:1521/ORCLCDB
conn / as sysdba;

--查看sid
select instance_name from v$instance;

--查看系统字符集编码
SELECT USERENV('LANGUAGE') FROM DUAL; 
--查看系统配置
SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER IN ('NLS_CHARCTERSET','NLS_NCHAR_CHARACTERSET'); 

shutdown immediate;
startup mount;

ALTER SESSION SET SQL_TRACE=TRUE;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER DATABASE OPEN;
SHOW PARAMETER JOB_QUEUE_PROCESSES;

ALTER DATABASE CHARACTER SET AL32UTF8; --ZHS16GBK
ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8; --ZHS16GBK

shutdown immediate;
startup;

-- export NLS_LANG= "SIMPLIFIED CHINESE_CHINA".AL32UTF8
-- export NLS_LANG= "SIMPLIFIED CHINESE_CHINA".ZHS16GBK
-- NLS_LANG='SIMPLIFIED CHINESE_CHINA.ZHS16GBK'
-- NLS_LANG='SIMPLIFIED CHINESE_CHINA.AL32UTF8'

2 ，数据库系统脚本

--修改日期显示格式
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';

--监听器命令
lsnrctl stop  --停止监听器命令
lsnrctl start --然后启动监听器
lsnrctl status --查看监听器命令

3 ，创建用户

--------------------
-- 创建表空间
CREATE TABLESPACE TEST_DAT
LOGGING DATAFILE '/OPT/ORACLE_DATA/TEST_DAT.DBF' 
SIZE 10240M;
--删除表空间
DROP TABLESPACE TEST_DAT INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINT;

--------------------
-- 创建目录
CREATE OR REPLACE DIRECTORY BACKUP_DIR AS '/OPT/BACKUP_DIR';
-- 删除目录
DROP DIRECTORY BACKUP_DIR;

--------------------
-- 删除用户
DROP USER DMUSER CASCADE;
-- 创建用户，指定表空间
CREATE USER DMUSER IDENTIFIED BY DMUSER ACCOUNT UNLOCK
DEFAULT TABLESPACE IAF_DAT
TEMPORARY TABLESPACE IAF_TEMP_DAT;
--授权，读写目录
GRANT CONNECT,RESOURCE,DBA TO DMUSER;
GRANT READ,WRITE ON DIRECTORY DB_MY_BAK TO DMUSER;

4，数据泵导出导入

# 查看数据泵作业状态
select * from dba_datapump_sessions;

# ----------------------------------
# 用户全表导出
expdp \'/ as sysdba\' directory=BACKUP exclude=statistics compression=ALL schemas=DWUSER dumpfile=DWUSER_0101.dmp LOGFILE=DWUSER_0101_EXP.log
expdp DWUSER/DWUSER@10.10.10.10:1521/orcl directory=BACKUP exclude=statistics compression=ALL schemas=DWUSER dumpfile=DWUSER_0101.dmp LOGFILE=DWUSER_0101_EXP.log

# 用户全表导入
impdp \'/ as sysdba\' DIRECTORY=BACKUP TABLE_EXISTS_ACTION=replace DUMPFILE=DWUSER_0101.dmp schemas=DWUSER remap_schema=DWUSER:DWUSER remap_tablespace=TBS_DWUSER_DAT:TBS_DWUSER_DAT LOGFILE=DWUSER_0101_IMP.log
impdp DWUSER/DWUSER@10.10.10.10:1521/orcl DIRECTORY=BACKUP TABLE_EXISTS_ACTION=replace DUMPFILE=DMUSER_0101.dmp schemas=DMUSER remap_schema=DMUSER:DMUSER remap_tablespace=TBS_DMUSER_DAT:TBS_DMUSER_DAT LOGFILE=DMUSER_0101_IMP.log


# ----------------------------------
# 指定表导出
nohup expdp DMUSER/DMUSER@10.10.10.10:1521/orcl directory=BACKUP exclude=statistics compression=all cluster=n dumpfile=f_tables_20211220BFD_1.dmp logfile=f_tables_20211220BFD_1.log tables=F_ICF_PVP_REDISCIN,F_ICF_PVP_DISC_BILL_LIST &

# 指定表导入
impdp DMUSER/DMUSER@10.10.10.10:1521/orcl DIRECTORY=BACKUP DUMPFILE=BI_20210902.dmp INCLUDE=TABLE:\"IN \(\'F_EVT_TRANSSETTINTEREST\',\'F_CBS_SETT_VTRANSINTERESTDT\',\'F_CBS_GL_ASSISTANT\'\)\" TABLE_EXISTS_ACTION=replace remap_schema=fdmdb:fdmdb remap_tablespace=TBS_FDMDB_DAT:TBS_FDMDB_DAT LOGFILE=expdp_full_20210902.log

5，锁表解除

SELECT b.OWNER,b.OBJECT_NAME,a.session_id,a.LOCKED_MODE FROM v$locked_object a,dba_objects b where b.OBJECT_ID = a.OBJECT_ID;

select * from v$session t1,v$locked_object t2 where t1.sid = t2.session_id;

alter system kill session '' immediate;

6，统计查询

--正在执行的存过
select   
    b.sid,b.SERIAL#,a.OBJECT, 'alter system kill session   ' || '''' || b.sid || ',' ||b.SERIAL# ||  ''';' kill_command
from SYS.V_$ACCESS a, SYS.V_$session b
where a.type = 'PROCEDURE'
    and (a.OBJECT like upper('%存储过程名%') or a.OBJECT like lower('%存储过程名%'))
    and a.sid = b.sid
    and b.status = 'ACTIVE';
alter system kill session 'sid,SERIAL#';

--查询建表语句
SELECT DBMS_LOB.SUBSTR(DBMS_METADATA.GET_DDL('TABLE','ACT_FINAL_REPORT')) AS T FROM DUAL;

--查询存过依赖的表
SELECT * FROM ALL_DEPENDENCIES T WHERE NAME IN (
    'PROC_ACT_BUSINESS_BASE'
    ,'PROC_ACT_CHECK_BUSINESS'
    ,'PROC_ACT_CHECK_VOUCHER'
    ,'PROC_ACT_DETAIL_DEPOSIT'
    ,'PROC_ACT_DETAIL_DISCOUNT'
    ,'PROC_ACT_DETAIL_EXPENSE'
    ,'PROC_ACT_IMP_FIN_BALANCE'
) AND T.REFERENCED_OWNER = 'DMUSER';

--查询源码
select * from USER_SOURCE;

--存过名称
select OBJECT_NAME from ALL_PROCEDURES WHERE OWNER = 'DMUSER' AND OBJECT_TYPE = 'PROCEDURE';

--表名称
select TABLE_NAME from ALL_TABLES WHERE OWNER = 'DMUSER';

--存过编译的错误
SELECT NAME FROM SYS.USER_ERRORS WHERE TYPE = 'PROCEDURE';

7，表空间

转：https://www.cnblogs.com/timlong/p/11075825.html

--1、查看表空间的名称及大小 
SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size 
FROM dba_tablespaces t, dba_data_files d 
WHERE t.tablespace_name = d.tablespace_name 
GROUP BY t.tablespace_name; 
--2、查看表空间物理文件的名称及大小 
SELECT tablespace_name, 
file_id, 
file_name, 
round(bytes / (1024 * 1024), 0) total_space 
FROM dba_data_files 
ORDER BY tablespace_name; 
--3、查看回滚段名称及大小 
SELECT segment_name, 
tablespace_name, 
r.status, 
(initial_extent / 1024) initialextent, 
(next_extent / 1024) nextextent, 
max_extents, 
v.curext curextent 
FROM dba_rollback_segs r, v$rollstat v 
WHERE r.segment_id = v.usn(+) 
ORDER BY segment_name; 
--4、查看控制文件 
SELECT NAME FROM v$controlfile; 
--5、查看日志文件 
SELECT MEMBER FROM v$logfile; 
--6、查看表空间的使用情况 
SELECT SUM(bytes) / (1024 * 1024) AS free_space, tablespace_name 
FROM dba_free_space 
GROUP BY tablespace_name; 
SELECT a.tablespace_name, 
a.bytes total, 
b.bytes used, 
c.bytes free, 
(b.bytes * 100) / a.bytes "% USED ", 
(c.bytes * 100) / a.bytes "% FREE " 
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c 
WHERE a.tablespace_name = b.tablespace_name 
AND a.tablespace_name = c.tablespace_name; 
--7、查看数据库库对象 
SELECT owner, object_type, status, COUNT(*) count# 
FROM all_objects 
GROUP BY owner, object_type, status; 
--8、查看数据库的版本　 
SELECT version 
FROM product_component_version 
WHERE substr(product, 1, 6) = 'Oracle'; 
--9、查看数据库的创建日期和归档方式 
SELECT created, log_mode, log_mode FROM v$database; 
--10,表空间剩余空间
select tablespace_name,sum(bytes)/1024/1024/1024||'G' AS FREE
FROM DBA_FREE_SPACE;

--创建默认表空间
create tablespace IAF_DAT
    datafile 'E:\data\appData\Oracle\orcl\IAF_DAT.DBF'
    size 6144m
    autoextend on
    next 2048m maxsize unlimited
    extent management local;


--创建临时表空间
create temporary tablespace DAT_TEMP
    tempfile 'E:\data\appData\Oracle\orcl\DAT_TEMP.DBF'
    size 2048m
    autoextend on
    next 2048m maxsize unlimited
    extent management local;


--查看表空间位置
--转 https://blog.csdn.net/weixin_35368877/article/details/116294070
select tablespace_name,
file_id,
file_name,
round(bytes / (1024 * 1024), 0) total_space
from sys.dba_data_files
order by tablespace_name;

--方法一：增大所需表空间大小：
--alter database datafile '表空间位置' resize 新的尺寸
alter database datafile '/ora_data/icsdb/system01.dbf' resize 500M;

--方法二：增加数据文件个数
--alter tablespace 表空间名称 add datafile '新的数据文件地址' size 数据文件大小
alter tablespace system add datafile '/ora_data/icsdb/system02.dbf' size 1G;
alter tablespace system add datafile '/ora_data/icsdb/system02.dbf' size 2G autoextend on next 2G maxsize unlimited;

--方法三：设置表空间自动扩展
--alter database datafile '数据文件位置' autoextend on next 自动扩展大小 maxsize 最大扩展大小
alter database datafile '/ora_data/icsdb/system02.dbf' autoextend on next 500m maxsize 10000m;