Oracle数据库日常检查方法和步骤
Oracle数据库日常检查方法和步骤
一、检查表分区情况
<!--[if !supportLists]-->1、<!--[endif]-->检查哪些表有分区
Select * from user_tables where partitioned=’YES’;
2、检查并列出当前表所有辅助分区
select * from user_tab_subpartitions t where t.table_name = upper('tablename');
3、检查并列出当前表的所有分区
select * from user_tab_partitions t where t.table_name = upper('tablename');
二、字符集查看和修改方法
1、字符集查看
select userenv('language') from dual;
<!--[if !supportLists]-->2、<!--[endif]-->汉字占用的字节长度
select lengthb('你') from dual;
3、字符集修改
Oralce代码 conn /as sysdba shutdown immediate; startup mount; ALTER SYSTEM ENABLE RESTRICTED SESSION; ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; ALTER SYSTEM SET AQ_TM_PROCESSES=0; alter database open; ALTER DATABASE CHARACTER SET ZHS16GBK; select * from v$nls_parameters; select userenv('language') from dual; shutdown immediate; startup
注意:
在执行ALTER DATABASE CHARACTER SET ZHS16GBK;时
提示我们的字符集:新字符集必须为旧字符集的超集,这时我们可以跳过超集的检查做更改:
SQL> ALTER DATABASE character set INTERNAL_USE ZHS16GBK;
--我们看到这个过程和之前ALTER DATABASE CHARACTER SET操作的内部过程是完全相同的,也就是说INTERNAL_USE提供的帮助就是使Oracle数据库绕过了子集与超集的校验.
三、表空间使用情况
1、查看当前表空间使用情况
Oracle代码 select a.tablespace_name, a.bytes / 1024 / 1024 "Sum MB", (a.bytes - b.bytes) / 1024 / 1024 "used MB", b.bytes / 1024 / 1024 "free MB", round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "percent_used" from (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a, (select tablespace_name, sum(bytes) bytes, max(bytes) largest from dba_free_space group by tablespace_name) b where a.tablespace_name = b.tablespace_name order by ((a.bytes - b.bytes) / a.bytes) desc