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;