爱的光芒 - TeamBlog

RANK() Over用法

select * from employee

ID name salary start_date city region
----------- ---------- ----------- ----------------------- ---------- ------
1 Jason 40420 1994-02-01 00:00:00.000 New York W
2 Robert 14420 1995-01-02 00:00:00.000 Vancouver N
3 Celia 24020 1996-12-03 00:00:00.000 Toronto W
4 Linda 40620 1997-11-04 00:00:00.000 New York N
5 David 80026 1998-10-05 00:00:00.000 Vancouver W
6 James 70060 1999-09-06 00:00:00.000 Toronto N
7 Alison 90620 2000-08-07 00:00:00.000 New York W
8 Chris 26020 2001-07-08 00:00:00.000 Vancouver N
9 Mary 60020 2002-06-09 00:00:00.000 Toronto W

(9 rows affected)

SELECT ID, Name, RANK() Over (ORDER BY Name) As Rank
FROM Employee
ORDER BY Name

ID Name Rank
----------- ---------- -------------- ...

文章内容未全部显示，请点击查看更多内容

takken @ 2007-01-08 13:44 | 评论 (0) | 小命令 | 无标签 | 加入收藏

找ORACLE字符集

select * from sys.props$ where name='NLS_CHARACTERSET';

takken @ 2007-01-05 15:49 | 评论 (0) | 小命令 | 无标签 | 加入收藏

查看碎片程度高的表

SELECT segment_name table_name , COUNT(*) extents
FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment_name
HAVING COUNT(*) = (SELECT MAX( COUNT(*) ) FROM dba_segments GROUP BY segment_name);

takken @ 2007-01-05 15:48 | 评论 (0) | 小命令 | 无标签 | 加入收藏

定位最大表

prompt Top 20 size of Segments:

column Owner format a15 heading 'Owner'
column SegmentName format a40 heading 'Object Name'
column SegmentType format a9 heading 'Segment|Type'
column BytesUsed format 999,999,999 heading 'Size (K)'
column Blocks format 99,999,999 heading 'Blocks'
column ExtentNumber format 999,999 heading 'Extent|Number'

select *
from (select
owner Owner,
segment_name SegmentName,
segment_type SegmentType,
bytes / 1024 BytesUsed,
blocks Blocks,
extents ExtentNumber
from dba_segments
where owner not in ('SYS', 'SYSTEM', 'OUTLN')
order by blocks desc)
where rownum < 21;

takken @ 2007-01-04 16:24 | 评论 (0) | 小命令 | 无标签 | 加入收藏

定位表所属数据文件

select b.tablespace_name,b.file_name from dba_extents a ,dba_data_files b where a.owner='table_owner' and a.segment_name='table_name' and a.file_id=b.file_id

takken @ 2007-01-04 15:53 | 评论 (0) | 小命令 | 无标签 | 加入收藏