Oracle 常用命令

连接数篇:

-- 当前的连接数
select count(*) from v$session;
-- 并发连接数
select count(*) from v$session where status='ACTIVE';
--数据库允许的最大连接数
select value from v$parameter where name = 'processes';
-- 查看不同用户的连接数
select username,count(username) from v$session where username is not null group by username;
-- sql语句执行的时间
SELECT osuser, a.username,cpu_time/executions/1000000||'s', sql_fulltext,machine
from v$session a, v$sqlarea b
where a.sql_address =b.address order by cpu_time/executions desc;
---查看当前闲置连接数
select program 连接方式,machine 客户机,schemaname 用户,LOGON_TIME 登陆时间,status 状态 from v$session 
where 
-- status='INACTIVE' 
schemaname not in ('SYS')
ORDER BY  LOGON_TIME DESC;

---查看 Oracle 正在执行的 sql 语句以及发起的用户
SELECT b.sid oracleID,  
       b.username 用户名,  
       b.serial#,  
       paddr,  
       sql_text 正在执行的SQL, 
    C.FIRST_LOAD_TIME 第一次执行时间,C.LAST_LOAD_TIME 最近一次执行时间,C.LAST_ACTIVE_TIME 最近一次活跃时间,
       b.machine 计算机名称
FROM v$process a, v$session b, v$sqlarea c  
WHERE a.addr = b.paddr  
   AND b.sql_hash_value = c.hash_value 

-- 修改最大游标数：
show parameter open_cursors; ---查看当前最大游标数
select count(*) from v$open_cursor;---查看目前使用的游标数
alter system set open_cursors=1500 scope=both; ---修改最大游标数
show parameter open_cursors; ---确认是否生效

-- 查看表空间
SELECT B.FILE_NAME 物理文件名,B.TABLESPACE_NAME 表空间,B.BYTES/1024/1024 大小M,(B.BYTES-SUM(NVL(A.BYTES,0)))/1024/1024 已使用M,SUBSTR((B.BYTES-SUM(NVL(A.BYTES,0)))/(B.BYTES)*100,1,5) 利用率
　　FROM DBA_FREE_SPACE A,DBA_DATA_FILES B
　　WHERE A.FILE_ID=B.FILE_ID
　　GROUP BY B.TABLESPACE_NAME,B.FILE_NAME,B.BYTES
　　ORDER BY B.TABLESPACE_NAME
-- 增加表空间
ALTER TABLESPACE RIGHT_DATA ADD DATAFILE '/data/app/oracle/oradata/orclRIGHT_01.ORA' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 32767M;

服务器运维篇

oracle闪回恢复数据：
适用于被delete删除的数据，根据实际情况，可恢复几个小时内的数据

1、查询删除数据时间点之前的数据，比如查询7月1日12点之前的数据
select * from 表名 as of timestamp to_timestamp('2023-07-01 12:00:00','yyyy-mm-dd hh24:mi:ss') ;
若没有数据 ，将时间继续提前，后面也可以跟where条件

2、如果查询到数据，则创建临时表保存数据
create table 临时表名 as select * from 表名 as of timestamp to_timestamp('2023-07-01 12:00:00','yyyy-mm-dd hh24:mi:ss') ;

3、通过insert into 将需要恢复的数据从临时表插入到正式表
第一种情况：该表一直有业务数据产生，建议对比正式表和临时表，只将临时表比正式表多的数据插入到正式表
第二种情况：该表在删除的这段时间内没有新数据产生，可直接备份正式表，再删除，最后整表插入
oracle闪回恢复表及数据：
适用于被drop删除表且非purge情况下的表恢复，一旦清空了回收站，将无法通过闪回恢复表数据

​恢复操作：
flashback table 表名 to before drop;

如果需要恢复的报表已存在，可以在恢复时重新命名其他名字：
flashback table 表名 to before drop rename to 新表名

如何重启数据库服务（图示见图1、图2、图3）

第一步：切换root用户

第二步：在root用户下，切换oracle用户

su - oracle

第三步：进入sql命令行状态

sqlplus / as sysdba

第四步：关闭数据库服务

shutdown immediate

第五步：开启数据库服务

startup

修改oracle最大连接数（图示见下图1）

第一步：切换root用户

第二步：在root用户下，切换oracle用户

su - oracle

第三步：进入sql命令行状态

sqlplus / as sysdba

第四步：修改当前最大连接数为3000

alter system set processes = 3000 scope = spfile;

第五步：关闭数据库服务：

shutdown immediate

第六步：开启数据库服务：

startup

第七步：验证修改是否生效：

select value from v$parameter where name ='processes';

修改oracle最大游标数

--查询当前最大游标数

select value from v$parameter where name='open_cursors';

---修改最大游标数

alter system set open_cursors=3000 scope=both;

---确认是否生效

select value from v$parameter where name='open_cursors';

修改游标数不需要重启数据库服务，修改后即生效

解锁表

---查看被锁表信息：

SELECT

c.sid,c.serial#,a.oracle_username 登陆账号,a.os_user_name 登陆的电脑,b.object_name 被锁表名,a.locked_mode 锁住级别

FROM v$locked_object a,dba_objects b,v$session c

WHERE

  b.object_id = a.object_id

  AND a.session_id = c.sid;

---查看数据库引起锁表的SQL语句

SELECT A.USERNAME,A.MACHINE,A.PROGRAM,A.SID,A.SERIAL#,A.STATUS,C.PIECE,C.SQL_TEXT

FROM V$SESSION A, V$SQLTEXT C

WHERE A.SID IN (SELECT DISTINCT T2.SID

FROM V$LOCKED_OBJECT T1, V$SESSION T2

WHERE T1.SESSION_ID = T2.SID)

AND A.SQL_ADDRESS = C.ADDRESS(+)

ORDER BY C.PIECE;

---杀掉锁表进程

alter system kill session '68,51'; --68和51分别为SID和SERIAL#

oracle创建dblink（主要用于不同数据库之间的数据同步）

1.授权用户创建dblink权限

grant CREATE PUBLIC DATABASE LINK,DROP PUBLIC DATABASE LINK to 用户名;

2.在A库创建dblink，连接B库：（标红内容为B库相关信息）

create public database link 名称   

connect to 用户名 identified by 密码

using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.202.103)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = erporcl)))';

3.查询数据：

select * from 表名@dblink名称;

4.删除dblink：

drop public database link 名称;

oracle用户密码过期，如何恢复

第一步：修改密码：

alter user  用户 identified by 密码;

第二步：解锁：

alter user  用户 account unlock;