数据库管理员常用&问题排查sql-Oracle篇

select * from v$version;	查看数据库版本
select instance_name from v$instance;	获取数据库实例名
select * from dba_directories;	查询指定的数据库备份位置
select * from v$sql;	查询最近所有的SQL操作
select * from v$logfile;	查看oracle日志路径
select status from v$instance;	检查数据库是否挂载
select instance_name,status from v$instance;	查看数据库是否启动
select count(1) as ACTIVE_COUNT from v$session where status = 'ACTIVE' and type = 'USER' and program not like '%PR%';	查看并发会话数
select (select count(1) from v$session) as current_cnt,(select value from v$parameter where name ='sessions') as session_cnt, (select round((select count(1) from v$session)/(select value from v$parameter where name 'sessions')*100) pct from dual)as from dual;	查看session会话使用率
select tablespace_name,round(used_space * 8192 / 1024 / 1024 , 2) used_space,round(tablespace_size * 8192 / 1024 / 1024 , 2) tablespace_size, round(used_percent, 2) used_percent from dba_tablespace_usage_metrics;	查看表空间使用率
SELECT USERNAME, ACCOUNT_STATUS FROM DBA_USERS WHERE ACCOUNT_STATUS = 'LOCKED';	查看锁定的用户
select file_name from dba_data_files;	查看Oracle数据文件存放位置
alter user username account unlock；	解锁用户
alter user username identified by "密码";	修改用户密码
show parameter audit_file_dest;	查看审计日志文件路径
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 1;	查询数据库使用空间
select tablespace_name,round(used_space * 8192 / 1024 / 1024 , 2) used_space, round(tablespace_size * 8192 / 1024 / 1024 , 2) tablespace_size, round(used_percent, 2) used_percent from dba_tablespace_usage_metrics;	查看表空间使用率（简版）
select tablespace_name,file_id,file_name from dba_data_files;	查看表空间文件
alter tablespace TS_TUSP add datafile '/u01/TS_TUSP4.DBF' size 10000m autoextend on;	表空间扩容
select * from dba_role_privs where granted_role='DBA';	查找那些用户有DBA权限
SELECT DISTINCT GRANTEE FROM DBA_SYS_PRIVS WHERE GRANTEE NOT IN ('PUBLIC');	查看特权用户
SELECT profile FROM dba_profiles WHERE resource_name = 'PASSWORD' AND username = '你的用户名';	查看特定用户密码最小长度
SELECT * FROM dba_profiles WHERE profile = 'DEFAULT' AND resource_name = 'PASSWORD';	查看密码策略
select limit from dba_profiles where profile = 'DEFAULT' and resource name = 'FAILED_LOGIN_ATTEMPTS';	查询Oracle数据库中默认资源配置文件（'DEFAULT' profile）下关于失败登录尝试（'FAILED_LOGIN_ATTEMPTS' resource）的限制
CREATE TABLESPACE fxqsjqy DATAFILE '路径/fxqsjqy.dbf' SIZE 1G AUTOEXTEND ON NEXT 1G MAXSIZE 30G;	创建表空间
CREATE USER fxqsjqy IDENTIFIED BY 密码 DEFAULT TABLESPACE fxqsjqy;	创建用户
GRANT DBA TO fxqsjqy;	赋予权限
SQL> @?/rdbms/admin/awrrpt.sql Enter value for report_type: html Enter value for num_days: 1 Enter value for begin_snap: 100 Enter value for end_snap: 101 Enter value for report_name: awr.html	取awr报告，snap是天数出来的快照号
SELECT DATABASE_ROLE, OPEN_MODE FROM V$DATABASE;	查询数据库读写模式。主库：DATABASE_ROLE 显示 PRIMARY，OPEN_MODE 通常为 READ WRITE。 备库：DATABASE_ROLE 显示 PHYSICAL STANDBY 或 LOGICAL STANDBY，OPEN_MODE 可能为 MOUNTED 或 READ ONLY WITH APPLY