oracle 基础操作

引言

1. 2024-07-04

2024-07-04

1. Oracle 数据库链接配置

service_name1=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ip )(PORT = prot ))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = FIB)
    )
  )  
    
service_name2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =10.xxx.xxx.xxx)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )  

2. Oracle 数据库表空间

SELECT TABLESPACE_NAME "表空间",
      To_char(Round(BYTES / 1024, 2), '99990.00')
      || ''           "实有",
      To_char(Round(FREE / 1024, 2), '99990.00')
      || 'G'          "现有",
      To_char(Round(( BYTES - FREE ) / 1024, 2), '99990.00')
      || 'G'          "使用",
      To_char(Round(10000 * USED / BYTES) / 100, '99990.00')
      || '%'          "比例"
FROM   (SELECT A.TABLESPACE_NAME                             TABLESPACE_NAME,
              Floor(A.BYTES / ( 1024 * 1024 ))              BYTES,
              Floor(B.FREE / ( 1024 * 1024 ))               FREE,
              Floor(( A.BYTES - B.FREE ) / ( 1024 * 1024 )) USED
       FROM   (SELECT TABLESPACE_NAME TABLESPACE_NAME,
                      Sum(BYTES)      BYTES
               FROM   DBA_DATA_FILES
               GROUP  BY TABLESPACE_NAME) A,
              (SELECT TABLESPACE_NAME TABLESPACE_NAME,
                      Sum(BYTES)      FREE
               FROM   DBA_FREE_SPACE
               GROUP  BY TABLESPACE_NAME) B
       WHERE  A.TABLESPACE_NAME = B.TABLESPACE_NAME)
WHERE TABLESPACE_NAME LIKE 'TB_SSP_SC35%' --这一句用于指定表空间名称
ORDER  BY Floor(10000 * USED / BYTES) DESC;

3. Oracle 查询 数据库链接数

--查看数据库最大连接数
select value from v$parameter where name = 'processes';

--更改数据库连接数
alter system set processes = 3000 scope = spfile;

4. Oracle 查询 锁表语句

select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;
-------------------------------------
--查询锁表语句  
select l.session_id sid, 
      s.serial#, 
      l.locked_mode, 
      l.oracle_username, 
      s.user#, 
      l.os_user_name, 
      s.machine, 
      s.terminal, 
      a.sql_text, 
      a.action 
 from v$sqlarea a, v$session s, v$locked_object l 
where l.session_id = s.sid 
  and s.prev_sql_addr = a.address 
  order by sid,s.erial#;

---------------------------------
SELECT l.session_id sid,  
      s.serial#,  
      l.locked_mode 锁模式,  
      l.oracle_username 登录用户,  
      l.os_user_name 登录机器用户名,  
      s.machine 机器名,  
      s.terminal 终端用户名,  
      o.object_name 被锁对象名,  
      s.logon_time 登录数据库时间  
FROM v$locked_object l, all_objects o, v$session s  
WHERE l.object_id = o.object_id  
  AND l.session_id = s.sid  
ORDER BY sid, s.serial#;

---------------------------------

5.Oracle 查询 当前活跃线程数

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

6. Oracle 查询 sql执行记录

select * from v$sqltext  where sql_text like '%sql语句%';

7. 杀掉进程、批量杀死进程

--杀掉进程 sid,serial#
alter system kill session '223,10747';

--批量杀死进程
declare cursor mycur is   
select b.sid,b.serial#   
　　from v$locked_object a,v$session b   
　　where a.session_id = b.sid group by b.sid,b.serial#;   
   
begin   
  for cur in mycur   
    loop     
     execute immediate ( 'alter system  kill session  '''||cur.sid || ','|| cur.SERIAL# ||''' ');   
     end loop;   
end;

8.闪回-恢复数据

1.
SELECT	* 
FROM
	SSP_SEC_DATAOBJ_USER AS OF TIMESTAMP TO_TIMESTAMP( '2021-01-01 20:00:00', 'yyyy-mm-dd hh24:mi:ss' );

2.
SELECT	* 
FROM
	SSP_SEC_DATAOBJ_USER AS OF TIMESTAMP SYSDATE - 1440 / 1440 
WHERE	1 = 1 
	AND SSP_SEC_DATAOBJ_USER.EMP_UID = '03248156';

常用函数

1.获取随机数（32位）

SELECT * FROM dba_jobs;

2 查看数据库定时任务

SELECT * FROM dba_jobs;