oracle常用命令1

#给oracle创建监控用户
CREATE USER C##QGS123
IDENTIFIED BY QGS123
DEFAULT TABLESPACE SYSTEM
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;

#注释
创建用户C##QGS，密码QGS123
默认表空间系统
临表空间温度
配置文件默认值
账户解锁；
允许连接到C##QGS；
向C##QGS授予资源；
允许选择任意表给C##QGS；
允许选择任意字典到C##QGS；
向C##QGS授予无限表空间；
允许选择任意字典到C##QGS；
将创建会话权限授予C##QGS；

GRANT CONNECT TO C##QGS123;
GRANT RESOURCE TO C##QGS123;
GRANT SELECT ANY TABLE TO C##QGS123;
GRANT SELECT ANY DICTIONARY TO C##QGS123;
GRANT UNLIMITED TABLESPACE TO C##QGS123;
GRANT SELECT ANY DICTIONARY TO C##QGS123;
GRANT CREATE SESSION TO C##QGS123;

#常见监控Oracle数据库，创建监控用户只监控数据库活性，授予用户连接权限
CREATE USER QGS2 IDENTIFIED BY QGS1234;
 
GRANT CONNECT TO QGS2;

#创建用户，指定表空间（一个表空间可以建立多个用户）
create user wateruser 
identified by itcast
default tablespace boos;

#创建表空间，表空间名test，设定文件大小为1024m,当满了之后自动扩展100m
create tablespace test logging datafile'/opt/oracle/oradata/test.dbf' 
size 1024m autoextend on 
next 100m ;
#
create table 表名称 （
字段名 类型（长度） primary key,
字段名 类型（长度）
...............
）

key 主键

数字类型
1.字符型
char；固定长度的字符类型，最多存储2000个字节
varchar2；可变长度的字符类型，最多存储4000个字符
long；大文本类型。最大可以存储2G
2.数值型
number：
number（5） 最大可以存数为99999  ， #（5）表示总共5位
number（5，2）最大可以存的数为999.99   #（5）表示总共5位  （，2）表示小数点后的位数
3.日期型
date：日期时间型，精确到秒
timestamp：精确到秒的小数点后9位
4.二进制型（大数据类型）
clob；存储字符，最大可存4G
blob；存储图像，声音，视频等二进制数据，最多可存4G

#检查Oracle在线日志状态
select group#,status,type,member from v$logfile; 

#检查Oracle表空间的状态
select tablespace_name,status from dba_tablespaces; 

#检查Oracle所有数据文件状态
select name,status from v$datafile;
select file_name,status from dba_data_files; 

#检查无效对象(有记录返回，说明存在无效对象)

select owner, object_name, object_type
      from dba_objects
     where status != 'VALID'
       and owner != 'SYS'
       and owner != 'SYSTEM';


SELECT owner, object_name, object_type
  FROM dba_objects
 WHERE status = 'INVALID';

#检查所有回滚段状态
select segment_name,status from dba_rollback_segs;

#,检查数据库连接情况(查看当前会话连接数，是否属于正常范围)
select count(*) from v$session;
 
select sid,serial#,username,program,machine,status from v$session;

#检查表空间使用情况
select f.tablespace_name,
       a.total,
       f.free,
       round((f.free / a.total) * 100) "% Free"
  from (select tablespace_name, sum(bytes / (1024 * 1024)) total
          from dba_data_files
         group by tablespace_name) a,
         (select tablespace_name, round(sum(bytes / (1024 * 1024))) free
            from dba_free_space
           group by tablespace_name) f
 WHERE a.tablespace_name = f.tablespace_name(+)
 order by "% Free";

#检查一些扩展异常的对象
select Segment_Name,
       Segment_Type,
       TableSpace_Name,
        (Extents / Max_extents) * 100 Percent
  From sys.DBA_Segments
 Where Max_Extents != 0
   and (Extents / Max_extents) * 100 >= 95
 order By Percent;

#检查system表空间内的内容
select distinct (owner)
  from dba_tables
 where tablespace_name = 'SYSTEM'
   and owner != 'SYS'
   and owner != 'SYSTEM'
union
select distinct (owner)
  from dba_indexes
 where tablespace_name = 'SYSTEM'
   and owner != 'SYS'
   and owner != 'SYSTEM';

#检查对象的下一扩展与表空间的最大扩展值

select a.table_name, a.next_extent, a.tablespace_name
  from all_tables a,
         (select tablespace_name, max(bytes) as big_chunk 
            from dba_free_space 
           group by tablespace_name)          f
 where f.tablespace_name = a.tablespace_name 
   and a.next_extent > f.big_chunk
union
select a.index_name, a.next_extent, a.tablespace_name
  from all_indexes a,
         (select tablespace_name, max(bytes) as big_chunk 
            from dba_free_space 
           group by tablespace_name)           f
 where f.tablespace_name = a.tablespace_name 
   and a.next_extent > f.big_chunk;

#检查运行很久的SQL

SELECT USERNAME,
       SID,
       OPNAME,
       ROUND(SOFAR * 100 / TOTALWORK, 0) || '%' AS PROGRESS,
       TIME_REMAINING,
       SQL_TEXT
  FROM V$SESSION_LONGOPS, V$SQL
 WHERE TIME_REMAINING <> 0
   AND SQL_ADDRESS = ADDRESS
   AND SQL_HASH_VALUE = HASH_VALUE;

#等待时间最多的5个系统等待事件的获取

SELECT *
  FROM (SELECT *
          FROM V$SYSTEM_EVENT
         WHERE EVENT NOT LIKE 'SQL%'
         ORDER BY TOTAL_WAITS DESC)
 WHERE ROWNUM <= 5;

#检查表空间的I/O比例

SELECT DF.TABLESPACE_NAME NAME,
                                   DF.FILE_NAME       "FILE",
                                   F.PHYRDS           PYR,
                                   F.PHYBLKRD         PBR,
                                   F.PHYWRTS          PYW,
                                   F.PHYBLKWRT        PBW  
      FROM V$FILESTAT     F,
           DBA_DATA_FILES DF  WHERE F.FILE# = DF.FILE_ID  ORDER BY DF.TABLESPACE_NAME;

#检查文件系统的I/O比例

    SELECT SUBSTR(A.FILE#, 1, 2) "#",
           SUBSTR(A.NAME, 1, 30) "NAME",
           A.STATUS,
           A.BYTES,
           B.PHYRDS,
           B.PHYWRTS
      FROM V$DATAFILE A, V$FILESTAT B
     WHERE A.FILE# = B.FILE#;

#检查缓冲区命中率

SELECT a.VALUE + b.VALUE logical_reads,
              c.VALUE phys_reads,
              round(100 * (1 - c.value / (a.value + b.value)), 4) hit_ratio  
  FROM v$sysstat a,
       v$sysstat b,
       v$sysstat c  WHERE a.NAME = 'db block gets'    AND b.NAME = 'consistent gets'    AND c.NAME = 'physical reads';

#检查共享池命中率

select sum(pinhits) / sum(pins) * 100 from v$librarycache; 

#检查排序区

select name,value from v$sysstat where name like '%sort%'; 

#检查日志缓冲区

 select name, value
      from v$sysstat
     where name in ('redo entries', 'redo buffer allocation retries');

#Oracle Job是否有失败

select job, what, last_date, next_date, failures, broken
      from dba_jobs
     Where schema_user = 'CAIKE';

#监控数据量的增长情况
select A.tablespace_name, (1 - (A.total) / B.total) * 100 used_percent  
  from (select tablespace_name, sum(bytes) total          
          from dba_free_space          group by tablespace_name) A,
               (select tablespace_name, sum(bytes) total          
                  from dba_data_files          group by tablespace_name) B  where A.tablespace_name = B.tablespace_name;

#检查失效的索引

select index_name, table_name, tablespace_name, status
      From dba_indexes
     Where owner = 'CTAIS2'
       And status <> 'VALID';

#检查不起作用的约束

SELECT owner, constraint_name, table_name, constraint_type, status  
   FROM dba_constraints  WHERE status = 'DISABLE'    and constraint_type = 'P';

#检查无效的trigger

SELECT owner, trigger_name, table_name, status
  FROM dba_triggers
 WHERE status = 'DISABLED';

#查看所有用户
select * from all_users;
#删除普通用户
drop user QGS3112 cascade;
#当前用户被激活的全部角色
select * from session_roles;
#全部用户被授予的角色
select * from dba_role_privs;
#查看某个用户所拥有的角色
select * from dba_role_privs where grantee='用户名'; 
#查看某个角色所拥有的权限
select * from dba_sys_privs where grantee='用户名'; 

#查询Oracle中有哪些可用存储空间
select * from v$tablespace;

#检查登录成功的日志
grep -i accepted /var/log/secure
#检查登录失败的日志
grep -i inval /var/log/secure &&grep -i failed /var/log/secure
#系统负载情况
uptime
#系统I/O情况
iostat -k 1 3
#内存使用情况
free -m
#CPU使用情况
top
#检查系统磁盘空间
df -h

#查看当前数据库所有的表
select * from tabs;

#将dba权限赋予给AA用户，DBA的权限主要是对数据库对象而言具有可完全操作的权限
grant dba to AA;

#

#

#

#