开发者社区> 科技小能手> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

oracle--常用操作

简介:
+关注继续查看

一、数据库语言类型

1、数据定义语言(DDL)

Create ,alter ,drop

2、数据操纵语言(DML)

Insert ,select ,update, delete

3、数据控制语言(DCL)

Grant, revoke

4、查询  select

此外是还事务控制(TCL),如commit。

1、SQL数据定义功能

模式定义,  表定义,  视图和索引的定义

wKioL1bTt-CiwTI-AAAw6OtQo08945.png

新建一个表

CREATE TABLE Student

        ( Sno       CHAR(9)   PRIMARY KEY,  /* 列约束, 主码 */

          Sname  CHAR(20) UNIQUE,               /* 不允许重名 */

          Ssex      CHAR(2)   NOT NULL,

          Sage      SMALLINT NOT NULL,       /* 非空 */

          Sdept    CHAR(20),

          CHECK (Sage > 0 AND Sage <= 50)     /* 表级约束 */

        );

删除表

DROP TABLE Student

修改表属性

ALTER TABLE <表名>

        [ADD <新列名> <数据类型> [完整性约束]]

        [DROP <完整性约束>]

        [ALTER COLUMN <列名> <数据类型>] 

ALTER TABLE Student  ADD Sdorm CHAR(10);    /* 增加宿舍列 */ 

ALTER TABLE Student  DROP COLUMN Sdorm; /* 删除宿舍列 */

ALTER TABLE Student    ADD UNIQUE (Sname);    /* 增加唯一性约束 */

ALTER TABLE Student    ALTER COLUMN Sage SMALLINT;   /* 修改类型 */

2、数据更新

插入数据 (INSERT INTO … VALUES)

INSERT

    INTO Student(Sno,  Sname,  Ssex,  Sdept,  Sage)

    VALUES (‘200816012’,  ‘陈东’,  ‘男’,  ‘IS’,  18);

/* 注:  属性的顺序可任意排放,  只需保持对应 */

/* 注:  没有指出属性,  表示表中所有属性且同顺序 */

修改数据 (UPDATE … SET … WHERE …)

UPDATE Student

    SET Sage = 21,  Sdept = ‘JP’

    WHERE Sno = ‘200815001’;

删除数据 (DELETE … FROM … WHERE …)

DELETE

    FROM Student

WHERE Sno = ‘200815001’;

常用的查询条件:

wKioL1bTuDewoDZgAAA6XYZ9dmg978.png


SELECT的一般格式

SELECT [ALL | DISTINCT] <目标列表达式>

                                                   [, <目标列表达式>] …

    FROM <表名或视图名> [, <表名或视图名>] …

   [WHERE <条件表达式>]

   [GROUP BY <列名1> [HAVING <条件表达式>] ]

   [ORDER BY <列名2> [ASC | DESC] ]

所有需要修改的配置已经完成,我们需要启动监听服务和数据库服务器,启动完成后通过客户端的sqldeveloper去远程连接下看看是否创建成功。下面附上数据库和监听的启动的命令

启动监听 lsnrctl start

停止监听 lsnrctl stop

查看监听状态 lsnrctl status

数据库 通过 sqlplus / as sysdba 登入到sqlplus界面

启动 startup 启动到mount状态 startup mount

关闭 shutdown

立即关闭 shutdown immediate

强行关闭 shutdown abort (慎用,可能会导致数据库出现不一致状态,需要重做日志才能启动)

二、扩展表空间

需要扩展表空间情况

1 新搭建环境,需要新建表空间。

2 应用报错,表空间不足,需要扩展表空间。(首先分析下能不能清理数据)

通过以下脚本查看表空间不足情况

 select b.file_name filename,

       b.tablespace_name spacename,

       b.bytes / 1024 / 1024 M,

       (b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024 userM,

       substr((b.bytes - sum(nvl(a.bytes, 0))) / (b.bytes) * 100, 1, 5) userd

  from dba_free_space a, dba_data_files b

 where a.file_id = b.file_id

 /*  and b.tablespace_name = 'NNC_INDEX01'*/

 group by b.tablespace_name, b.file_name, b.bytes

 order by b.tablespace_name;

修改表空间

alter tablespace NNC_INDEX01  --指定修改的表空间

 add datafile 

‘/u01/app/oracle/oradata/hrdb/nnc_index04.dbf’ --指定给表空间增加的数据文件

 size 6144M --指定数据文件大小 

autoextend off; --关闭自动扩展功能

新增表空间语句

create tablespace EAS_D_DEPPON009_STANDARD datafile '/u01/app/oracle/oradata/dpweb/EAS_D_DEPPON009_STANDARD01.dbf' size 1024M autoextend off;

表空间操作详细http://blog.csdn.net/starnight_cbj/article/details/6792364

三、解决死锁

 当测试人员反映数据库卡,先查看数据库锁进程

  通过下列语句alter system kill session ‘SID,SERIAL#’;杀掉进程

查询锁定

select sess.sid,

           p.SPID,

           sess.serial#,

           lo.oracle_username,

           lo.os_user_name,   ao.object_name,

           lo.locked_mode

      from v$locked_object lo, dba_objects ao, v$session sess, v$process p

     where ao.object_id = lo.object_id

       and lo.session_id = sess.sid

       and sess.PADDR = p.ADDR;

四、定位性能sql

1、查找前十条性能差的sql

SELECT * FROM (select PARSING_USER_ID,EXECUTIONS,SORTS, 

COMMAND_TYPE,DISK_READS,sql_text FROM v$sqlarea 

order BY disk_reads DESC )where ROWNUM<10 ; 


2、查看IO高的session

SELECT se.sid,se.serial#,pr.SPID,

--se.username,se.status, 

--se.terminal,se.program,se.MODULE,se.sql_address,st.event,

st.p1text,si.physical_reads 

--,si.block_changes 

FROM v$session se,v$session_wait st, 

v$sess_io si,v$process pr WHERE st.sid=se.sid AND st.sid=si.sid AND se.PADDR=pr.ADDR AND se.sid>6 AND st. 

wait_time=0 AND st.event NOT LIKE '%SQL%' ORDER BY physical_reads DESC;

3、通过进程号查看异常sql

SELECT a.username,

 a.machine,

       a.program,

       a.sid,

       a.serial#,

       a.status,

       c.piece,

       c.sql_text

  FROM v$session a,

       v$process b,

       v$sqltext c

WHERE b.spid=5200  

   AND b.addr=a.paddr

   AND a.sql_address=c.address(+)

ORDER BY c.piece  ;

alter system kill session 'sid,serial#' ;--查看sql

五、账号密码问题

1、密码过期

密码过期导致无法连接修改密码解决

Alter user scott identified by scott;

用户锁导致无法登入 用sys用户登入sqlplus解锁

Alter user scott account unlock;

2、权限问题

创建search用户 并授予增删改权限

create user search identified by search;

grant connect to search;

grant select any table to search;

grant update any table to search;

grant delete any table to searhc;

查看用户、权限、角色的命令和视图 

DBA_*意为DBA拥有的或可以访问的所有的对象。

ALL_*意为某一用户拥有的或可以访问的所有的对象。

USER_*意为某一用户所拥有的所有的对象

3、查看所有用户:  

 select * from dba_users; --只有dba用户能够使用这个命令

wKioL1cU69WAutt4AAGwB9ZWnZU248.png

 select * from all_users;--每个用户都能够查询

wKiom1cU63jRKYiCAAFRa_xSXqo716.png  

select * from user_users; --查询登录用户的信息

wKioL1cU7IKgl6a1AAFRa_xSXqo020.png

4、查看用户系统权限:  

 select * from dba_sys_privs; 

 select * from session_privs; 

 select * from user_sys_privs; 

5、查看用户对象权限:  

 select * from dba_tab_privs;  

select * from all_tab_privs;  

select * from user_tab_privs; 

 6、查看所有角色:  

 select * from dba_roles; 

7、查看用户所拥有的角色: 

 select * from dba_role_privs;  

select * from user_role_privs;

b 概述     与权限,角色相关的视图大概有下面这些:    

DBA_SYS_PRIVS: 查询某个用户所拥有的系统权限    

USER_SYS_PRIVS:  查询当前用户所拥有的系统权限   

SESSION_PRIVS:    查询当前用户所拥有的全部权限   

ROLE_SYS_PRIVS:   查询某个角色所拥有的系统权限  注意: 要以SYS用户登陆查询这个视图,否则返回空.   

ROLE_ROLE_PRIVS:    当前角色被赋予的角色    

SESSION_ROLES:      当前用户被激活的角色   

USER_ROLE_PRIVS:   当前用户被授予的角色   

 另外还有针对表的访问权限的视图:   

TABLE_PRIVILEGES   

ALL_TAB_PRIVS        

ROLE_TAB_PRIVS:     

某个角色被赋予的相关表的权限 oracle与用户角色权限相关的视图   Oracle 本身的数据字典设计我个人觉得很合理, 因为DBA_xxx, ALL_xxx,USER_xxx  让人一看大概就知道这个视图是干什么用的. 本文简要总结了一下与权限,角色相关的视图.

一. 概述     与权限,角色相关的视图大概有下面这些: 

1、DBA_SYS_PRIVS:   查询某个用户所拥有的系统权限。 

2、USER_SYS_PRIVS:  查询当前用户所拥有的系统权限。  

3、SESSION_PRIVS:    查询当前用户所拥有的全部权限。 

4、ROLE_SYS_PRIVS:  查询某个角色所拥有的系统权限。注意: 要以SYS用户登陆查询这个视图,否则返回空。    

5、ROLE_ROLE_PRIVS:    当前角色被赋予的角色  

6、SESSION_ROLES:      当前用户被激活的角色  

7、USER_ROLE_PRIVS:   当前用户被授予的角色    

针对表的访问权限的视图:      

TABLE_PRIVILEGES           

ALL_TAB_PRIVS             

ROLE_TAB_PRIVS: 某个角色被赋予的相关表的权限


六.日志查看

一.Oracle日志的路径:

登录:sqlplus "/as sysdba"

查看路径:SQL> select * from v$logfile;

SQL> select * from v$logfile;(#日志文件路径)

二.Oracle日志文件包含哪些内容:(日志的数量可能略有不同)

control01.ctl example01.dbf redo02.log sysaux01.dbf undotbs01.dbf

control02.ctl redo03.log system01.dbf users01.dbf

control03.ctl redo01.log SHTTEST.dbf temp01.dbf

三.Oracle日志的查看方法:

SQL>select * from v$sql (#查看最近所作的操作)

SQL>select * from v$sqlarea(#查看最近所作的操作)

Oracle 数据库的所有更改都记录在日志中,从目前来看,分析Oracle日志的唯一方法就是使用Oracle公司提供的LogMiner来进行,因为原始的日志信息我们根本无法看懂,Oracle8i后续版本中自带了LogMiner,而LogMiner就是让我们看懂日志信息的工具,通过这个工具可以:查明数据库的逻辑更改,侦察并更正用户的误操作,执行事后审计,执行变化分析。 

四.LogMiner的使用:

1、创建数据字典文件(data-dictionary)

1).首先在init.ora初始化参数文件中,添加一个参数UTL_FILE_DIR,该参数值为服务器中放置数据字典文件的目录。如:UTL_FILE_DIR = ($ORACLE_HOME\logs) ,重新启动数据库,使新加的参数生效:

SQL> shutdown;

SQL>startup;

2).然后创建数据字典文件 

SQL> connect /as sysdba 

SQL> execute dbms_logmnr_d.build(dictionary_filename => 'dict.ora',dictionary_location => '/data1/oracle/logs');


PL/SQL procedure successfully completed


2、创建要分析的日志文件列表 

1).创建分析列表,即所要分析的日志 

SQL>execute dbms logmnr.add logfile(LogFileName => '/data1/oracle/oradata/akazamdb/redo01.log',Options => dbms_logmnr.new); 

PL/SQL procedure successfully completeds 

2).添加分析日志文件,一次添加1个为宜 

SQL>execute dbms_ logmnr.add_ logfile(LogFileName => 

'/data1/oracle/oradata/akazamdb/redo01.log',Options => dbms_logmnr.ADDFILE); 

PL/SQL procedure successfully completed


3、使用LogMiner进行日志分析(具体要查询什么内容可以自己修改)

(1)无限制条件

SQL> EXECUTE dbms_logmnr.start_logmnr(

DictFileName=>'/data1/oracle/logs/v816dict.ora '); 

(2)有限制条件

通过对过程DBMS_ LOGMNR.START_LOGMNR中几个不同参数的设置,可以缩小要分析日志文件的范围。通过设置起始时间和终止时间参数我们可以限制只分析某一时间范围的日志。如下面的例子,我们仅仅分析2007年9月18日的日志:

SQL> EXECUTE dbms_logmnr.start_logmnr(

DictFileName => ' /data1/oracle/logs/ v816dict.ora ', 

StartTime => to_date('2007-9-18 00:00:00','YYYY-MM-DD HH24:MI:SS')

EndTime => to_date(''2007-9-18 23:59:59','YYYY-MM-DD HH24:MI:SS ')); 

也可以通过设置起始SCN和截至SCN来限制要分析日志的范围:

SQL> EXECUTE dbms_logmnr.start_logmnr(

DictFileName => ' /data1/oracle/logs/ v816dict.ora ',

StartScn => 20,

EndScn => 50); 

4、观察分析结果(v$logmnr_contents)

到现在为止,我们已经分析得到了重作日志文件中的内容。动态性能视图v$logmnr_contents包含LogMiner分析得到的所有的信息。

SELECT sql_redo FROM v$logmnr_contents; 

如果我们仅仅想知道某个用户对于某张表的操作,可以通过下面的SQL查询得到,该查询可以得到用户DB_ZGXT对表SB_DJJL所作的一切工作。

SQL> SELECT sql_redo FROM v$logmnr_contents WHERE username='DB_ZGXT' AND tablename='SB_DJJL'; 

需要强调一点的是,视图v$logmnr_contents中的分析结果仅在我们运行过程'dbms_logmrn.start_logmnr'这个会话的生命期中存在。这是因为所有的LogMiner存储都在PGA内存中,所有其他的进程是看不到它的,同时随着进程的结束,分析结果也随之消失。

最后,使用过程DBMS_LOGMNR.END_LOGMNR终止日志分析事务,此时PGA内存区域被清除,分析结果也随之不再存在。

5、查看LogMiner工具分析结果 

SQL> select * from dict t where t.table_name like '%LOGMNR%';-看所有与logmnr相关的视图 

TABLE_NAME COMMENTS 

------------------------------ -------------------------------------------------------------------------------- 

GV$LOGMNR_CALLBACK Synonym for GV_$LOGMNR_CALLBACK 

GV$LOGMNR_CONTENTS Synonym for GV_$LOGMNR_CONTENTS 

GV$LOGMNR_DICTIONARY Synonym for GV_$LOGMNR_DICTIONARY 

GV$LOGMNR_LOGFILE Synonym for GV_$LOGMNR_LOGFILE 

GV$LOGMNR_LOGS Synonym for GV_$LOGMNR_LOGS 

GV$LOGMNR_PARAMETERS Synonym for GV_$LOGMNR_PARAMETERS 

GV$LOGMNR_PROCESS Synonym for GV_$LOGMNR_PROCESS 

GV$LOGMNR_REGION Synonym for GV_$LOGMNR_REGION 

GV$LOGMNR_SESSION Synonym for GV_$LOGMNR_SESSION 

GV$LOGMNR_STATS Synonym for GV_$LOGMNR_STATS 

GV$LOGMNR_TRANSACTION Synonym for GV_$LOGMNR_TRANSACTION 

V$LOGMNR_CALLBACK Synonym for V_$LOGMNR_CALLBACK 

V$LOGMNR_CONTENTS Synonym for V_$LOGMNR_CONTENTS 

V$LOGMNR_DICTIONARY Synonym for V_$LOGMNR_DICTIONARY 

V$LOGMNR_LOGFILE Synonym for V_$LOGMNR_LOGFILE 

V$LOGMNR_LOGS Synonym for V_$LOGMNR_LOGS 

V$LOGMNR_PARAMETERS Synonym for V_$LOGMNR_PARAMETERS 

V$LOGMNR_PROCESS Synonym for V_$LOGMNR_PROCESS 

V$LOGMNR_REGION Synonym for V_$LOGMNR_REGION 

V$LOGMNR_SESSION Synonym for V_$LOGMNR_SESSION 

TABLE_NAME COMMENTS 

------------------------------ -------------------------------------------------------------------------------- 

V$LOGMNR_STATS Synonym for V_$LOGMNR_STATS 

V$LOGMNR_TRANSACTION Synonym for V_$LOGMNR_TRANSACTION 

GV$LOGMNR_LOGS 是分析日志列表视图 

分析结果在GV$LOGMNR_CONTENTS 视图中,可按以下语句查询: 

select scn,timestamp,log_id,seg_owner,seg_type,table_space,data_blk#,data_obj#,data_objd#, 

session#,serial#,username,session_info,sql_redo,sql_undo from logmnr3 t where t.sql_redo like 'create%'; 

如果不能正常查询GV$LOGMNR_CONTENTS视图,并报以下错误,ORA-01306: 在从 v$logmnr_contents 中选择之前必须调用 dbms_logmnr.start_logmnr() 。可采用如下方法: 

create table logmnr3 as select * from GV$LOGMNR_CONTENTS;


Oracle端口:1521

七.Oracle查询某用户下的表,创建时间


查询TEST用户下的所有表,及其创建时间

SELECT OBJECT_NAME ,CREATED FROM ALL_OBJECTS WHERE OBJECT_TYPE='TABLE' AND OWNER='TEST' ORDER BY CREATED DESC;


查询当前用户下的所有表

SELECT * FROM USER_TABLES;

查询TEST用户下的所有表

SELECT * FROM ALL_TABLES WHERE OWNER='TEST'


八、查看数据库服务器的编码

SQL> select * from nls_database_parameters where parameter ='NLS_CHARACTERSET'; 


PARAMETER

------------------------------

VALUE

--------------------------------------------------------------------------------

NLS_CHARACTERSET

AL32UTF8



SQL> 



本文转自aaa超超aaa 51CTO博客,原文链接:http://blog.51cto.com/10983441/1745923

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
Oracle操作总结
聚合函数: count sum max min avg count返回的结果集中行的数目 sum max min avg顾名思义。但是要注意聚合函数强调的是"结果集中……",强调了 现在进行的操作对象是结果集 注意NULL不参与聚合函数的预算,要想其参加运算那么要将其转换为非空的值 分组: 在一个查询中需要进行多次统计就需要就必须使用分组 where是分组前过滤,过滤的是原始数据。
737 0
Oracle 数据库性能优化3日实战(企业培训)
课程名称一: Oracle性能优化及调整 课程时长 1天 课程深度: 高级 上机实验: 10%-30% 授课对象: Oracle开发人员、Oracle数据库管理人员,应用程序开发人员 课程描述: 本课程讲述Oracle数据库物理层规划,系统性能的监控,数据库性能参数调整,统计信息的收集,使用自动化调试工具优化数据库,I/O子系统的配置与设计以及性能优化方法论等。
1894 0
创建Oracle数据库、数据库名与实例名与SID之间的关系(图文详解)
目录 目录 软件环境 前言 安装Oracle监听程序 启动停止监听程序 创建数据库 数据库名db_name 数据库实例名instance_name 数据库名与实例名的关系 操作系统环境变量ORACLE_SID 软件环境 操作系统 RHEL 6.1 软件环境 Oracle10gr2 前言 在上一篇 RHEL6.1 安装 Oracle10gr2 (图文、解析) 中记录了如何成功的在RHEL6.1安装Oracle10gr2,但是在安装的过程中没有创建数据库,本篇主要记录了如果创建数据库和监听程序。
2315 0
安装Oracle数据库
Oracle数据库是现在大型企业、银行经常使用的一种关系型数据库。因为工作需要,需要安装Oracle数据库。这里就来记录一下。 下载数据库 首先去Oracle官网下载数据库。
973 0
文章
问答
文章排行榜
最热
最新
相关电子书
更多
Oracle 至PostgreSQL案例分享
立即下载
PostgresChina2018_刘成伟_oracle到Postgres数据库迁移工具
立即下载
ORACLE 10g 数据库体系结构图
立即下载