苹果电脑终端连接oracle数据库常用操作

苹果电脑终端连接oracle数据库常用操作

1，访问服务器

ssh root@192.0.0.0 输入你的密码

2，切换到oracle用户

su - oracle

3.登录进数据库

SQLplus / as sysdba

4.查询数据库实例

select name from v$database;

5.查询表空间的总容量

select a.TABLESPACE_NAME, sum) /1024 / 1024 as MB from a group by a.TABLESPACE_NAME;

6.查询表空间的空闲容量

select b.TABLESPACE_NAME,count(1) as extends,sum) / 1024 / 1024 as MB, sum) as blocks from b group by b.TABLESPACE_NAME;

7.查询表空间的使用情况

select , 2) as TOTAL_MB,round - , 2) as USED_MB,ROUND((1 - / ) * 100, 2) || '%' as USED_PCT,ROUND(, 2) as FREE_mb from (select a.TABLESPACE_NAME, sum) / 1024 / 1024 as MB from a group by a.TABLESPACE_NAME) total, (select b.TABLESPACE_NAME, count(1) as extends,sum) / 1024 / 1024 as MB,sum) as blocks from b group by b.TABLESPACE_NAME) free where = ;

8.查询阻塞SQL语句

SELECT UPPER) "表空间名",D.TOT_GROOTTE_MB "表空间大小(M)",D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",TO_CHAR(ROUND - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99')||'%' "使用比",F.TOTAL_BYTES "空闲空间(M)",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) / (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 4 DESC;