Oracle专题
监视执行过的SQL 语句
select * from v$sqlarea a where module='PL/SQL Developer' order by a.FIRST_LOAD_TIME desc
导出命令:exp
https://blog.csdn.net/jiushancunmonkeyking/article/details/78851461
exp的参数文件 : https://blog.csdn.net/IndexMan/article/details/102534974
数据库导出注意事项
如不能整个库导出,单独导出表、触发器、序列,Oracle中使用序列产生的自增列,注意序列的当前值问题
查看数据库大小
Linsener.log文件太大
Oracle函数--字符串拼接
select WM_CONCAT(COLUMN_NAME ) AS SCORE表的列名 from user_tab_columns WHERE TABLE_NAME = 'T_BC_QH';
oracle常用系统表
语句:
INSERT INTO T_BD_FXXX(BT) VALUES((select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual)||' '||'BEGIN_PRO_XSQ_MSFHFHFH'); -- 字符串连接不用+,而是||
lsbNo number;
select count(*) into lsbNo from all_tables where TABLE_NAME = 'PD_TMP'; -- 变量赋值
后来发现PL/SQL中在SQL窗口下是不能执行EXEC命令的,要用BEGIN END包起来,改成这样后马上执行成功
BEGIN
INSERTINFO;
END;
SQL窗口中:
declare r varchar(90);begin
-- Call the procedure
--sayremotehello;
execute immediate 'begin sayHello@hsmzj(:a); end;' using out r;
dbms_output.put_line(r);
end;
存储过程中
create or replace procedure "SAYREMOTEHELLO" authid current_user as
sqlstr varchar(1000);
r varchar(100);
begin
--sqlstr:='drop database link tlink';
-- execute immediate sqlstr;
-- sqlstr:= 'create database link tlink connect to scott identified by fj1234 using ''(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.70)(PORT = 1521)))(CONNECT_DATA = (SERVICE_NAME = orcl)))''';
-- SERVICE_NAME 使用 SELECT * FROM GLOBAL_NAME; 可查,默认为orcl
-- sqlstr:= 'create database link tlink connect to '||name||' identified by '||pwd||' using ''(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = '||ip|| ')(PORT = 1521)))(CONNECT_DATA = (SERVICE_NAME = orcl)))''';
-- execute immediate sqlstr;
sqlstr:='begin sayHello@hsmzj(:a); end;'; -- 注意ORACLE中的等于 :=
execute immediate sqlStr using out r; --存储过程返回值赋值
dbms_output.put_line(r);
end SAYREMOTEHELLO;
execute immediate sqlStr into 变量
oracle 写declare例子
oracle 的存储过程里没有declare的语法,触发器和plsql块才是declare
如果要定义变量,后面直接跟as的
CREATE OR REPLACE PROCEDURE c(xxx)
aS
para number;
BEGIN
end;
存储过程一个总结的非常全的博客
http://www.cnblogs.com/chinafine/archive/2010/07/12/1776102.html