oracle上一些查询表和字段语句

oracle上一些查询表和字段语句

查询表空间使用情况

select upper(f.tablespace_name) "tablespace_name",
       round((d.tot_grootte_mb - f.total_bytes) / d.tot_grootte_mb * 100,2) " used (%) ",
       round(f.total_bytes / d.tot_grootte_mb * 100,2) "free (%)",
       d.tot_grootte_mb "total (mb)",
       d.tot_grootte_mb - f.total_bytes " used (mb)",
       f.total_bytes " free_space (mb) "
from      
    (select tablespace_name,
            round(sum(bytes)/(1024*1024),2) total_bytes,
            round(max(bytes)/(1024*1024),2) max_bytes
      from sys.dba_free_space
      group by tablespace_name) f,
      (select dd.tablespace_name, round(sum(dd.bytes)/(1024*1024),2) tot_grootte_mb
       from   sys.dba_data_files dd
      group by dd.tablespace_name) d
where d.tablespace_name = f.tablespace_name    
order by 2 desc;

--查询表空间中表数据占用情况语句

 1 SELECT 
 2      OWNER
 3     ,TABLESPACE_NAME
 4     ,SEGMENT_NAME
 5     ,SUM(BYTES) / 1024 / 1024 AS TOALL
 6 FROM DBA_SEGMENTS
 7 WHERE SEGMENT_NAME = 'TB_DSS_APP_BI_KPI_D1901054'
 8 GROUP BY  
 9     SEGMENT_NAME
10 --HAVING SUM(BYTES) / 1024 / 1024 >= 10
11 ORDER BY 
12     TOALL DESC;

-------表大小

 1 select 
 2     (tt.free_gb + tt1.use_gb)
 3 from 
 4     (
 5         select 
 6              t.tablespace_name
 7             ,sum(t.bytes) / 1024 / 1024 / 1024 as free_gb
 8         from user_free_space t, user_users s
 9         where 
10             t.tablespace_name = s.default_tablespace
11         group by 
12             t.tablespace_name
13     ) tt,(
14             select 
15                 sum(t.bytes) / 1024 / 1024 / 1024 as use_gb
16             from user_segments t
17         ) tt1
18 ;

----查用户下表

 1 select * from all_TABLES where lower(owner) like '%dic_bi%'； 

----查用户下表对应字段

 1 select 
 2      table_name
 3     ,column_name
 4     ,data_type
 5 from ALL_tab_columns 
 6 where 
 7     lower(owner) like '%dic_bi%' 
 8     and lower(table_name) like 'tb_dss_app_bi_kpi%' 
 9 order by 
10      table_name
11     ,column_name
12 ;

-- 去重语句

 1 delete from ldc_data.tb_xw_lc_xhzwfx_02 t 
 2 where exists (
 3                 select * 
 4                 from  ldc_data.tb_xw_lc_xhzwfx_02 t2 
 5                 where  
 6                     t.latn_id=t2.latn_id 
 7                     and t.prd_inst_id=t2.prd_inst_id  
 8                     and t.rowid>t2.rowid
 9             )
10 ;

--  循环

 1 begin  
 2      for cu_latn in (select  latn_id  from  eda.tb_b_dim_latn) loop
 3       execute immediate 'insert into  eda.tem_chenbao_hlr_day_20200703
 4 select  /*+ parallel(a,8) */
 5 20200703 day_id,a.latn_id,b.latn_name,b.order_id,count(distinct a.prd_inst_id) kj,'||cu_latn.latn_id||' old_latn_id        
 6 from tb_b_ft_hlr_day_'||cu_latn.latn_id||' a,
 7      tb_b_dim_latn_city b         
 8 where a.act_flag=1 and a.day=3 and a.latn_id=b.latn_id
 9 group by a.latn_id,b.latn_name,b.order_id';
10 commit;
11 end loop;
12 end;
13 /

--  oracle添加注释

1 COMMENT ON COLUMN STUDENT_INFO.STU_ID IS '学号'; 

2、注释：comment on table tabel_name is "";

添加字段

alter table tb_dss_mid_ofr_inst_day add(prd_inst_id NUMBER); 

-- decode函数

 1 decode(条件，值1，返回值1，值2，返回值2，...值n,返回值n，缺省值)