ORACLE常用命令

以前学oracle的时候摘录的，仅作为参考。

http://blog.chinaunix.net/u/10516/showart_586296.html

1.SQL> show sga

Total System Global Area 2384169936 bytes
Fixed Size                   744400 bytes
Variable Size             805306368 bytes
Database Buffers         1577058304 bytes
Redo Buffers                1060864 bytes

2.SQL> col name format a20
SQL> col value format a20
SQL> select name,value from v$parameter where name in
2   ('db_cache_size','db_cache_advice','sga_max_size');

NAME                 VALUE
-------------------- --------------------
sga_max_size         2384169936
db_cache_size        1577058304
db_cache_advice      ON

3.SQL> show parameter block_size

4.SQL> select tablespace_name,block_size from dba_tablespaces;

TABLESPACE_NAME                                              BLOCK_SIZE
------------------------------------------------------------ ----------
SYSTEM                                                            16384
UNDOTBS1                                                          16384
TEMP                                                              16384
BLOBS                                                             16384
INDX                                                              16384
TOOLS                                                             16384
USERS                                                             16384

已选择7行。

5.SQL> select * from v$sga_dynamic_components;

COMPONENT
----------------------------------------------------------------------------

CURRENT_SIZE   MIN_SIZE   MAX_SIZE OPER_COUNT LAST_OPER_TY LAST_OPER_MO
------------ ---------- ---------- ---------- ------------ ------------
LAST_OPER_ GRANULE_SIZE
---------- ------------
shared pool
   536870912 536870912 536870912          0
               16777216

large pool
   117440512 117440512 117440512          0
               16777216

COMPONENT
----------------------------------------------------------------------------

CURRENT_SIZE   MIN_SIZE   MAX_SIZE OPER_COUNT LAST_OPER_TY LAST_OPER_MO
------------ ---------- ---------- ---------- ------------ ------------
LAST_OPER_ GRANULE_SIZE
---------- ------------

buffer cache
1577058304 1577058304 1577058304          0
               16777216

SGA大小                       区组
<128MB                        4MB
>=128MB                       16MB
注：Oracle9i中的区组是内存单位。如果GRANULE_SIZE的大小与上表不符合则会产生著名的ORACLE故障。

6.SQL>select block_size,name,size_for_estimate                                   cache_size,buffers_for_estimate,estd_physical_read_factor
      ,estd_physical_reads from v$db_cache_advice where advice_status='ON'
      order by 1,2,3
注：查询显示每个数据块大小和每一个缓冲区池的估计物理读取。

7.SQL>col ration heading "buffer cache hit ratio" format a30
SQL>select round((1-(PHY.VALUE/(CUR.VALUE+CON.VALUE)))*100,1)||'%' ratio
      from v$sysstat PHY,v$sysstat CUR,v$sysstat CON
      where PHY.NAME='physical reads'
      and CUR.NAME='db block gets'
      and CON.NAME='consistent gets'
注：查看高速缓冲区的命中率： 这个比例的阀值是90%

8.SQL>select O.OWNER,O.OBJECT_TYPE,O.OBJECT_NAME,COUNT(*) buffers,ROUND((COUNT(*)/(SELECT COUNT(*)
      FROM V$BH))*100) BUFFER_PERCENT FROM DBA_OBJECTS O,V$BH B
      WHERE O.OBJECT_ID=B.OBJD AND O.OWNER NOT IN ('SYS','SYSTEM')
      GROUP BY O.OWNER,O.OBJECT_TYPE,O.OBJECT_NAME
      ORDER BY 1,2 DESC
注：查询缓冲区中驻留的一些数据对象的视图和正在使用的数据块的数量。

9.SQL>SELECT DECODE(STATUS,'free','Free: not currently in use',
                           'xcur','Locked: exclusive',
                           'scur','Locked:shared',
                           'cr','Consistent Read',
                           'read','Being read from disk',
                           'mrec','In media recovery mode',
                           'irec','In instance recovery mode',
                           'Total Buffers') STATUS,
               COUNT(*) BUFFERS
         FROM V$BH
    GROUP BY ROLLUP(STATUS)
注：获得用于不同用途和缓存类型的缓冲区数量。Total Buffers是高速缓冲区的数量。

10.SQL> show parameters area_size
注：查看UGA各参数的大小。

11.查看共享池的命中率：
   SQL>select sum(pinhits)/sum(pins)*100 "hit radio" from v$librarycache

12.排序部分：
   SQL>select name,value from v$sysstat where name like '%sort%'
如果sort (disk)/(sorts(memory)+sorts(disk))比例过高，则意味sort_area_size部分内存较小。

13.log_buffer:
   SQL>select name,value from v$sysstat where name in ('redo entries','redo buffer allocation retries')
假如redo buffer allocation retries/redo entries的比例超过1%则可以考虑增大log_buffer

14.通过查询数据字典v$datafile,v$database来确认文件的SCN相同：
   a. select group#,sequence#,status,first_change# from v$log
   b. select file#,checkpoint_change#,name from v$datafile
   c. select name,dbid,checkpoint_change# from v$database

15.查看某个数据文件所包含的对象：
select owner,segment_name,sum(bytes) from dba_extents where file_id=3
group by owner,segment_name
order by 3 desc;

16.查询某个表的大小：
Select segment_name,sum(bytes)/1024 ||'KB' from user_segments
group by segment_name having segment_name='table_name';

17.查询占用空间最大的前十个表：
select * from
(select segment_name,owner,tablespace_name,bytes/1024/1024 as "Size(M)"
from dba_segments
order by 4 desc
)
where rownum <11;

18.怎样计算一个表占用的空间的大小？
select owner,table_name,NUM_ROWS,BLOCKS*AAA/1024/1024 "Size M",EMPTY_BLOCKS,
LAST_ANALYZED from dba_tables where table_name='XXX';

Here: AAA is the value of db_block_size ;
XXX is the table name you want to check

19.如何查看最大会话数？
SQL> show parameter processes
NAME TYPE VALUE
------------------------------------ ------- ------------------------------
aq_tm_processes integer 1
db_writer_processes integer 1
job_queue_processes integer 4
log_archive_max_processes integer 1
processes integer 200

这里为200个用户。
select * from v$license;
其中sessions_highwater纪录曾经到达的最大会话数

20.查看PGA使用了多少内存？
select sum(PGA_USED_MEM) from v$process;

21.如何查看系统被锁的事务时间？
select * from v$locked_object;

22.查询锁的狀況的对象有?
V$LOCK, V$LOCKED_OBJECT, V$SESSION, V$SQLAREA, V$PROCESS ;
查询锁的表的方法:
SELECT S.SID SESSION_ID, S.USERNAME, DECODE(LMODE, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR(LMODE)) MODE_HELD, DECODE(REQUEST, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR(REQUEST)) MODE_REQUESTED, O.OWNER||'.'||O.OBJECT_NAME||' ('||O.OBJECT_TYPE||')', S.TYPE LOCK_TYPE, L.ID1 LOCK_ID1, L.ID2 LOCK_ID2 FROM V$LOCK L, SYS.DBA_OBJECTS O, V$SESSION S WHERE L.SID = S.SID AND L.ID1 = O.OBJECT_ID ;

23.如何解锁?
ALTER SYSTEM KILL SESSION ‘SID,SERIR#’;

24.如何才能得知系统当前的SCN号 ?
select max(ktuxescnw * power(2, 32) + ktuxescnb) from x$ktuxe;

25.查看数据库字符狀況?
SELECT * FROM NLS_DATABASE_PARAMETERS;
SELECT * FROM V$NLS_PARAMETERS;

26. 如何获取錯誤信息?
SELECT * FROM USER_ERRORS;

27.查询当前用戶對象?
SELECT * FROM USER_OBJECTS;
SELECT * FROM DBA_SEGMENTS;

28.如何在字符串里加回车？
    select 'Welcome to visit'||chr(10)||'www.CSDN.NET' from dual ;

29.怎样修改oracel数据库的默认日期?
   alter session set nls_date_format='yyyymmddhh24miss';
   OR
   可以在init.ora中加上一行 nls_date_format='yyyymmddhh24miss'

30.如何将小表放入keep池中?
alter table xxx storage(buffer_pool keep);

31.如何知道数据裤中某个表所在的tablespace?
select tablespace_name from user_tables where table_name='TEST';

32.怎么可以快速做一个和原表一样的备份表?
create table new_table as (select * from old_table);

33.请问如何修改一张表的主键?
alter table aaa
drop constraint aaa_key ;
alter table aaa
add constraint aaa_key primary key(a1,b1) ;

34.如何在PL/SQL中读写文件？
UTL_FILE包允许用户通过PL/SQL读写操作系统文件。

35.怎樣把“&”放入一條記錄中？
insert into a values (translate ('at{&}t','at{}','at'));

36.如何創建SPFILE/pfile?
SQL> connect / as sysdba
SQL> select * from v$version;
SQL> create pfile from spfile;
SQL> CREATE SPFILE FROM PFILE='E:\ora9i\admin\eygle\pfile\init.ora';
文件已创建。
SQL> CREATE SPFILE='E:\ora9i\database\SPFILEEYGLE.ORA' FROM PFILE='E:\ora9i\admin\eygle\pfile\init.ora';
文件已创建。

37.怎样查看哪些用户拥有SYSDBA、SYSOPER权限？
SQL>conn sys/change_on_install
SQL>select * from V_$PWFILE_USERS;

38.如何快速清空一個大表?
SQL>truncate table table_name;

39.如何查有多少個數据库实例?
SQL>SELECT * FROM V$INSTANCE;

40.如何查询數据库有多少表?
SQL>select * from all_tables;

41.如何測試SQL語句執行所用的時間?
SQL>set timing on ;
SQL>select * from **;

42.CHR()的反函數是?
    ASCII()
    SELECT CHAR(65) FROM DUAL;
    SELECT ASCII('A') FROM DUAL;

43.字符串的連接
    SELECT CONCAT(COL1,COL2) FROM TABLE ;
    SELECT COL1||COL2 FROM TABLE ;

44.怎么把select出来的结果导到一个文本文件中？
    SQL>SPOOL C:\ABCD.TXT;
    SQL>select * from table;
    SQL >spool off;

45.如何在sqlplus下改变字段大小?
alter table table_name modify (field_name varchar2(100));
改大行，改小不行（除非都是空的）

46.如何取得命令的返回状态值？
sqlcode=0

47.如何知道用户拥有的权限?
    SELECT * FROM dba_sys_privs ;

48. sql>startup pfile和ifile,spfiled有什么区别？
pfile就是Oracle传统的初始化参数文件，文本格式的。
ifile类似于c语言里的include，用于把另一个文件引入
spfile是9i里新增的并且是默认的参数文件，二进制格式
startup后应该只可接pfile

49.如何统计两个表的记录总数?
select (select count(id) from aa)+(select count(id) from bb) 总数 from dual;

50.怎样用Sql语句实现查找一列中第N大值？
   select * from
(select t.*,dense_rank() over (order by sal) rank from employee)
where rank = N;

51.如何在给现有的日期加上2年？
    select add_months(sysdate,24) from dual;
   返回當前月的最后一天?
SELECT LAST_DAY(SYSDATE) FROM DUAL;

52. 返回大于等于N的最小整數值?
    SELECT CEIL(N) FROM DUAL;
    返回小于等于N的最大整數值?
    SELECT FLOOR(N) FROM DUAL;

53.获得表空间的剩余空间。
SELECT tablespace_name, sum ( blocks ) as free_blk ,  
trunc ( sum ( bytes ) /(1024*1024) ) as free_m,  
max ( bytes ) / (1024) as big_chunk_k, count (*) as num_chunks  
FROM dba_free_space GROUP BY tablespace_name;