oracle平时遇到的问题

最新推荐文章于 2025-09-11 20:53:34 发布

weixin_33995481

最新推荐文章于 2025-09-11 20:53:34 发布

阅读量109

点赞数

CC 4.0 BY-SA版权

文章标签：数据库 python 网络

原文链接：https://my.oschina.net/findurl/blog/829848

本文介绍了Oracle数据库中关于连接数、参数调整、表空间管理、用户权限配置、SQL优化等实用操作，包括如何查看及调整连接数、创建表空间、用户及导入导出数据的方法。

2019独角兽企业重金招聘Python工程师标准>>>

查看修改当前连接数

alter system set open_cursors=1000 scope=both;

需要重启数据库
查看连接数命令
show parameter open_cursors

sqlplus 查看连接数

SELECT v.name, v.value value FROM V$PARAMETER v WHERE name = 'open_cursors';

select count(*) from v$process --当前的连接数

dblink

dblink

create public database link crosslink connect to ll identified by oracle using 
'(DESCRIPTION = 
     (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.98.31)(PORT = 1521)))
     (CONNECT_DATA =
         (SERVER = DEDICATED)
         (SERVICE_NAME = drpa)
    )
)';
连接时候用
crosslink.MID_DRP_TO_BAITE@crosslink

数据库名和实例名

--查看数据库的名字
select name from v$database;

--查看实例名
select instance_name from v$instance;

其他添加修改字段

=============================删除列===================================================================
alter table BK_BILLREQUEST drop column is_send;
======================================================================================================

=============================列添加注释===============================================================
comment on column BK_BILLREQUEST.Serial_No_Erp is 'NC流水号';
======================================================================================================

==============================复制表结构==============================================================
execute immediate 'create table NC_BK_BILLREQUEST as select * from BK_BILLREQUEST where 1=2';
======================================================================================================
--判断表是否存在，如果不存在则创建
declare 
      num   number; 
begin 
      select count(1) into num from all_tables where upper(TABLE_NAME) = 'NC_BK_BILLREQUEST'; 
      if   num<1   then 
           execute immediate 'create table NC_BK_BILLREQUEST(BILL_ID number(14),
                                      BILL_NO varchar2(50),
                                      SERIAL_NO_ERP varchar2(32),
                                      VOUCHER_NO_ERP varchar2(20),
                                      BILL_STATUS integer,
									  APPLY_DATE TIMESTAMP(3),
                                      IS_SEND char(1)
                                      )';
                                      
      end   if; 
end; 
/
=================================================================================================================

查看锁记录干掉锁

查询锁记录
select b.username,b.sid,b.serial#,logon_time 
from v$locked_object a,v$session b 
where a.session_id = b.sid order by b.logon_time; 

干掉锁
alter system kill session 'sid,serial#';

查询表空间大小以及位置

select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name

表空间创建

1.	1.创建两个表空间：NNC_DATA01.DBF 和 NNC_INDEX01.DBF ：  
2.	create tablespace NNC_DATA01  
3.	logging    
4.	datafile 'H:\IDE\oracle\oradata\orcl\NNC_DATA01.dbf'   
5.	size 50m    
6.	autoextend on    
7.	next 50m maxsize 32767m    
8.	extent management local;   
9.	  
10.	  
11.	create tablespace NNC_INDEX01  
12.	logging    
13.	datafile 'H:\IDE\oracle\oradata\orcl\NNC_INDEX01.dbf'   
14.	size 50m    
15.	autoextend on    
16.	next 50m maxsize 32767m    
17.	extent management local;     
18.	  
19.	alter tablespace NNC_DATA01    
20.	add datafile 'H:\IDE\oracle\oradata\orcl\NNC_DATA02.dbf' size 50m   
21.	autoextend on next 50m maxsize 32767m;  
22.	  
23.	  
24.	2.创建用户nc633jx/a:  
25.	Create user nc633jx identified by a default tablespace NNC_DATA01 temporary  tablespace temp;  
26.	  
27.	3.授权用户：  
28.	Grant connect,dba to nc633jx;   
29.	  
30.	4.还原数据库nc633jx:  
31.	impdp nc633jx/a@orcl schemas=nc633jx directory=DATA_PUMP_DIR dumpfile=1009jt1.DMP logfile=1009jt1.log

查询最近执行sql

SELECT   sql_text, last_load_time
    FROM v$sql
   WHERE last_load_time IS NOT NULL
ORDER BY last_load_time DESC

用户目录赋值权限导入导出

select * from dba_directories
--创建目录
create directory dpdata as 'd:/dpdata'

--给权限
grant all on directory dpdata to gfcw;


expdp lljf05/lljf05@127.0.0.1:1521/orcl DIRECTORY=DATA_PUMP_DIR dumpfile=lljf05.dmp VERSION=11.1.0.6.0
@pause

exp username/password@oracleservice file=c:\backup%date:~4,4%%date:~9,2%%date:~12,2%.dmp    owner=(lee)
--导入的表复制到创建的文件目录
--导入的用户 目录  文件 原始的用户名当前用户名 

impdp gfcw/gfcw directory=dpdata dumpfile=201603151012.DMP  logfile=aa.log remap_schema=gfcwgs:gfcw remap_tablespace=btdata:users


imp gy/gy@orcl file=E:\备份20160401\数据库备份\gfcwgs\gy-04-29.dmp tablespaces=gync log=D:\bb.log fromuser=gy_user touser=gy

干掉连接用户

1、查询一下当前有哪些用户在连接，并且查询是从哪个操作系统连接的

select username,osuser,sid,serial# from v$session

2、kill掉相应的连接

alter system kill session ‘sid,serial#’

删除重复数的只保留rownum最小的

delete from vitae a 
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) 
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

转载于:https://my.oschina.net/findurl/blog/829848