oracle理论
rownum是根据sql查询出的结果给每行分配一个逻辑编号，所以你的sql不同也就会导致最终rownum不同，但是rowid是物理结构上的，在每条记录insert到数据库中时，都会有一个唯一的物理记录
数据格式查询
1例子
select result, to_char(result, '9999.99') as "9的位数不足", to_char(result, '9999999999.99') as "9的位数充足", to_char(result, '0000.00') as "0的位数不足", to_char(result, '0000000000.00') as "0的位数充足", to_char(result, '9999999990.00') as "0的位数充足", trim(to_char(result, '9999999990.00')) as "9的位数充足且去掉左侧" from ( select 0 result from dual union select 1 result from dual union select 123 result from dual union select 123.4 result from dual union select 12345.67 result from dual union select 12345678.2 result from dual )
2例子
select result,
to_char(result * 100, '99.99') || '%' as "9位数不足",
to_char(result * 100, '00.99') || '%' as "0位数不足",
to_char(result * 100, '99990.99') || '%' as "百分比",
trim(to_char(result * 100, '99990.99')) || '%' as "去左侧空格的百分比",
to_char(result * 100, 'fm99990.99') || '%' as "使用fm前缀的格式百分比",
to_char(result * 100, 'fm99990.00') || '%' as "使用fm前缀的格式百分比2"
from (
select 0 result from dual union
select 1 result from dual union
select 0.12345 result from dual union
select 0.2 result from dual union
select 0.23 result from dual union
select 0.234 result from dual union
select 0.2345 result from dual union
select 0.23454 result from dual union
select 0.23456 result from dual
)
打印一个单引号''''使用四个单引号
有两种含义的表大小。一种是分配给一个表的物理空间数量，而不管空间是否被使用。可以这样查询获得字节数：
select segment_name, bytes
from user_segments
where segment_type = 'TABLE';
或者
Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name
另一种表实际使用的空间。这样查询：
analyze table emp compute statistics;
select num_rows * avg_row_len
from user_tables
where table_name = 'EMP';
查看每个表空间的大小
Select Tablespace_Name,Sum(bytes)/1024/1024 From Dba_Segments Group By Tablespace_Name
1.查看剩余表空间大小
SELECT tablespace_name 表空间,sum(blocks*8192/1000000) 剩余空间M FROM dba_free_space GROUP BY tablespace_name;
2.检查系统中所有表空间总体空间
select b.name,sum(a.bytes/1000000)总空间 from v$datafile a,v$tablespace b where a.ts#=b.ts# group by b.name;
1.查询oracle表空间的使用情况
select b.file_id 文件ID,
b.tablespace_name 表空间,
b.file_name 物理文件名,
b.bytes 总字节数,
(b.bytes-sum(nvl(a.bytes,0))) 已使用,
sum(nvl(a.bytes,0)) 剩余,
sum(nvl(a.bytes,0))/(b.bytes)*100 剩余百分比
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.file_id,b.bytes
order by b.tablespace_name
2.查询oracle系统用户的默认表空间和临时表空间
select default_tablespace,temporary_tablespace from dba_users
3.查询单张表的使用情况
select segment_name,bytes from dba_segments where segment_name = 'RE_STDEVT_FACT_DAY' and owner = USER
RE_STDEVT_FACT_DAY是您要查询的表名称
4.查询所有用户表使用大小的前三十名
select * from (select segment_name,bytes from dba_segments where owner = USER order by bytes desc ) where rownum <= 30
5.查询当前用户默认表空间的使用情况
select tablespacename,sum(totalContent),sum(usecontent),sum(sparecontent),avg(sparepercent)
from
(
SELECT b.file_id as id,b.tablespace_name as tablespacename,b.bytes as totalContent,(b.bytes-sum(nvl(a.bytes,0))) as usecontent,sum(nvl(a.bytes,0)) as sparecontent,sum(nvl(a.bytes,0))/(b.bytes)*100 as sparepercent
FROM dba_free_space a,dba_data_files b
WHERE a.file_id=b.file_id and b.tablespace_name = (select default_tablespace from dba_users where username = user)
group by b.tablespace_name,b.file_name,b.file_id,b.bytes
)
GROUP BY tablespacename
6.生成自增序列select (sysdate-1) from dual connect by rownum <6
(SELECT LEVEL l FROM DUAL CONNECT BY LEVEL <= 100)
寻找节点关系
select objid,toobjectid,fromobjectid,level from sprt_orgrela START WITH toobjectid = 999007470 CONNECT BY prior toobjectid = fromobjectid
Clob字段可以使用like 不能使用in('')方法
事务
在事务开始设置读已提交
SET TRANSACTION READ ONLY | WRITE
SET TRANSACTION ISOLATION LEVEL read committed|SERIALIZABLE
oracle不允许脏读/【read uncommitted】
查看表名称 表注释SELECT * FROM user_tab_comments
查看数据库所有执行的语句
SELECT SQL_ID,SQL_TEXT, LAST_ACTIVE_TIME,SQL_FULLTEXT FROM v$sql
ORDER BY LAST_ACTIVE_TIME DESC
select * from dba_hist_active_sess_history
查看数据库ip地址
select sys_context('userenv','ip_address') from dual;
