oracle_11g_学习笔记.doc

1、常用oracle参数查询 1、查看控制文件 select name from v$controlfile; 2、查看日志文件 select member from v$logfile; 3、查看表空间的使用情况 select sum(bytes)/(1024*1024) as free_space_MB,tablespace_name from dba_free_space group by tablespace_name; SELECT A.TABLESPACE_NAME,A.BYTES/(1024*1024*1024) TOTAL_GB,B.BYTES/(1024*1024*1024) USED_GB, C.BYTES/(1024*1024*1024) FREE_GB, (B.BYTES*100)/A.BYTES % USED,(C.BYTES*100)/A.BYTES % FREE FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME= C.TABLESPACE_NAME; 4、查看数据库库对象 select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status; 5、查看数据库的版本 Select version FROM Product_component_version Where SUBSTR(PRODUCT,1,6)=Oracle; 6、查看数据库的创建日期和归档方式 Select Created, Log_Mode, Log_Mode From V$Database; 7、捕捉运行很久的SQL column username format a12 column opname format a16 column progress format a8 select username,sid,opname, round(sofar*100 / totalwork,0)||% as progress, time_remaining,sql_text from v$session_longops , v$sql where time_remaining 0 and sql_address = address and sql_hash_value = hash_value / 8、查看数据表的参数信息 SELECT partition_name, high_value, high_value_length, tablespace_name, pct_free, pct_used, ini_trans, max_trans, initial_extent, next_extent, min_extent, max_extent, pct_increase, FREELISTS, freelist_groups, LOGGING, BUFFER_POOL, num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len, sample_size, last_analyzed FROM dba_tab_partitions --WHERE table_name = :tname AND table_owner = :towner ORDER BY partition_position 9、查看还没提交的事务 select * from v$locked_object; select * from v$transaction; 10、查找object为哪些进程所用 select p.spid, s.sid, s.serial# serial_num, s.username user_name, a.type object_type, s.osuser os_user_name, a.owner, a.object object_name, decode(sign(48 - command), 1, to_char(command), Action Code # to_char(command