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

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

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

 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 '学号';

-- decode函数

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

尽管很渺小，但终究会变得伟大