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

1，访问服务器

ssh [email protected] 输入你的密码

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;