本文包括软件版本及平台信息、查看数据库的创建日期、获取数据库名和SID、数据库已安装的组件、数据库启动时间、查看ORACLE 的隐含参数、查看当前SQLPLUS 用户的sid 和serial#
软件版本及平台信息
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL>
SQL> set linesize 180
SQL> col platform_name format a30
SQL> select * from dba_registry_database;
PLATFORM_ID PLATFORM_NAME EDITION
----------- ------------------------------ ------------------------------
13 Linux x86 64-bit
SQL>
SQL> select dbid, name, open_mode, database_role, platform_name from v$database;
DBID NAME OPEN_MODE DATABASE_ROLE PLATFORM_NAME
---------- --------- ---------- ---------------- ------------------------------
2839985009 ZYDEV READ WRITE PRIMARY Linux x86 64-bit
SQL>
SQL> select dbms_utility.port_string from dual;
PORT_STRING
-----------------------------------------------
x86_64/Linux 2.4.xx
SQL>
set serveroutput on
declare
ver VARCHAR2(100);
compat VARCHAR2(100);
begin
dbms_utility.db_version(ver, compat);
dbms_output.put_line('Version: ' || ver ||' Compatible: ' || compat);
end;
/
Version: 10.2.0.4.0 Compatible: 10.2.0.3.0
PL/SQL procedure successfully completed.
查看数据库的创建日期
SQL> select created ,log_mode from v$database;
SQL> select to_char(created,'yyyy-mm-dd hh:mi:ss'),log_mode from v$database;
TO_CHAR(CREATED,'YY LOG_MODE
------------------- ------------
2008-12-25 12:05:25 ARCHIVELOG
SQL>
获取数据库名和SID
SQL> select name from v$database;
NAME
---------
AS4PRO1
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
orcl
SQL> show parameter db_name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string as4pro1
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string orcl
parameter instance单实例与RAC的显示
单实例
======
SQL> show parameter instance
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
active_instance_count integer
cluster_database_instances integer 1
instance_groups string
instance_name string rcv
instance_number integer 0
open_links_per_instance integer 4
parallel_instance_group string
parallel_server_instances integer 1
RAC
====
SQL> show parameter instance
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
active_instance_count integer
cluster_database_instances integer 2
instance_groups string
instance_name string bosdb1
instance_number integer 1
open_links_per_instance integer 4
parallel_instance_group string
parallel_server_instances integer 2
SQL>
set serveroutput on
declare
inst_tab dbms_utility.instance_table;
inst_cnt NUMBER;
begin
if dbms_utility.is_cluster_database then
dbms_utility.active_instances(inst_tab, inst_cnt);
dbms_output.put_line('-' || inst_tab.FIRST);
dbms_output.put_line(TO_CHAR(inst_cnt));
else
dbms_output.put_line('Not A Clustered Database');
end if;
end;
/
SQL> declare
2 inst_tab dbms_utility.instance_table;
3 inst_cnt NUMBER;
4 begin
5 if dbms_utility.is_cluster_database then
6 dbms_utility.active_instances(inst_tab, inst_cnt);
7 dbms_output.put_line('-' || inst_tab.FIRST);
8 dbms_output.put_line(TO_CHAR(inst_cnt));
9 else
10 dbms_output.put_line('Not A Clustered Database');
11 end if;
12 end;
13 /
Not A Clustered Database
PL/SQL procedure successfully completed.
数据库已安装的组件
数据库中装了哪些组件
set linesize 180
col comp_id format a10
col comp_name format a40
col version format a15
col status format a10
select comp_id, comp_name, version, status from dba_registry;
SQL> select comp_id, comp_name, version, status from dba_registry;
SQL> set linesize 180
SQL> col comp_id format a10
SQL> col comp_name format a40
SQL> col version format a15
SQL> col status format a10
SQL> select comp_id, comp_name, version, status from dba_registry;
COMP_ID COMP_NAME VERSION STATUS
---------- ---------------------------------------- --------------- ----------
SDO Spatial 10.2.0.4.0 VALID
ORDIM Oracle interMedia 10.2.0.4.0 VALID
AMD OLAP Catalog 10.2.0.4.0 VALID
EM Oracle Enterprise Manager 10.2.0.4.0 VALID
XDB Oracle XML Database 10.2.0.4.0 VALID
CONTEXT Oracle Text 10.2.0.4.0 VALID
EXF Oracle Expression Filter 10.2.0.4.0 VALID
RUL Oracle Rules Manager 10.2.0.4.0 VALID
OWM Oracle Workspace Manager 10.2.0.4.3 VALID
ODM Oracle Data Mining 10.2.0.4.0 VALID
CATALOG Oracle Database Catalog Views 10.2.0.4.0 VALID
COMP_ID COMP_NAME VERSION STATUS
---------- ---------------------------------------- --------------- ----------
CATPROC Oracle Database Packages and Types 10.2.0.4.0 VALID
JAVAVM JServer JAVA Virtual Machine 10.2.0.4.0 VALID
XML Oracle XDK 10.2.0.4.0 VALID
CATJAVA Oracle Database Java Packages 10.2.0.4.0 VALID
APS OLAP Analytic Workspace 10.2.0.4.0 VALID
XOQ Oracle OLAP API 10.2.0.4.0 VALID
数据库启动时间
select sysdate - startup_time from v$instance;
select to_char(startup_time,'YYYY-MM-DD HH:MI:SS') from v$instance;
*******************************************************************************
查看ORACLE 的隐含参数
ORACLE 的显式参数，除了在INIT.ORA 文件中定义的外，在svrmgrl 中用"show parameter *"，
可以显示。但ORACLE 还有一些参数是以“ _ ”，开头的。如们非常熟悉的
“_offline_rollback_segments”等。
这些参数可在sys.x$ksppi 表中查出。
已sys 用户登陆
sqlplus "/ as sysdba"
select ksppinm from x$ksppi where substr(ksppinm,1,1)='_';
除了V$parameter 中的常规参数外，ORACLE 还有大量的隐含参数，下面的语句就可以查询
到数据库的所有隐含参数以及其值与参数的描述。
SELECT NAME
,VALUE
,decode(isdefault, 'TRUE','Y','N') as "Default"
,decode(ISEM,'TRUE','Y','N') as SesMod
,decode(ISYM,'IMMEDIATE', 'I',
'DEFERRED', 'D',
'FALSE', 'N') as SysMod
,decode(IMOD,'MODIFIED','U',
'SYS_MODIFIED','S','N') as Modified
,decode(IADJ,'TRUE','Y','N') as Adjusted
,description
FROM ( --GV$SYSTEM_PARAMETER
SELECT x.inst_id as instance
,x.indx+1
,ksppinm as NAME
,ksppity
,ksppstvl as VALUE
,ksppstdf as isdefault
,decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE') as ISEM
,decode(bitand(ksppiflg/65536,3),
1,'IMMEDIATE',2,'DEFERRED','FALSE') as ISYM
,decode(bitand(ksppstvf,7),1,'MODIFIED','FALSE') as IMOD
,decode(bitand(ksppstvf,2),2,'TRUE','FALSE') as IADJ
,ksppdesc as DESCRIPTION
FROM x$ksppi x
,x$ksppsv y
WHERE x.indx = y.indx
AND substr(ksppinm,1,1) = '_'
AND x.inst_id = USERENV('Instance')
)
ORDER BY NAME
*******************************************************************************
*******************************************************************************
查看当前SQLPLUS 用户的sid 和serial#
查看当前SQLPLUS用户的sid和serial#：
select sid, serial#, status from v$session where audsid=userenv('sessionid');
SQL> drop user jneall cascade;
drop user jneall cascade
*
ERROR 位于第 1 行:
ORA-01940: 无法删除当前已连接的用户
SQL> select username,sid, SERIAL# from v$session ;
SQL> select username,sid, SERIAL# from v$session where username='JNEALL';
SQL> alter system kill session '12,269';
欢迎关注我的公众号 扫描二维码或公众号搜索 “我的工作”
