Oracle 常用脚本

ceate by nohi 20201230

锁

-- 锁       
SELECT /*+ rule */ s.username, decode(l.type,'TM','TABLE LOCK','TX','ROW LOCK',
NULL) LOCK_LEVEL, o.owner,o.object_name,o.object_type,
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser
FROM v$session s,v$lock l,dba_objects o
WHERE l.sid = s.sid
AND l.id1 = o.object_id(+)
and l.type in ('TM','TX')
AND s.username is NOT NULL
and s.username in ('','CURVAPP','');

-- 杀进程
ALTER SYSTEM KILL SESSION '619, 14479';

SELECT 'ALTER SYSTEM KILL SESSION '''  || s.sid || ',' || s.serial# || ''';'
FROM v$session s,v$lock l,dba_objects o
WHERE l.sid = s.sid
AND l.id1 = o.object_id(+)
AND s.username is NOT NULL
and s.username in ('','CURVAPP','')
and l.type in ('TM','TX');

表空间

-- 修改表所属表空间：alter table 表名 move tablespace "表空间名称"
-- 修改表索引所属表空间：alter index 索引名 rebuild tablespace "表空间名称"

-- 查询某表空间的所有表：
select segment_name from dba_segments where owner='表空间名称' and segment_type='TABLE' group by segment_name

-- 查询用户占用空间大小
select owner,tablespace_name,round(sum(bytes)/1024/1024,2) "USED (M)" from dba_segments 
group by owner,tablespace_name
order by sum(bytes)desc;

-- 临时表空间   
select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;

-- 临时表空间
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",
NVL(FREE_SPACE,0) "FREE_SPACE(M)"
FROM
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,
ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE  D.TABLESPACE_NAME = F.TABLESPACE_NAME(+);

alter database tempfile '+BJXG_CS_CBPC_DATA/APPDB/TEMPFILE/temp.370.1021910773' resize 30G;

表数据清理

查询表占用空间大小

select owner,tablespace_name,round(sum(bytes)/1024/1024,2) "USED (M)" from dba_segments 
group by owner,tablespace_name
order by sum(bytes)desc;

SYS_LOB 占用大小

select object_name,status from dba_objects where object_id='xxxxxxxxxx';
## object_id 这里写LOBSEGMENT名字里SYS_LOB后的10位数字。

SELECT owner, table_name, column_name
FROM dba_lobs
WHERE segment_name = 'SYS_LOB<<identifier>>$$';

清理
- truncate table xxxxx;
- 清空一下回收站 purge table xxxx;
- 清空一下回收站 purge recyclebin;

创建表空间

-- 查询表空间路径
select * from dba_data_files; 
-- 创建表空间
create tablespace jcms
datafile '/opt/oracle/app/oradata/orcl/jcms.dbf'
size 10M reuse autoextend on;
-- 创建临时表空间
create tablespace ocrm
datafile '/home/oracle/app/oracle/oradata/orcl/ocrm.dbf'
size 10M reuse autoextend on;

用户

drop user xetl cascade;
DROP TABLESPACE newoa INCLUDING CONTENTS AND DATAFILES;
drop table CHANNELCODE cascade constraints;

-- 12c 数据 切换seesion
select * from V$ACCESS;
select name,cdb from v$database;
select pdb_id,pdb_name,dbid,status,creation_scn from dba_pdbs;
select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

alter session set container=ORCLPDB1;
alter session set container = cdb$root;

alter pluggable database ORCLPDB1 open;

-- 创建用户
 create user jcms
 identified by jcms
 default tablespace jcms
 account unlock;
-- 赋权 
 grant create session to xetl;
 grant create table to xetl;
 grant create sequence to xetl;
 grant create procedure to xetl;
 grant create VIEW to xetl;
 grant resource to xetl;
 
alter user pi_valuation account  unlock;
revoke dba from cap;
grant dba to jcms;

导入导出

exp、imp
- exp userid=sap/sap@orcl_sit owner=sap file=sap_sit_20121206.dmp log=sap.log
- exp userid=sap/sap@orcl_sit owner=sap file=sap_sit_20121206.dmp log=sap.log tables=T_USER,T_ROLE
- imp newoa1203/newoa1203@nohi file=sap_sit_20121206.dmp fromuser=sap touser=sap_uat
- imp newoa1203/newoa1203@nohi file=sap_sit_20121206.dmp fromuser=sap touser=sap_uat tables=T_USER,T_ROLE
- 增加buffer imp esb_tmp/esb_tmp file=sap_sit_20121206 log=imp tables=T_USER ignore=yes buffer=102400000 commit=yes feedback=1000
- 导出表结构 exp ocrm/ocrm@43_227 file=ocrm_t.dmp owner=(ocrm) rows=n
数据泵方式
- 创建目录
```
select * from dba_directories;
 -- 创建目录
create or replace directory data_dump_dir as '/home/oracle/exp/';
```
- expdp nohi/nohi@orcl schemas=riskDIRECTORY=data_dump_dir dumpfile=EXPDP_20171208.dmp LOGFILE=expdb.log version='10.2.0'
  - version 指定导入版本，向下兼容
- impdp nohi/nohi@orcl DIRECTORY=data_dump_dir REMAP_SCHEMA=UserA:UserB remap_tablespace=TbsA:TbsB DUMPFILE=EXPDP_20171208.dmp LOGFILE =impdb.log version='10.2.0'

常用语句



select d.dbid            dbid
     , d.name            db_name
     , i.instance_number inst_num
     , i.instance_name   inst_name
  from v$database d,
       v$instance i;
    

-- 表分析
analyze table tableName compute statistics;
analyze index indexname compute statistics;



--- # 查看1小时内执行的sql语句，并按照执行时间倒序排序
select s.LAST_ACTIVE_TIME,s.SQL_TEXT,s.SQL_FULLTEXT,s.FIRST_LOAD_TIME,s.LAST_LOAD_TIME,s.EXECUTIONS from v$sql s
where  s.LAST_ACTIVE_TIME>sysdate-1/24
 and s.PARSING_SCHEMA_NAME in ('CURVAPP', 'BASEAPP')
order by s.LAST_ACTIVE_TIME desc;
-- 超时时间
-- 其中IDLE_TIME的值就是设置的空闲超时时间
select * from dba_profiles t where t.resource_name='CONNECT_TIME';
select * from user_resource_limits;
select resource_name,resource_type,limit from dba_profiles where profile='DEFAULT' ;
ALTER PROFILE DEFAULT LIMIT IDLE_TIME UNLIMITED;

select * from dba_profiles where profile='DEFAULT' ;

-- 临时表空间
create temporary tablespace temp tempfile '/opt/oracle/oradata/conner/temp.dbf' size 200M autoextend off;
alter database default temporary tablespace temp;
drop tablespace temp2; //drop tablespace temp including contents and datafiles cascade constraints --彻底删除包括操作系统中的临时表空间的数据文件
-- 表记录数
select * from user_tables where num_rows > 100000;
select num_rows * avg_row_len , table_name , num_rows from user_tables where num_rows > 10000 order by num_rows desc;
  
-- 查询表占用大小
select segment_name,bytes , bytes/1024/1024 M from user_segments where segment_type = 'TABLE' and segment_name not like 'BIN%' order by bytes desc
  
select 'create sequence '||sequence_name||
   ' minvalue '||min_value||
   ' maxvalue '||max_value||
   ' start with '||last_number||
   ' increment by '||increment_by||
   (case when cache_size=0 then ' nocache' else ' cache '||cache_size end) ||';'
from user_sequences
--    
select * from dba_users;
select * from user_tables;
select * from dba_data_files;

# Oracle 常用脚本

# 锁

# 表空间

# 表数据清理

# 创建表空间

# 用户

# 导入导出

# 常用语句

Oracle 常用脚本

锁

表空间

表数据清理

创建表空间

用户

导入导出

常用语句