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

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;