Oracle數(shù)據(jù)庫的配置和結構等信息

熟悉數(shù)據(jù)庫系統(tǒng)，獲取Oracle數(shù)據(jù)庫的配置和結構等信息1.獲取Oracle數(shù)據(jù)庫的名字、創(chuàng)建日期等SQL>selectname,created,log_mode,open_modefromv$database;NAMECREATED LOG_MODEOPEN_MODE—ORCL21-6月-11ARCHIVELOGREADWRITE2.獲取運行Oracle數(shù)據(jù)庫的計算機的主機名、Oracle數(shù)據(jù)庫的實例名、Oracle數(shù)據(jù)庫管理系統(tǒng)的版本SQL>colhost_namefora15SQL>selecthost_name,instance_name,versionfromv$instance;HOST_NAMEINSTANCE_NAMEVERSIONFALA orcl 10.2.0.1.03.獲取Oracle數(shù)據(jù)庫管理系統(tǒng)的詳細信息SQL>select*fromv$version;BANNEROracleDatabase10gEnterpriseEditionRelease10.2.0.1.0-ProdPL/SQLRelease10.2.0.1.0-ProductionCORE10.2.0.1.0ProductionTNSfor32-bitWindows:Version10.2.0.1.0-ProductionNLSRTLVersion10.2.0.1.0-Production4.獲取控制文件的名字SQL>colnamefora60SQL>selectnamefromv$controlfile;NAMEF:\ORACLE\PRODUCT\1020\ORADATA\ORCL\CONTROL01.CTLF:\ORACLE\PRODUCT\1020\ORADATA\ORCL\CONTROL02.CTLF:\ORACLE\PRODUCT\1020\ORADATA\ORCL\CONTROL03.CTL5.獲取Oracle重做日志的配置信息SQL>selectgroup#,members,bytes,status,archivedfromv$log;GROUP#MEMBERS BYTESSTATUSARC■1152428800CURRENTNO2152428800INACTIVEYES3152428800INACTIVEYES6.查看重做日志組（成員）文件所存放的具體位置SQL>colmemberfora48

SQL>select*fromv$logfile;ISGROUP#STATUSTYPEMEMBERISONLINE F:\ORACLE\PRODUCT\1020\ORADATA\ORCL\REDO01.LOGNO3STALEONLINEF:\ORACLE\PRODUCT\1020\ORADATA\ORCL\REDO03.LOGNOONLINE F:\ORACLE\PRODUCT\1020\ORADATA\ORCL\REDO02.LOGNO7.查看Oracle歸檔信息、SQL>archiveloglist數(shù)據(jù)庫日志模式自動存檔存檔終點最早的聯(lián)機日志序列下一個存檔日志序列當前日志序列存檔模式啟用F:\disk3\offlinelog\optional8278298298.查看數(shù)據(jù)庫有多少個表空間SQL>selecttablespace_name,block_size,status,contents,loggingfromdba_tablespaces;CONTENTSLOGGINGTABLESPACE_NAMEBLOCK_SIZESTATUSCONTENTSLOGGINGSYSTEM8192ONLINEPERMANENTLOGGINGUNDOTBS18192ONLINEUNDO LOGGINGSYSAUX8192ONLINEPERMANENTLOGGINGTEMP8192ONLINETEMPORARYNOLOGGINGUSERS8192ONLINEPERMANENTLOGGINGEXAMPLE8192ONLINEPERMANENTNOLOGGINGKANTO8192ONLINEPERMANENTLOGGINGPIONEER_DATA8192ONLINEPERMANENTLOGGINGDJK8192ONLINEPERMANENTLOGGINGTABTEST 8192ONLINEPERMANENTLOGGING9.查看每個表空間存在哪個磁盤上以及文件的名字等信息SQL>colfile_namefora51SQL>selectfile_id,file_name,tablespace_namefromdba_data_files;FILE_IDFILE_NAME TABLESPACE_NAME5F:\ORACLE\PRODUCT\1020\ORADATA\ORCL\EXAMPLE01.DBFEXAMPLE4F:\ORACLE\PRODUCT\1020\ORADATA\ORCL\USERS01.DBF USERSF:\ORACLE\PRODUCT\1020\ORADATA\ORCL\SYSAUX01.DBFSYSAUX2F:\ORACLE\PRODUCT\1020\ORADATA\ORCL\UNDOTBS01.DBFUNDOTBS1F:\ORACLE\PRODUCT\1020\ORADATA\ORCL\SYSTEM01.DBFSYSTEMF:\ORACLE\PRODUCT\1020\ORADATA\ORCL\DJK.DBF DJKF:\DISK1\ORATABLESPACE\TABTEST_DATA.DBF TABTEST6F:\DISK1\ORATABLESPACE\TABLESPACE_D；TA.DBF KANTOF:\DISK1\ORATABLESPACE\PJL_DATA.DBF PIONEER_DATAF:\DISK4\MOON\PIONEER_INDEX.DBF PIONEER_INDEX12F:\DISK7\MOON\JINLIAN_UNDO1.DBF10.查看Oracle數(shù)據(jù)庫上有哪些用戶和什么時間創(chuàng)建的SQL>selectusername,createdfromdba_users;JINLIAN_UNDO1USERNAMETESTDJKPJINLIAN