ORACLE資料庫常用查詢二

標籤：des   style   blog   ar   color   os   使用   sp   strong   

ORACLE資料庫常用查詢

1、查看錶空間對應資料檔案情況：

SQL> SELECT TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024 MB,AUTOEXTENSIBLE FROM DBA_DATA_FILES;TABLESPACE_NAME FILE_NAME MB AUTOEXTENSIBLE------------------------------ -------------------------------------------------------------- ---- --------------USERS D:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF 5 YESUNDOTBS1 D:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF 85 YESSYSAUX D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF 520 YESSYSTEM D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF 690 YESEXAMPLE D:\APP\ADMINISTRATOR\ORADATA\ORCL\EXAMPLE01.DBF 100 YES

2、查看剩餘資料表空間大小

SQL> SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 MB FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;TABLESPACE_NAME MB------------------------------ ----------SYSAUX 30.875UNDOTBS1 71.625USERS 0.9375SYSTEM 3.4375EXAMPLE 21.25

3、資料表空間使用統計

SQL> SELECT DF.TABLESPACE_NAME "資料表空間名",TOTALSPACE "總空間 MB",FREESPACE "剩餘空間 MB",ROUND((1-FREESPACE/TOTALSPACE)*100,2) "作用率" FROM2 (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/1024/1024) TOTALSPACE FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) DF,3 (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/1024/1024) FREESPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) FS4 WHERE DF.TABLESPACE_NAME = FS.TABLESPACE_NAME ORDER BY DF.TABLESPACE_NAME;資料表空間名 總空間 MB 剩餘空間 MB 作用率------------------------------ ---------- ----------- ----------EXAMPLE 100 21 79SYSAUX 520 31 94.04SYSTEM 690 3 99.57UNDOTBS1 85 72 15.29USERS 5 1 80

4、查看資料庫相關檔案的位置
查看控制檔案、初始化參數檔案、查看警示記錄檔位置：

SQL> SHOW PARAMETER CONTROL;NAME TYPE VALUE------------------------------------ ----------- ------------------------------control_file_record_keep_time integer 7control_files string D:\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL01.CTL, D:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\CONTROL02.CTLcontrol_management_pack_access string DIAGNOSTIC+TUNINGSQL> SHOW PARAMETER SPFILE;NAME TYPE VALUE------------------------------------ ----------- ------------------------------spfile string D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILEORCL.ORASQL> SHOW PARAMETER DUMP;NAME TYPE VALUE------------------------------------ ----------- ------------------------------background_core_dump string partialbackground_dump_dest string d:\app\administrator\diag\rdbms\orcl\orcl\tracecore_dump_dest string d:\app\administrator\diag\rdbms\orcl\orcl\cdumpmax_dump_file_size string unlimitedshadow_core_dump string noneuser_dump_dest string d:\app\administrator\diag\rdbms\orcl\orcl\trace

查看資料檔案的位置：

SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;FILE_NAME--------------------------------------------------------------------------------D:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBFD:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBFD:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBFD:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBFD:\APP\ADMINISTRATOR\ORADATA\ORCL\EXAMPLE01.DBFSQL> SELECT FILE_NAME FROM DBA_TEMP_FILES;FILE_NAME--------------------------------------------------------------------------------D:\APP\ADMINISTRATOR\ORADATA\ORCL\TEMP01.DBF

6、查看使用者相關資訊

SQL> SELECT USERNAME FROM DBA_USERS;USERNAME------------------------------SYSSYSTEMSYSMANSCOTTHR……=================SQL> ALTER USER HR IDENTIFIED BY rusky ACCOUNT LOCK;User alteredSQL> ALTER USER HR ACCOUNT UNLOCK;User alteredSQL> ALTER USER SCOTT IDENTIFIED BY scott;User altered

===================
查使用者對像：
select * from user_objects;
查看使用者表資訊：
select * from user_tables;
查看使用者索引資訊：
select * from user_indexes;
查看使用者序列資訊：
select * from user_sequences;
查看使用者視圖資訊：
select * from user_views;
查看使用者觸發器資訊：
select * from user_trigers;--以sys登入
查看使用者權限：
SQL> conn scott/tiger;
已串連。
SQL> select * from user_sys_privs;

USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
SCOTT UNLIMITED TABLESPACE NO
=======================

7、查看及修改使用者串連數

SQL> SHOW PARAMETER PROCESS;NAME TYPE VALUE------------------------------------ ----------- ------------------------------aq_tm_processes integer 0cell_offload_processing boolean TRUEdb_writer_processes integer 1gcs_server_processes integer 0global_txn_processes integer 1job_queue_processes integer 1000log_archive_max_processes integer 4processes integer 150SQL> ALTER SYSTEM SET PROCESSES=200 SCOPE=SPFILE;System altered重啟生效SQL> SHUTDOWN IMMEDIATESQL> STARTUP

8、給資料庫增加日誌組

SQL> SELECT GROUP#,THREAD#,BYTES,MEMBERS,STATUS FROM V$LOG;GROUP# THREAD# BYTES MEMBERS STATUS---------- ---------- ---------- ---------- ----------------1 1 52428800 1 CURRENT2 1 52428800 1 INACTIVE3 1 52428800 1 INACTIVESQL> ALTER DATABASE ADD LOGFILE GROUP 4 ‘D:\testAddLog.log‘ SIZE 2M;ALTER DATABASE ADD LOGFILE GROUP 4 ‘D:\testAddLog.log‘ SIZE 2MORA-00336: 大小為 4096 的記錄檔塊數小於最小 8192 塊數SQL> ALTER DATABASE ADD LOGFILE GROUP 4 ‘D:\testAddLog.log‘ SIZE 8M;Database altered

日誌組會影響資料庫的效能，根據業務情況設定合適的日誌組大小和組數。

ORACLE資料庫常用查詢二