Oracle基础知识

1.查看数据库当前版本

SQL> col product format a38
SQL> col version format a10
SQL> col version_full format a12
SQL> col status format a12
SQL> select * from product_component_version;
PRODUCT                                VERSION    VERSION_FULL STATUS
-------------------------------------- ---------- ------------ ------------
Oracle Database 19c Enterprise Edition 19.0.0.0.0 19.3.0.0.0   Production

参考：Oracle® Database 19c Database Administrator’s Guide P93

2. 在SQLPLUS引用外部变量

[oracle@rac1 ~]$ fm=400
[oracle@rac1 ~]$ result=`sqlplus -s / as sysdba <<EOF
SHOW PARAMETER PGA_AGGREGATE_TARGET;
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=${fm}M SCOPE=BOTH;
SHOW PARAMETER PGA_AGGREGATE_TARGET;
EOF`
[oracle@rac1 ~]$ echo "$result"

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 300M

System altered.


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 400M

查看V$视图输出：

OPENSQL="select open_mode from v\$database;"
OPENMODE=`sqlplus -s '/ as sysdba' <<EOF
set pagesize 999 linesize 999 heading off feedback off
$OPENSQL
exit
EOF`

echo $OPENMODE
READ WRITE

3. 检查待处理的事务，查看关闭数据库时回滚进度

set lines 120
col useg format a30
alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';

select sysdate,b.name useg, b.inst# instid, b.status$ status, a.ktuxeusn
xid_usn, a.ktuxeslt xid_slot, a.ktuxesqn xid_seq, a.ktuxesiz undoblocks
from x$ktuxe a, undo$ b
where a.ktuxesta = 'ACTIVE'  and a.ktuxecfl like '%DEAD%'
and a.ktuxeusn = b.us#;