常用Oracle管理语句.doc

例：查询约束信息
SQL> SELECT * FROM user_constraints;
SQL> SELECT * FROM user_cons_columns;

例：查询表空间块的大小
SQL>SELECT tablespace_name,block_size segment_type FROM dba_segments;
例：查询当前段的类型
SQL>SELECT distinct segment_type FROM dba_segments;
例：查询scott的emp表的段信息
SQL>SELECT * FROM dba_segments
2>WHERE owner=’SCOTT’ and segment_name=’EMP’;
例：查询scott的emp表的区信息
SQL>SELECT * FROM dba_extents
2>WHERE owner=’SCOTT’ and segment_name=’EMP’;

例：查询设置shared pool 参数 SQL>SHOW PARAMETER shared_pool_size SQL>SELECT * FROM v$parameter WHERE parameter=’shared_pool_size’;

例：查询当前shared pool大小 SQL>SELECT * FROM v$sgastat WHERE pool=’shared pool’;

例：查询设置db buffer cache参数
SQL>SHOW PARAMETER cache_size
SQL>SELECT * FROM v$parameter WHERE parameter like ‘%cache_size%;

例：查询当前db buffer cachel大小
SQL>SELECT * FROM v$sgastat WHERE name=’buffer cache’;
SQL>SELECT * FROM v$buffer_pool;

例：查询设置db buffer cache参数
SQL>SHOW PARAMETER log_buffer
SQL>SELECT * FROM v$parameter WHERE parameter=’log_buffer’;
例：查询当前log buffer大小
SQL>SELECT * FROM v$sgastat WHERE name=’log buffer’;