oralce问题

死锁，如果较多使用存储过程杀死

create or replace procedure killer is
    v_obj varchar2(200);
    v_sql varchar2(500);
 --   v_session varchar2(30);
    cursor kill_lockedobj_crs is(
        select t2.sid,t2.serial# serial from v$locked_object
        t1,v$session t2 where t1.session_id=t2.sid 
    );
    
begin
    for cont in kill_lockedobj_crs loop
        exit when kill_lockedobj_crs%notfound;
        v_obj := cont.sid ||','||cont.serial;
        v_sql := 'alter system kill session' || v_obj;
        execute immediate v_sql;
    end loop;
end;

查询被锁的对象
select t2.username, t2.sid, t2.serial#, t2.logon_time
from v$locked_object t1, v$session t2
where t1.session_id = t2.sid
order by t2.logon_time;

-同一个sid可能会被不同的session使用，所以通过sid和serial number可以唯一定位；

alter system kill session 'sid,serial#';

12c之前的客户端连接问题
SQLNET.ALLOWED_LOGON_VERSION=8

ora-00845
mount -t tmpfs shmfs -o size=7g /dev/shm
echo "shmfs /dev/shm tmpfs size=7g 0" >> /etc/fstab

 

 

容器数据库

查询表空间是否自动扩展
select file_id,file_name,autoextensible from dba_data_files where tablespace_name like 'SUNYARD' order by 1;

删除表空间同时删除对应的数据文件
drop tablespace db  including contents and datafiles;
开启数据库
alter PLUGGABLE DATABASE db1 open;
ALTER PLUGGABLE DATABASE ALL OPEN 开启所有PDB

 

根据pdbseed创建pdb

在oracle的dba_data_files创建可插拔数据库表空间文件夹
#select con_id,name,open_mode from v$containers;
#select * from dba_data_files;

CREATE PLUGGABLE DATABASE db2
ADMIN USER root IDENTIFIED BY root ROLES=(CONNECT)
FILE_NAME_CONVERT=('/opt/oracle/oradata/ORCLCDB/pdbseed','/opt/oracle/oradata/ORCLCDB/db2');
删除PDB
drop pluggable database db2 keep datafiles; 保留数据文件。
drop pluggable database db2 including datafiles; 彻底删除PDB的数据文件。

数据文件查询对应表空间
select t1.name,t2.name from v$tablespace t1,v$datafile t2 where t1.ts#=t2.ts#;

查询表空间剩余
select
b.file_id 文件ID号,
b.tablespace_name 表空间名,
b.bytes/1024/1024||'M'字节数,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024||'M' 已使用,
sum(nvl(a.bytes,0))/1024/1024||'M' 剩余空间,
100 - sum(nvl(a.bytes,0))/(b.bytes)*100 占用百分比
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_id,b.bytes
order by b.file_id;
安装依赖包
yum clean all
yum makecache
yum install -y binutils compat-libstdc++-33 elfutils-libelf elfutils-libelf-devel gcc gcc-c++ glibc glibc-common glibc-devel libaio libaio-devel libgcc libstdc++ libstdc++-devel make sysstat ld-linux.so.2 ksh

查询em端口
select dbms_xdb_config.gethttpsport() from dual;