苹果电脑终端连接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(a.BYTES) /1024 / 1024 as MB from sys.dba_data_files a group by a.TABLESPACE_NAME;
6.查询表空间的空闲容量
select b.TABLESPACE_NAME,count(1) as extends,sum(b.BYTES) / 1024 / 1024 as MB, sum(b.BLOCKS) as blocks from sys.dba_free_space b group by b.TABLESPACE_NAME;
7.查询表空间的使用情况
select total.TABLESPACE_NAME,round(total.MB, 2) as TOTAL_MB,round(total.MB – free.MB, 2) as USED_MB,ROUND((1 – free.MB / total.MB) * 100, 2) || ‘%’ as USED_PCT,ROUND(free.MB, 2) as FREE_mb from (select a.TABLESPACE_NAME, sum(a.BYTES) / 1024 / 1024 as MB from sys.dba_data_files a group by a.TABLESPACE_NAME) total, (select b.TABLESPACE_NAME, count(1) as extends,sum(b.BYTES) / 1024 / 1024 as MB,sum(b.BLOCKS) as blocks from sys.dba_free_space b group by b.TABLESPACE_NAME) free where total.TABLESPACE_NAME = free.TABLESPACE_NAME;
8.查询阻塞SQL语句
SELECT UPPER(F.TABLESPACE_NAME) “表空间名”,D.TOT_GROOTTE_MB “表空间大小(M)”,D.TOT_GROOTTE_MB – F.TOTAL_BYTES “已使用空间(M)”,TO_CHAR(ROUND((D.TOT_GROOTTE_MB – 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(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 4 DESC;
