Routine Oracle database check methods and procedures

Routine Oracle database check methods and steps 1. Check the partition information <! -- [If! SupportLists] --> 1. <! -- [Endif] --> check which tables have partitions

Select * from user_tables where partitioned=’YES’;

 

2. Check and list all secondary partitions in the current table

select * from user_tab_subpartitions t where t.table_name = upper('tablename');

 

3. Check and list all partitions in the current table

select * from user_tab_partitions t where t.table_name = upper('tablename');

 

Ii. Methods for viewing and modifying character sets 1. Viewing character sets

select userenv('language') from dual;

 

<! -- [If! SupportLists] --> 2. <! -- [Endif] --> bytes used by Chinese Characters

Select lengthb ('you') from dual;

 

3. Character Set Modification

Oralce code 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

 

Note: When you execute alter database character set ZHS16GBK, the system prompts our character set: the new character set must be the superset of the old character set. In this case, we can skip the superset check and make changes:

SQL> ALTER DATABASE character set INTERNAL_USE ZHS16GBK; 

 

-- We can see that this process is exactly the same as the internal process of the alter database character set operation. That is to say, the help provided by INTERNAL_USE is that the Oracle DATABASE bypasses the validation of the subset and superset. iii. Table space usage 1. view the current table space usage

Oracle code select. tablespace_name,. bytes/1024/1024 "Sum MB", (. bytes-B. bytes)/1024/1024 "used MB", B. bytes/1024/1024 "free MB", round (. bytes-B. bytes)/. 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. tablespace_name = B. tablespace_name order by (. bytes-B. bytes)/. bytes) desc