Oracle数据库日常操作

Oracle数据库日常操作

一、查询数据库表的字段、类型、长度、是否为空

select
       a.TABLE_NAME as TableName,
       case when (select count(*) from user_views v where v.VIEW_NAME =a.TABLE_NAME )>0 then 'V' else 'U'end as "TableType",
       a.COLUMN_NAME as ColumnName,
       A.COLUMN_ID as ColumnIndex,
       a.DATA_TYPE as DataType,
       case  
         when a.DATA_TYPE = 'NUMBER' then
           case when a.Data_Precision is null then
             a.Data_Length
             else 
               a.Data_Precision
               end 
         else
          a.Data_Length
       end as Length,
       case when a.nullable = 'N' then  '0' else '1' end as IsNullable,
       b.comments as "Description", 
       case
          when (select count(*) from user_cons_columns c 
            where c.table_name=a.TABLE_NAME and c.column_name=a.COLUMN_NAME and c.constraint_name=
                (select d.constraint_name from user_constraints d where d.table_name=c.table_name and d.constraint_type   ='P')
                 )>0 then '1' else '0'end as IsPK
  from USER_TAB_COLS a,
       sys.user_col_comments b
 where a.table_name = b.table_name      
       and b.COLUMN_NAME = a.COLUMN_NAME       
       order by a.TABLE_NAME, a.COLUMN_ID;

二、查询表的约束

select
	a.TABLE_NAME,
	a.CONSTRAINT_NAME,
	a.CONSTRAINT_TYPE,
	wm_concat (b.COLUMN_NAME) as CONSTRAINT_COLUMNS,
	a.R_CONSTRAINT_NAME
from
	USER_CONSTRAINTS a
	left join USER_CONS_COLUMNS b on a.CONSTRAINT_NAME = b.CONSTRAINT_NAME
group by
	a.TABLE_NAME,
	a.CONSTRAINT_NAME,
	a.CONSTRAINT_TYPE,
	a.R_CONSTRAINT_NAME
order by
	a.TABLE_NAME,
	a.CONSTRAINT_NAME;

三、查询表的索引

select
	a.table_name,
	a.index_name,
	a.index_type,
	b.column_name,
	a.uniqueness as isuniqueness,
	a.partitioned as ispartition,
	a.tablespace_name
from
	user_indexes a,
	user_ind_columns b
where
	a.index_name = b.index_name
order by
	table_name,
	index_name;

四、查询表分区情况

select
	table_name,
	partitioned as ispartitioned,
	'非分区' as partitioning_type
from
	user_tables
where
	table_name not in(
		select
			table_name from user_part_tables)
union
select
	t.table_name,
	t.partitioned as ispartitioned,
	p.partitioning_type
from
	user_tables t,
	user_part_tables p
where
	t.table_name = p.table_name;

五、查询全表扫描的语句

--查找SQL_ID
SELECT *
FROM v$sql_plan v
WHERE v.OPERATION = 'TABLE ACCESS'
	AND v.OPTIONS = 'FULL'
	AND v.OBJECT_OWNER = 'XXX';--查询指定用户
 
--根据SQL_ID查找SQL_TEXT
SELECT p.SQL_TEXT
	,p.SQL_ID
	,q.TIMESTAMP
FROM v$sqlarea p
	,v$sql_plan q
WHERE p.SQL_ID = q.SQL_ID
	AND q.OPERATION = 'TABLE ACCESS'
	AND q.OPTIONS = 'FULL'
	AND q.OBJECT_OWNER = 'XXX'--查询指定用户
	AND q.TIMESTAMP > sysdate - 1;

五、查询全表扫描的语句

--表统计信息收集
begin
	dbms_stats.gather_table_stats('USER','TABLE_NAME',cascade => true);
end;
/

推荐阅读