一名数据库爱好者的专栏

参考 https://blog.youkuaiyun.com/weixin_50464560/article/details/119277677。

oracle 用户含有特殊字符，使用 sqlplus时这样登录。

Database 19 Release Updates and Revisions Bugs Fixed Lists (文档 ID 2523220.1)Oracle Database 19c Proactive Patch Information (文档 ID 2521164.1)Requirements for Installing Oracle Database 19c on OL7 or RHEL7 64-bit (x86-64) (文档 ID 2551169.1)Requirements fo

SQL> create user pyb1 identified by pyb1pyb1;grant connect,resource to pyb1;使用 pyb1 登录数据库SQL> select * from dba_tables;select * from dba_tables *ERROR at line 1:ORA-00942: table or view does not exist授权(即时生效)grant select on

SQL> create user pyb1 identified by pyb1pyb1;grant connect,resource to pyb1;使用 pyb1 登录系统SQL> select * from v$mystat;select * from v$mystat *ERROR at line 1:ORA-00942: table or view does not existSQL> select * from v$sessi

./runcluvfy.sh stage -pre crsinst -n node1,node2 -verboseVerifying RPM Package Manager database ...INFORMATIONPRVG-11250 : The check "RPM Package Manager database" was not performed becauseit needs 'root' user privileges.查看 mos 后，发现要添加 -method roo

解决办法如下yum install gpm*;systemctl restart gpm;systemctl enable gpm;

db: oracle 11.2.0.4SQL> create sequence seq_1;查询数据字典，max_value 显示为 1E28(科学计数法，表示10的28次方，相当于1后面29个0 )，够大。查看oracle 的文档显示如下SQL> create sequence seq_1;SQL> create sequence seq_1start with 9999999999999999999999999970;SQL> select s

# su - oracle$ vi /tmp/test.sqlinsert into tmp_t0(c0) values('http://abc?&a="s"');执行脚本需要输入变量a的值$ sqlplus / as sysdbaSQL> @/tmp/test.sqlEnter value for a: 查看文档后说是要开头设置 set define off;$ vi test.sqlset define off;insert into tmp_t0(c0)va

SQL> set lines 200;set pages 200;SQL> select * from v$version;BANNER CON_ID-------------------------------------------------------------------------------- ----------Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64b

使用 tom 大叔的 print_tablecreate or replace procedure print_table( p_query in varchar2 )AUTHID CURRENT_USERis l_theCursor integer default dbms_sql.open_cursor; l_columnValue varchar2(4000); l_status integer; l_descTbl dbms_sql.desc_tab; l_colCnt number

动态监听$ vi listener.ora LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oradb-node1)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) )静态监听添加一个静态监听$ vi listener.ora SID_LIST_LIS

os: centos 7.4db: oracle 11.2.0.4在给表加中文注释时会碰到一些莫名其妙的提示，比如 ORA-01756: quoted string not properly terminated版本# cat /etc/centos-releaseCentOS Linux release 7.4.1708 (Core) # # su - oracleLast lo...

os: centos 7.4db: oracle 11.2.0.4create tableSQL> create table tmp_t0(c0 varchar2(100),c1 varchar2(100),c2 varchar2(100),c3 date );shell 并发启动 100个进程$ vi insert_date.sh #!/bin/bashfor((i=1...

os: centos 7.4db: oracle 11.2.0.4dbms_output 对于程序调试非常重要，经常需要输出执行的SQL语句或者变量sqlplus 的 serveroutput 变量默认 sqlplus 的 serveroutput 是关闭的，无法输出SQL> show serveroutput;serveroutput OFFSQL> begin ...

准备表、索引drop table tmp_t0 purge;create table tmp_t0 ( c0 varchar2(50), c1 varchar2(20), c2 varchar2(20), c3 date) -- pctfree 20 initrans 32partition by hash(c0)partitions 64tablespace us...

os: centos 7.4db: oracle 11.2.0.4查看 v$sql_monitorselect (sysdate-v.SQL_EXEC_START) * 3600*24 as exec_sec, v.sql_id, v.sql_text, v.binds_xml from v$sql_monitor v where 1=1 o...

os: centos 7.4db: oracle 18c(18.3)使用 dbca 建库时创建的是CDB，顺带创建了一个 pdbpeiyb，现在再创建如下几个pdb。pdbshenzpdbguangzpdbdongg使用了几种方式创建pdb，大同小异。使用 dbca 创建 pdbshenz# xhost +# su - oracle$ export DISPLAY=:0.0$...

os: centos 7.6db: oracle 19.3rac 环境,运行 pgsql 匿名块,一段时间后报错.ORA-04021SQL> ERROR at line 1:ORA-04021: timeout occurred while waiting to lock objectpeiyongbin.tmp_t0ORA-06512: at line 15ORA-065...

单实例自动启动#start oraclesu - oracle -c "/data/u01/oracle/app/oracle/product/12.1.0/dbhome_1/bin/lsnrctl start"su - oracle -c "/data/u01/oracle/app/oracle/product/12.1.0/dbhome_1/bin/sqlplus -S / as sy...

os: centos 7.6db: oracle 19.3开发环境做压测时报错 WARNING: too many parse errors,简单分析了下.错误2019-07-30T16:35:15.930590+08:00PDB1(3):WARNING: too many parse errors, count=49360 SQL hash=0xa4a6bcc4PDB1(3):PAR...

os: centos 7.6db: oracle 19.3压测应用报错错误2019-07-31 12:11:59.860 [appnode1] ERROR BaseControllerorg.springframework.jdbc.UncategorizedSQLException:### Error querying database. Cause: java.sql.SQLEx...

补丁作为软件的bug修复,非常重要.oracle 数据库软件也是有很完善的补丁体系.RELEASE标准产品基础发行版,比如 11.2.0.1PSRPatch Set Release(PSR)针对发行版中BUG进行修复的补丁.每隔一定时期,会将所有补丁集成到软件中,经集成测试后,进行发布.PSR的发行周期是按照发行版本来的,一般都需要一年的时间.每个PSR修改5位版本号的第4位,比如 ...

os: centos 7.6db: oracle 19.3打补丁离不开 opatch 下载地址 https://updates.oracle.com/download/6880880.html1 - RAC Patching methodsOPatch supports 3 different patch methods on a RAC environment:Patching R...

os: centos 7.6db: oracle 19.3目前在官网下载19c时,详细的版本是 19.3,在 mos 上查看补丁已经有19.4 了,所以升级下 oracle 19.3 rac 到 19.4 (This patch is not Oracle RAC Rolling installable.)ip规划如下#################################19...

drop logfile groupalter database drop logfile group 1;alter database drop logfile group 2;alter database drop logfile group 3;alter database drop logfile group 4;drop logfile group memberadd...

oracle standby redo 不建议复用多member。会造成io竞争，并且毫无意义。drop standby logfile groupalter database drop standby logfile group 1;alter database drop standby logfile group 2;alter database drop standby logfil...

os: centos 7.6db: oracle 19c早上一哥们在说 oracle date 类型无法插入 3000年,自己也不敢确定到底可不可以，做了个实验。事实还是挺打脸的.# su - oracle$ sqlplus / as sysdba;SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 11 16:43:25 201...

os: centos 7.4db: oracle 11.2.0.4默认登录 sqlplus 后，显示的实在是太老土了，而且又不清楚。# su - oracle$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Tue Oct 8 16:25:30 2019Copyright (c) 1982, 2013,...

舍入SQL> select round(11/3,2) from dual;ROUND(11/3,2)------------- 3.67取整SQL> select trunc(11/3) from dual;TRUNC(11/3)----------- 3取余SQL> select mod(11,3) from dual; MOD(1...

最近在准备压测数据时需要从客户表中随机获取一些客户,通常有如下方式:使用 sampleselect * from ( select * from cus sample(10) order by dbms_random.value() ) t0 where 1=1 and rownum <= n; 或者select * ...

os: centos 7.6db: oracle 19.3oracle 杀掉会话要使用 alter system kill session,不要使用 kill 命令.select 'alter system kill session '''||s.sid||', '||s.serial#||''';' as standalone_kill_session ,'alter sys...

os: centos 7.4db: oracle 12.1.0.2permanent smallfile tablespaceselect 'alter database datafile '''||dt.file_name||''' autoextend on next 1g maxsize 30g; ', dt.*, dts.*from dba_data_...

os: centos 7.4db: oracle 12.1.0.2A bigfile tablespace contains only one data file or temp file, which can contain up to approximately 4 billion (2的32次方) blocks. The minimum size of the single data f...

os: centos 7.4db: oracle 12.1.0.2dbms_rowid 的 package测试表准备SQL&amp;gt; create table tmp_t0(c0 varchar2(100),c1 varchar2(100));Table created.SQL&amp;gt; insert into tmp_t0 select '1','11' from dual;1...

os: centos 7.4db: oracle 12.1.0.2alter system flush生产环境上慎用,有可能执行后,服务器短时间内的cpu,io会遇到瓶颈.SQL&amp;gt; alter system flush shared_buffer;alter system flush shared_buffer*ERROR at line 1:ORA-02000: missi...

os: centos 7.4db: oracle 18c(18.3)source_file_name_convert 看官方文档，描述的不清不楚的。下面以一个具体的例子说明下。create pdb$ sqlplus / as sysdba;SQL&amp;amp;amp;gt; CREATE PLUGGABLE DATABASE pdbhuiz ADMIN USER pdbadminhuiz IDENTIF...

os: centos 7.4db: oracle 18c(18.3)pdb 的优势之一就是 unplug、plug，本篇blog介绍下 pdb 的 unplug。以 pdbdongg 为例，进行 unplug 操作。unplug pdb必须先 close，然后再 unplug。需要再 cdb$root 下操作$ sqlplus / as sysdba;SQL&amp;amp;gt; show c...

os: centos 7.4db: oracle 18c(18.3)有了cdb，pdb之后，也多了一些视图，下面简单分析下 dba_users cdb_users 这两个数据字典视图，其它类似的视图也是一样的情况。dba_users查看下dba_users的定义，下面贴了些重点代码。 from sys.user$ u left outer join sys.r...

os: centos 7.4db: oracle 18c(18.3)12c开始引入了cdb，pdb，用户也自然有了 common user 和 local user ，怎么理解这两类用户了？common user在CDB中创建的以C##(或者c##)开头用户就是 common user，创建的 common user 会传递到每一个 container。local user在PDB中创...