oracle 快速查询数据库各种信息、及转换对应java代码

最新推荐文章于 2022-09-08 13:51:52 发布

置顶 pioayang

最新推荐文章于 2022-09-08 13:51:52 发布

阅读量1.8k

点赞数 1

分类专栏： oracle 文章标签： sql oracle 大数据优化数据

本文链接：https://blog.csdn.net/pioayang/article/details/11575257

版权

oracle 专栏收录该内容

89 篇文章 1 订阅

订阅专栏

1 查询表中数据量

select 'select ' || '''' || t.TABLE_NAME || '''' ||
' as table_name, count(*) from ' || t.TABLE_NAME || ' union'
from user_tables t;

2 查询数据库中锁表和解锁

详细见： http://blog.csdn.net/pioayang/article/details/10108671

SELECT /*+ rule */ s.username
,  'alter system kill session '''||s.sid||','||s.serial#||''';' ,
utl_inaddr.get_host_address(s.TERMINAL),
decode(l.type,'TM','TABLE LOCK',  
'TX','ROW LOCK',  
NULL) LOCK_LEVEL,  
o.owner,o.object_name,o.object_type,  
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser  
FROM v$session s,v$lock l,dba_objects o  
WHERE l.sid = s.sid  
AND l.id1 = o.object_id(+)  
AND s.username is NOT Null ;

--kill session语句
alter system kill session'50,492';
--以下几个为相关表
SELECT * FROM v$lock;
SELECT * FROM v$sqlarea;
SELECT * FROM v$session;
SELECT * FROM v$process ;
SELECT * FROM v$locked_object;
SELECT * FROM all_objects;
SELECT * FROM v$session_wait;

--1.查出锁定object的session的信息以及被锁定的object名

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# ;

--2.查出锁定表的session的sid, serial#,os_user_name, machine name, terminal和执行的语句

--比上面那段多出sql_text和action
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.serial#;

--3.查出锁定表的sid, serial#,os_user_name, machine_name, terminal，锁的type,mode

SELECT s.sid, s.serial#, s.username, s.schemaname, s.osuser, s.process, s.machine,
s.terminal, s.logon_time, l.type
FROM v$session s, v$lock l
WHERE s.sid = l.sid
AND s.username IS NOT NULL
ORDER BY sid;

3 查询表中注释及列的注释

详细见 http://blog.csdn.net/pioayang/article/details/11248235

select t3.table_name,t3.comments,t2.comments,t1.COLUMN_NAME
       ,t1.DATA_TYPE||'('||decode(t1.DATA_TYPE, '', t1.DATA_PRECISION+t1.DATA_SCALE, t1.data_length)||')'
from user_tab_cols t1, user_col_comments t2, user_tab_comments t3
where t1.TABLE_NAME=t2.table_name(+)
and t1.COLUMN_NAME=t2.column_name(+)
and t1.TABLE_NAME=t3.table_name(+)
and lower(t1.TABLE_NAME)='user';

4 查看表空间（查看路径、修改、创建）

详细见 http://blog.csdn.net/pioayang/article/details/10924423

1 查看表空间路径

select * from dba_data_files;

2 查看用户属于表空间和对应的表空间大小

select distinct t.OWNER from dba_tables t ;

select /*+parallel(t,4)*/t.tablespace_name,t.owner,sum(t.bytes),sum(bytes)/1024/1024
from dba_segments t where t.tablespace_name like '%DZJC%'
group by t.tablespace_name,t.owner;

3 修改表空间大小

ALTER TABLESPACE MAXDATA ADD DATAFILE '/oradata/XX/MAXDATA02.DBF' SIZE 1000M;

可以通过PL/SQL查看。
1. 查看所有表空间大小
 select tablespace_name,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name; 
2. 未使用的表空间大小 
select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name; 

查看当前用户每个表占用空间的大小： 
Select   Segment_Name,Sum(bytes)/1024/1024   From   User_Extents   Group   By   Segment_Name

3.创建表空间

-- 创建 DZJC 表空间
CREATE SMALLFILE TABLESPACE "LHCZW" DATAFILE 'F:\oracle\product\10.2.0\oradata\LH\LHCZW' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
-- 创建 DZJC_INDEX 表空间
CREATE SMALLFILE TABLESPACE "LHCZW_INDEX" DATAFILE 'F:\oracle\product\10.2.0\oradata\LH\LHCZW_index' SIZE 100M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
-- 创建 DZJC_TEMP 表空间
CREATE SMALLFILE TEMPORARY TABLESPACE "LHCZW_TEMP" TEMPFILE 'F:\oracle\product\10.2.0\oradata\LH\LHCZW_temp' SIZE 100M AUTOEXTEND ON NEXT 25M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

-- 创建 DZJC 用户
CREATE USER "LHCZW" PROFILE "DEFAULT" IDENTIFIED BY "123456" DEFAULT TABLESPACE "LHCZW" TEMPORARY TABLESPACE "LHCZW_TEMP" ACCOUNT UNLOCK;
GRANT "CONNECT" TO "LHCZW" WITH ADMIN OPTION;
GRANT "DBA" TO "LHCZW" WITH ADMIN OPTION;

表做空间迁移时，使用如下语句：　　例1：alter table tb_name move tablespace tbs_name;
　　索引表空间做迁移，使用如下语句：
　　例2：alter index index_name rebuild tablespace tbs_name;
　　对于含有lob字段的表，在建立时，oracle会自动为lob字段建立两个单独的segment,一个用来存放数据，另一个用来存放索引，并且它们都会存储在对应表指定的表空间中，而例1：只能移动非lob字段以外的数据，所以在对含有lob字段的表进行空间迁移，需要使用如下语句：
　　例3：alter table tb_name move tablespace tbs_name lob (col_lob1,col_lob2) store as(tablesapce tbs_name);
　　项目实例：
　　表空间迁移
　　select 'alter table' ||table_name|| 'move tablespace tbs_name;' table_name from dba_tables where wner='%***%' and table_name like '%***%'
　　带lob字段
　　select 'alter table' ||table_name|| 'move lob('||index_name||') store as (tablespace tbs_name);' from dba_indexes where wner='%***%' and index_name like '%***%'
　　索引表空间
　　select 'alter index' ||index_name|| 'rebuild tablespace tbs_name;' index_name from dba_indexes where wner='%***%' and table_name like '%***%'
　　以上在oracle 的SQL*Plus Worksheet中运行，将得出的运行结果再运行一次即可。

5 快速导出多个数据表数据的sql

SELECT  TABLESPACE_NAME,
       substr(MAX(sys_connect_by_path(trim(K.STATUS_T), ' ')), 3)
       ,'EXP MSCX/123456  FILE=C:/MSXM.DMP LOG=C:/MSXM.LOG TABLES=('||substr(MAX(sys_connect_by_path(trim(K.STATUS_T), ' ')), 3)||') STATISTICS=NONE'
  FROM (SELECT TABLESPACE_NAME,TABLE_NAME,
               DECODE(T.TABLE_NAME, NULL, '', ',' || T.TABLE_NAME) AS STATUS_T,
               ROW_NUMBER() OVER(PARTITION BY T.TABLESPACE_NAME ORDER BY T.TABLESPACE_NAME) AS ID1,
               ROW_NUMBER() OVER(ORDER BY T.TABLESPACE_NAME) + DENSE_RANK() OVER(ORDER BY T.TABLESPACE_NAME) ID2
          FROM user_tables T
          where t.TABLE_NAME like '%MSXM%'
         GROUP BY T.TABLESPACE_NAME, T.TABLE_NAME) K
 start with k.id1 = 1
connect by prior (k.id2) = (k.id2 - 1)
 group by TABLESPACE_NAME;

结果拼接成： EXP MSCX/123456  FILE=C:/MSXM.DMP LOG=C:/MSXM.LOG TABLES=(table1, table2,...) STATISTICS=NONE

6 转换java代码（javaEE hiberanate1）

详细见：http://blog.csdn.net/pioayang/article/details/11252409

--数据库中字段java代码

select col.TABLE_NAME,replace(initcap(col.TABLE_NAME),'_', '')
  , 'private '||decode(k.DATA_TYPE, 'VARCHAR2', 'String', 'NUMBER', 'java.lang.Integer', 'DATE', 'java.sql.Timestamp','CHAR', 'String', k.DATA_TYPE)||' '|| lower(col.COLUMN_NAME)||';//'||com.comments
  , '<property name="'||lower(col.COLUMN_NAME)||'" column="'||lower(col.COLUMN_NAME)||'" type="'
  ||decode(k.DATA_TYPE, 'VARCHAR2', 'java.lang.String', 'NUMBER', 'java.lang.Integer', 'DATE', 'java.sql.Timestamp','CHAR', 'String', k.DATA_TYPE)||'"/>'
  from user_tab_columns k , user_tab_columns col
 inner join user_col_comments com    
   on col.TABLE_NAME = com.TABLE_NAME
   and col.COLUMN_NAME = com.COLUMN_NAME
   and com.table_name like '%T_JC_NEW_ZFCG_%' 
   and replace(initcap(col.TABLE_NAME),'_', '') like '%TJcNewZfcgZjjbxx%'
 where k.TABLE_NAME=col.TABLE_NAME and k.COLUMN_NAME=col.COLUMN_NAME and k.COLUMN_ID=col.COLUMN_ID
 order by col.TABLE_NAME, col.COLUMN_ID

7 转换java代码（javaEE hiberanate2）

select
    /*private String busiindexid;*/
    'private '||col_type||' '||java_col||';//'||col_commects
    /*get/set注入*/
    ,col_type||' '||java_col||','
    ,'this.'||java_col||'='||java_col||';'
    /*表列的get、set配置*/
     /*@Column(name = "BUSIINDEXID") public String getBusiindexid() {return this.busiindexid;}*/    
    ,'@Column(name ="'||col_name||'") public '||col_type||' get'||java_col_getSet||'() {return this.'||java_col||';} '
     /*public void setDeptid(String deptid) {this.deptid = deptid;}*/
    ||'public void set'||java_col_getSet||'('||col_type||' '||java_col||' ) {this.'||java_col||'='||java_col||';}'
     /*表的信息*/    
    ,tab.*
from (select col.TABLE_NAME,
   replace(initcap(col.TABLE_NAME), '_', '') as java_tab,
   col.COLUMN_NAME as col_name,
   lower(col.COLUMN_NAME) as java_col,
   replace(initcap(col.COLUMN_NAME), '_', '') as java_col_getSet,
   decode(k.DATA_TYPE,
       'VARCHAR2','String',
       'NUMBER','java.lang.Integer',
       'DATE','java.sql.Date',
       'CHAR','String',
       k.DATA_TYPE) AS col_type,
   com.comments AS col_commects
 from user_tab_columns k, user_tab_columns col
inner join user_col_comments com
  on col.TABLE_NAME = com.TABLE_NAME
 and col.COLUMN_NAME = com.COLUMN_NAME
 and com.table_name = 'T_ZDSX_YW_BASEINFO' /*表的名*/
where k.TABLE_NAME = col.TABLE_NAME
 and k.COLUMN_NAME = col.COLUMN_NAME
 and k.COLUMN_ID = col.COLUMN_ID
order by col.TABLE_NAME, col.COLUMN_ID
) tab

8、查询时间（年、季度、月、星期、天）

select to_char(sysdate,'yyyy'),to_char(sysdate,'q'),to_char(sysdate,'mm'),to_char(sysdate,'d'),to_char(sysdate,'dd') from dual;

to_char(sysdate,'q') 季
to_char(sysdate,'yyyy')年
to_char(sysdate,'mm')月
to_char(sysdate,'dd')日
to_char(sysdate,'d')星期中的第几天
to_char(sysdate,'DAY')星期几
to_char(sysdate,'ddd')一年中的第几天

9 查看用户连接和关闭

select t.USERNAME,status, utl_inaddr.get_host_address(t.TERMINAL) as ip, t.MODULE,
'alter system kill session '''||t.sid||','||t.serial#||''';'
from SYS.V_$SESSION t
where t.USERNAME in ('LH_SPJC', 'LH');

10 查看jobs和设置失效

select t.ENABLED,
to_char(t.last_start_date, 'yyyy-mm-dd hh24:mi:ss ff6'),
'exec dbms_scheduler.disable(''' || t.OWNER || '.' || t.JOB_NAME || ''');', t.*
from all_scheduler_jobs t
where t.ENABLED = 'TRUE'
or t.last_start_date is not null;