Oracle Study Notes: System Structure Query

■ Check the tablespace in which Scott uses the data segment to store and how many partitions there are:

SQL> select segment_name, tablespace_name, bytes, blocks from user_extents;

■ Query Scott's index information:

SQL> select index_name, table_owner, table_name, tablespace_name from user_indexes;

■ Check whether the automatic revocation management function is used

SQL> select value from V $ parameter where name = 'undo _ management ';

Value

---------------------------------------------------------------------------

Auto

SQL>

The above query result is auto, indicating that the automatic revocation management function is used. If you want to use the rollback segment function,

You must set the undo_management parameter to manual and restart the database.

■ Query the cache size of redo logs

SQL> show parameter log_buffer;

■ Use the data dictionary v $ sysstat to query the number of times that the user process is waiting to redo the log Cache

SQL> select name, value from V $ sysstat;

■ Query the cache size of the Shared Pool

SQL> show parameter shared_pool_size;

■ Query the number of successful and failed data dictionary caches in the shared pool through the data dictionary v $ rowcache

SQL> select sum (gets), sum (getmisses) from V $ rowcache;

Gets --- indicates the number of times the dictionary is successfully read.

Getmisses --- indicates the number of times a dictionary fails to be read.

■ Query the cache size of a large pool

SQL> show parameter large_pool_size;

■ Dynamically change the size of a large pool

SQL> alter system set large_pool_size = 20 m;

■ View the cache size of the Java pool (generally no less than 20 mb for Java Virtual Machine installation)

SQL> show parameter java_pool_size;

■ View the size of the PGA sorting area

SQL> show parameter sort_area_size;

■ View the size of the PGA cursor Area

SQL> show parameter open_cursors;

You can set the initialization parameter open_cursors to limit the number of cursors that can be opened at the same time.

■ Query the data dictionary view v $ bgprocess to check the number and name of background processes started in the database

SQL> select * from V $ bgprocess

■ The two parameters related to the checkpoint interval are also the time or conditions required to trigger the database checkpoint.

SQL> show parameter log_checkpoint _ %

Name type value

-----------------------------------------------------------------

Log_checkpoint_interval integer 0

Log_checkpoint_timeout integer 1800

log_checkpoint_interval --- specify a certain number of operating system data blocks (not ORACLE data blocks) when the redo log file is written,

A checkpoint is triggered. check Points will appear when switching to redo log files regardless of the value of this parameter.

log_checkpoint_timeout --- specifies the maximum interval (in seconds) for checkpoint execution ), disabled when set to 0.

be careful to use these two parameters

when a redo log file is full, a checkpoint is triggered, therefore, the number of operating system blocks set by log_checkpoint_interval must match the size of

redo log files.

■ Check whether the archiving process is started

SQL> show parameter log_archive_start

If log_archive_start is set to false, the arch process will not be started even if the database is running in archive mode. In this case, when the log file is redone

When all data is fully written, the database will be suspended and the DBA will manually archive the data.

■ Query information of all solution objects created by the user

SQL> select object_name, object_type from user_objects;

■ Nomount status

V $ parameter V $ SGA

V $ Option V $ Process

V $ Session V $ version

V $ instance

■ Mount status

V $ thread v $ controlfile

V $ database v $ datafile

V $ datafile_header v $ logfile

■ Open status

V $ filestat v $ session_wait

V $ watistat