linux下重啟oracle面試題「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;