我的常用sql 总结笔记

--查看后台
tail -f tmep.out
01a000911
--被锁的表
select sess.sid,sess.SERIAL#,lo.*,ao.*,sess.*  from v$locked_object lo, dba_objects ao,v$session sess where ao.OBJECT_ID = lo.OBJECT_ID and lo.SESSION_ID = sess.sid;

select * from v$session t1 ,v$locked_object t2 where t1.SID = t2.SESSION_ID;

alter system kill session '3185,1218';


--激活的连接数
select username,count(*) from v$session where status='INACTIVE'  group by username;
--1、耗时长sql查证：--
select * from (select SQL_TEXT,round(ELAPSED_TIME/1000000,2),round(ELAPSED_TIME/1000000/decode(EXECUTIONS,0,1,null,1,EXECUTIONS),2) perELAPSED_time,EXECUTIONS
   from (select * from v$sql order by ELAPSED_TIME desc) where rownum<26) order by perELAPSED_time desc

--6、查看表空间的使用情况
SELECT SUM(bytes) / (1024 * 1024) AS free_space, tablespace_name
FROM dba_free_space
GROUP BY tablespace_name;
SELECT a.tablespace_name,
a.bytes total,
b.bytes used,
c.bytes free,
(b.bytes * 100) / a.bytes "% USED ",
(c.bytes * 100) / a.bytes "% FREE "
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
WHERE a.tablespace_name = b.tablespace_name
AND a.tablespace_name = c.tablespace_name;

--注册jar包到本地mvn库
mvn install:install-file -Dfile=D:\jbarcode-0.2.8.jar -DgroupId=com.bitservice -DartifactId=jbarcode -Dversion=0.2.8 -Dpackaging=jar  

--创建表
drop table hzlbgrz;
--户坐落变更日志
create table hzlbgrz(
       id number(8),
       hxh number(8),
       bgqzl varchar2(500),
       bghzl varchar2(500),
       bgsj date,
       czz number(8)
);

--添加主键
alter table hzlbgrz add constraint pk_id primary key (id);
--添加注释
comment on column hzlbgrz.id is '序号';
comment on column hzlbgrz.hxh is '户序号';
comment on column hzlbgrz.bgqzl is '变更前坐落';
comment on column hzlbgrz.bghzl is '变更后坐落';
comment on column hzlbgrz.bgsj is '变更时间';
comment on column hzlbgrz.czz is '操作者';

drop sequence seq_hzlbgrz;
--seq_ckrz
create sequence seq_gr start with 1 nocache;

-- [tri_ckrz] 
create or replace trigger tri_hql_bi
	before insert on hql for each row
declare
begin
	if :new.hqlxh is null or :new.hqlxh < 0 then
		select seq_hql.nextval into :new.hqlxh from dual;
	end if;
end;
/
show err;




--输入以下命令, 修改 oracle 最大游标数为 1000
alter system set open_cursors=1000 scope=both;

--(4). 查看最大游标数是否已修改成功
show parameter open_cursors;


---如何导出空表，在dmp文件里
--1、先查询一下当前用户下的所有空表
    select table_name from user_tables where NUM_ROWS=0;  

--  2、用以下这句查找空表
    select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0  

--  3、把查询结果导出，执行导出的语句
    'ALTERTABLE'||TABLE_NAME||'ALLOCATEEXTENT;'   
    -----------------------------------------------------------       
    alter table AQ$_AQ$_MEM_MC_H allocate extent;        
    alter table AQ$_AQ$_MEM_MC_G allocate extent;  
    alter table AQ$_AQ$_MEM_MC_I allocate extent;  
    alter table AQ$_AQ_PROP_TABLE_T allocate extent;        
    alter table AQ$_AQ_PROP_TABLE_H allocate extent;       
    alter table AQ$_AQ_PROP_TABLE_G allocate extent;       
    alter table AQ$_AQ_PROP_TABLE_I allocate extent;       
    alter table AQ$_KUPC$DATAPUMP_QUETAB_T allocate extent;        
    alter table AQ$_KUPC$DATAPUMP_QUETAB_H allocate extent;        
    alter table AQ$_KUPC$DATAPUMP_QUETAB_G allocate extent;       
    alter table AQ$_KUPC$DATAPUMP_QUETAB_I allocate extent;  

--4、然后再执行导出即可。

2）windows下

编辑tomcat的catalina.bat文件，在第一行的后面增加一句：
set JAVA_OPTS=-server -Xms256m -Xmx512m -XX:PermSize=128M -XX:MaxPermSize=256M





--创建同义词
connect di/bitservice;

grant all on descartes_bld2 to nttfehsac;
grant all on building_bld2 to nttfehsac;
grant all on house_bld2 to nttfehsac;
grant all on housearchive_bld2 to nttfehsac;
grant all on parcel_bld2 to nttfehsac;
grant all on division_bld2 to nttfehsac;
grant execute on pkg_businesscodecreator to nttfehsac;

connect nttfehsac/bitservice;

create synonym afs2012_descartes for di.descartes_bld2;
create synonym afs2012_building for di.building_bld2;
create synonym afs2012_house for di.house_bld2;
create synonym afs2012_housearchive for di.housearchive_bld2;
create synonym afs2012_parcel for di.parcel_bld2;
create synonym afs2012_division for di.division_bld2;

--创建用户 
connect system/oracle;


--创建表空间
create tablespace FIREHOUSETBS datafile 'e:\test.ora' size 1000m;

--删除表空间
DROP TABLESPACE FIREHOUSETBS INCLUDING CONTENTS AND DATAFILES;

--删除用户
drop user nttfehsac cascade;

create user nttfehsac identified by bitservice;
grant resource,connect to nttfehsac ;
grant select any dictionary to nttfehsac ;
grant select any sequence to nttfehsac ;
grant select any table to nttfehsac ;
grant create any table,alter any table,drop any table,create any view to nttfehsac ;
grant select any table,update any table to nttfehsac ;
grant insert any table,delete any table to nttfehsac ;
grant create any table,alter any table,drop any table to nttfehsac ;
grant create any trigger,alter any trigger,drop any trigger to nttfehsac ;
grant create any procedure,alter any procedure,drop any procedure to nttfehsac ;
grant create any sequence,alter any sequence,drop any sequence to nttfehsac ;
grant create any synonym,drop any synonym to nttfehsac ;

select count(*) from v$process --当前的连接数
select value from v$parameter where name = 'processes' --数据库允许的最大连接数

--修改最大连接数:
alter system set processes = 300 scope = spfile;

--重启数据库:
shutdown immediate;
startup;

--查看当前有哪些用户正在使用数据
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;




--前几天为了测试oracle超过最大连接数会带来什么后果，故意将最大连接数设置的比较小一点，
--今天在使用pl/sql developer连接数据库的时候就出现了
--“ORA-12516: TNS: 监听程序找不到符合协议堆栈要求的可用处理程序”的错误。

--错误解决方案如下：
--1。首先查看oracle的最大连接数是多少引用
SQL> show parameter processes;
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------
aq_tm_processes                      integer     0
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
global_txn_processes                 integer     1
job_queue_processes                  integer     1000
log_archive_max_processes            integer     4
processes                            integer     50
SQL> show parameter sessions;
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------
java_max_sessionspace_size           integer     0
java_soft_sessionspace_limit         integer     0
license_max_sessions                 integer     0
license_sessions_warning             integer     0
sessions                             integer     98
shared_server_sessions               integer

--2。然后查看process和session的使用情况引用
SQL> select count(*) from v$process;
  COUNT(*)
----------
        49
SQL> select count(*) from v$session;
  COUNT(*)
----------
        46
--发现明显process的使用几乎达到的峰值

--3。修改oracle的process和session的值，加大他们的最大连接数引用
  SQL> alter system set processes=300 scope=spfile;
  System altered.
  SQL> alter system set sessions=335 scope=spfile;
  System altered.

--4。重起数据库，保存修改
  SQL> shutdown immediate;   
  SQL> startup;   
 



--建立job

declare  
   x number;
begin 
    dbms_job.submit(
                      job => x, 
                      what => 'proc_autoSetYwxhqz(1);',
                      next_date => sysdate,
                      interval => 'sysdate+30/(24*60)',
                      no_parse => TRUE 
                      );
    dbms_output.put_line('Job Number is: ' || to_char(x));  
    commit;
end;
/
show err;


--查看job
select * from dba_jobs;
--删除job
begin
dbms_job.remove(61); 
end;


--1、每分钟执行
TRUNC(sysdate,'mi')+1/(24*60)

--1.1 每30分钟运行一次
sysdate+30/(24*60)

--2、每天定时执行
--例如：
--每天凌晨0点执行
TRUNC(sysdate+1)
--每天凌晨1点执行
TRUNC(sysdate+1)+2/24
--每天早上8点30分执行
TRUNC(SYSDATE+1)+(8*60+30)/(24*60)

--3、每周定时执行
--例如：
--每周一凌晨2点执行
TRUNC(next_day(sysdate,1))+2/24
TRUNC(next_day(sysdate,'星期一'))+2/24
--每周二中午12点执行
TRUNC(next_day(sysdate,2))+12/24
TRUNC(next_day(sysdate,'星期二'))+12/24

--4、每月定时执行
--例如：
--每月1日凌晨0点执行
TRUNC(LAST_DAY(SYSDATE)+1)
--每月1日凌晨1点执行
TRUNC(LAST_DAY(SYSDATE)+1)+1/24

--5、每季度定时执行
--每季度的第一天凌晨0点执行
TRUNC(ADD_MONTHS(SYSDATE,3),'q')
--每季度的第一天凌晨2点执行
TRUNC(ADD_MONTHS(SYSDATE,3),'q')+2/24
--每季度的最后一天的晚上11点执行
TRUNC(ADD_MONTHS(SYSDATE+ 2/24,3),'q')-1/24

--6、每半年定时执行
--例如：
--每年7月1日和1月1日凌晨1点执行
ADD_MONTHS(TRUNC(sysdate,'yyyy'),6)+1/24

--7、每年定时执行
--例如：
--每年1月1日凌晨2点执行
ADD_MONTHS(TRUNC(sysdate,'yyyy'),12)+2/24