oracle数据库登录命令（linux下重启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(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;