verysu 设计模式 设计模式 响应式编程 百度开发平台 codeforces leetcode usfca

导航

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;

posted on 2020-01-08 13:08  泳之  阅读(126)  评论(0编辑  收藏  举报

我是谁? 回答错误