oracle数据库常用sql语句汇总

oracle数据库常用sql语句汇总

1 查看表空间名称、数据文件的路径、大小及使用率

select b.tablespace_name "TABLESPACE_NAME",
b.file_name "DATAFILE_PATH",
b.bytes/1024/1024 "SIZE(M)",
substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) "USAGE(%)"
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.bytes 
order by 1,2;

2 查看表空间名称、数据文件的总大小、剩余大小及使用率

SELECT * FROM (
SELECT df.tablespace_name "tablespace name",df.total_space "total
space(M)",fs.free_space "free space(M)",
ROUND((1-fs.free_space/df.total_space)*100,2) "usage(%)"
FROM
(SELECT tablespace_name,ROUND(SUM(bytes)/(1024*1024),2) total_space FROM
dba_data_files GROUP BY tablespace_name) df,
(SELECT tablespace_name,ROUND(SUM(bytes)/(1024*1024),2) free_space FROM
dba_free_space GROUP BY tablespace_name) fs
WHERE df.tablespace_name=fs.tablespace_name
UNION ALL
SELECT A.tablespace_name "tablespace name", D.mb_total "total space(M)",
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 "free space(M)",
ROUND(((SUM (A.used_blocks * D.block_size) / 1024 / 1024)/d.mb_total)*100,2)
"usage(%)"
FROM v$sort_segment A,
(SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size) D
WHERE A.tablespace_name = D.name GROUP by A.tablespace_name, D.mb_total
) order by 4 desc;

3 查看表空间名、表空间大小、已使用空间、空闲空间、使用比率、最大块

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
　　D.TOT_GROOTTE_MB "表空间大小(M)",
　　D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
　　F.TOTAL_BYTES "空闲空间(M)",
　　TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),\'990.99\') || \'%\' "使用比率",
　　F.MAX_BYTES "最大块(M)"
　　FROM (SELECT TABLESPACE_NAME,
　　ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
　　ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
　　FROM SYS.DBA_FREE_SPACE
　　GROUP BY TABLESPACE_NAME) F,
　　(SELECT DD.TABLESPACE_NAME,
　　 ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
　　FROM SYS.DBA_DATA_FILES DD
　　GROUP BY DD.TABLESPACE_NAME) D
　　WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
　　ORDER BY 1;

4 增加表空间大小

首先需要查询表空间名称和数据文件所在路径，然后执行以下命令

alter tablespace "USERS" add datafile \'/u01/app/oracle/oradata/ORCL/users02.dbf\' size 10240M autoextend on;

5 查看数据库用户信息

select username,account_status,lock_date from dba_users;

USERNAME                       ACCOUNT_STATUS                   LOCK_DATE
------------------------------ -------------------------------- ---------
SYS                            OPEN
SYSTEM                         OPEN
SCOTT                          OPEN
HR                             OPEN
OUTLN                          EXPIRED & LOCKED                 17-SEP-11
MGMT_VIEW                      EXPIRED & LOCKED                 17-SEP-11
FLOWS_FILES                    EXPIRED & LOCKED                 17-SEP-11
MDSYS                          EXPIRED & LOCKED                 17-SEP-11
ORDSYS                         EXPIRED & LOCKED                 17-SEP-11

6 查看数据库用户数据写在了哪个数据文件里

select username,default_tablespace from user_users;

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
SYS                            SYSTEM