我的菠萝呀的博客

原创 Oracle个人总结

原创 Oracle/Hive/Impala SQL比较

Oracle/Hive/Impala SQL

原创 C、Oracle赋予另一个用户查询权限，创建同义词

用户：UserA，UserB场景：用户UserA只有用户UserB指定表的查询权限。解决方案：1.给他一些权限，包括连接权限，因为他要创建同义词，还需要给他同义词grant connect to UserA;grant create synonym to UserA;grant create session to UserA;2.因为需要把**UserB的所有表的查询权限给UserA。**所以需要所有表的grant select on table_name to UserA语句，不可能一句一句

原创 Linux常用操作指令

备份(backup)： 业务数据的恢复过程（手工或rman）还原(restore)： 把原来备份从新覆盖回来恢复（recover）: 利用日志把数据回滚出来（归档日志+在线日志）完全恢复： 将数据库恢复到宕机前的最后一次提交状态。不完全恢复： 将数据库恢复到你 指定的某个时间段。[root@ps ~]#‘root’所属用户 ；‘ps’主机名 ；‘~’家目录 ；‘$’管理员用户 ；'#’管理员用户；cd 需要进入到哪个目录（[root@ps ~]# cd /tmp/）cd … 返回上一

原创 oracle操作指令 快捷

在plsql/developer的命令窗口执行sql脚本的命令是@+路径。报告窗口（command Window）：set serveroutput on;@d:2020.sql;导出用户：tools 工具 – export user objects 导出用户对象导出表tools 工具 – export tables 导出表...

原创 B、SQL优化

SQL优化：1、建议不用 * 代替所有列名2、使用truncate 代替 delete3、在确保完整性的情况下多用commit语句commit 语句所释放的资源:（1）回滚段上用于恢复数据的信息，撤销表空间也只做短暂的保留。（2）被程序语句获得的锁。（3）重做日志缓冲区（redo log buffer）中的空间。（4）Oracle为管理上述资源的内部花费。4、减少表的查询次数5、用exist 代替 in表连接优化：驱动表选择：驱动表（Driving Table）：是指被最先访问的表（

原创 A、杀进程，锁

查看当前数据库正在处理的sql语句select a.username,b.sql_fulltext,a.sid,a.serial# from v$session A,v$sqlarea b where a.sql_address = b.address查看锁select * from v$session t1 ,v$locked_object t2 where t1.sid = t2.session_id and t1.username = '...';--所属用户名查找被锁定的数

原创 27、用户 角色

角色是一个独立的数据库实体，它包括一组权限。 也就是说，角色包括一个或者多个权限的集合，它不被哪个用户所拥有。角色可以被授予任何权限，也可以从用户吧角色回收。几个系统预定义角色：connectresourcedbaexp_full_databaseimp_full_database角色connect 、resource 、dba 主要用于数据库管理，这三个角色之间相互没有包含关系（有些系统权限有重叠）。数据库管理员需要分别授予，connect 、resource、dba 角色，对于一般数据开

原创 26、用户 创建用户

用户是在数据库定义的一个名称，是oracle 访问控制机制，当链接数据库时，操作人员提供用户名和密码。oracle 提供了一些特权用户，sysdba,sysoper 这类用户主要用于数据库的维护操作，如启动数据库，关闭数据库，建立数据库，及执行备份和恢复操作。sysdba 和sysoper 区别： sysdba不仅具备sysoper所有权限，而且可以建立数据库，执行不完全恢复。oracle 提供了默认的特权用户sys 当以特权用户登录时，必须带有as sysdba 或as sysoper选项。与用户

原创 25、分区 索引分区

索引分区分为本地索引分区和全局索引分区全局索引不反应基础表的结构，因此若要分区就只能进行范围分区。局部索引反应基础表结构，因此对表的分区或子分区进行维护时，系统会自动对本地索引分区进行维护，而不需要对本地索引的分区进行维护。本地索引分区使用和分区表同样的分区键进行分区的索引，也就是说，索引分区所采用的列表与该表的分区所采用的列是相同的。本地索引分区的优点：1.如果只有一个分区需要维护，则只有一个本地索引受影响。2.支持分区独立性3.只有本地索引能够支持单一分区的装入和卸载。4.分表区和各自

原创 24、分区 表分区

范围分区范围分区关键字 range 创建该分区后，其中数据可以根据分区键值指定的范围进行分布，当数据在范围内均匀分布时，性能最好。create table ware_retail_part --创建一个描述商品零售的数据表 ( id integer primary key , retail_date date, ware_name varchar2(50))partition by range(retail_date) --rang分区字段也可以是两个或多个 range(id,retail

原创 23、序列 sequence

序列是Oracle提供用于生成一系列唯一数字的数据库对象。会自动生成顺序递增的序列号，以实现自动提供唯一的主键值。序列与视图一样不占用实际的存储空间，只是在数据字典中保存它的定义信息。用户在自己的模式中创建序列时，必须具有create sequence 系统权限。create sequence seq_name --序列名称[start with n] --可选子句，指定序列开始的位置，默认情况下，递增序列起始minvlue 递减序列起始 maxvalue[increment by n ] --可选

原创 22、同义词对象 sysnonym

建立私有同义词 create synonym,数据库必须具有create synonym权限如果在其他模式中创建私有同义词则用户必须具有 create any synonym 系统权限。建立公有同义词 create public synonym,数据库必须具有create synonym权限create public synonym private_dept for scott.dept;create synonym private_dept for dept;删除同义词对于基础对象名称和位置

原创 21、视图对象 view

视图是一个虚拟的表，它由存储的查询构成，可以将它的输出看作时一个表。视图不在数据库中存储数据值，其数据值来自定义的视图的查询语句所引用的表，数据库只在数据字典中存储存储视图的定义信息。创建视图create [or replace] view view_name[(alias,alias...)]--alias 指定视图别名as subquery --指定视图对应的子查询语句[with check option] --用于指定在视图定义check约束[constraint constraint_n

原创 20、索引对象 index

在关系性数据库中，用户查找数据与物理位置无关。为了能找到数据，表中每一行均用rowid标识。Oracle 为快速只读rowid 使用索引对象，提供服务器快速查找记录的功能。Oracle 对索引的管理与表享受，不仅要在数据字典中保存索引的定义，还需要在表空间为他分配实际存储空间。索引创建时，Oracle会自动在默认的表空间或指定表空间中建立一个索引段，为索引数据提供空间。将索引和对应表分别放在不同硬盘的不同表空间可以提高查询速度，因为oracle 能够并行读取不同硬盘的数据，这样查询可以避免产生i/o冲突

原创 19、数据表 约束

非空约束非空约束就是限制必须为某个列提供值。空（null）即不存在未知的情况。create table BOOKS( BookNo number(4) not null, --非空 BookName varchar2(30) , ISBN varchar2(30) not null);--使用 later table modify 修改重新定义非空约束alter table BOOKS modify BookName not null;alter table BOOKS modify B

原创 18、数据表 维护数据表

增加，删除字段--增加字段alter table student_6 add(province varchar2(10));--删除字段alter table student_6 drop column province;alter table student_6 drop(sex,age);修改字段（修改字段长度、数字列精度、列的数据类型、列的默认值等）alter table student_6 modify stu_name varchar2(30)：--改变数据长度修改表名称al

原创 17、数据表 创建数据表

数据表简称表，是ORACLE中主要的数据存储容器，表中的数据被组织成行和列。表中每一个列均有一个名称，并且每个列都有一个指定的大小和类型。如varchar2(10)数据库中每一个表都被一个模式或用户所拥有，因此表是一个典型的模式对象。在创建数据表时，Oracle 将一个指定的表空间中分配存储空间。最初创建的表是一个空的逻辑存储结构，其中不包含任何数据记录。数据类型1、char类型CHAR数据类型。存储固定长度字符串，范围为1-2000个字节，一旦定义了char类型列，就会一直保持声明时所规定的长度大

原创 16、表空间 创建表空间

create [smallfile/bigfile]tablespace tablespace_name --smallfile/bigfile大文件表空间，小文件表空间（默认）tablespace_name 表空间名称datafile '/path/filename' size [k/m] reuse,['/path/filename' size [k/m] reuse] --数据文件路径，size 大小，reuse 如果文件存在则清除重建，如果不存在则创建[autoextend [on|off] n

原创 15、表空间 表空间与数据文件的关系

Oracle 磁盘空间管理中的最高逻辑层是表空间。（数据库）–>表空间–>段–>盘区–>数据块一个或多个段组成一个表空间，一个段只能驻留在一个表空间内，一个或多个盘区组成一个段，一个盘区只能驻留在一个段中一组连续的数据块组成一个盘区，数据块是磁盘空间管理中逻辑划分的最底层。一个数据库包含多个表空间，一个表空间包含多个数据文件。表空间相当于数据库的逻辑结构，数据文件相当于数据库物理结构。dba_data_files 数据字典，表空间与对应数据文件的相关信息。...

原创 14、日志文件 -- 归档日志文件

归档日志：归档日志模式（archivelog）,非归档日志模式（noarchivelog）归档模式下，后台进程LGWR在写入下一个数据文件之前，必须等待该重做日志文件归档完成，否则LGWR进程将被暂停执行，直到对重做日志文件归档完成。归档操作由后台进程ARCN自动完成，也可由数据库管理员手动完成，另外为提高归档速度，可用考虑使用多个ARCN进程加速归档速度。非归档模式只能用于保护实例故障，而不能保护介质故障，当数据库处于NOARCHIEVELOG模式时，如果日志进行切换，生成新的内容将直接覆盖掉原来的

原创 13、日志文件 -- 重做日志文件

重做日志文件（Redo Log File）通常称作日志文件，它是保证数据安全和数据库备份与恢复的文件。原理：重做记录以循环的方式在SGA（系统全局区）区的重做日志高速缓存区中进行缓存，并且由后台进程LGWR写入到重做日志文件中。ORACLE对数据库所做的修改首先保存在内存中，这样可以提高数据库的性能，因为在内存中的数据进行操作比磁盘中数据进行操作快的多，每隔一段时间（日志信息存放超过3秒）或满足特定条件（发生提交命令或日志缓冲区信息满1/3）就会启动LFWR进程将重做日志记录保存到重做日志文件中。重

原创 12、oracle控制文件

在启动数据库实例时，oracle会根据初始化参数查找控制文件，并读取控制文件中的内容，然后，根据控制文件中的信息（如数据库名称、数据文件和日志文件的名称和位置）在实例和数据库之间建立联系。控制文件记录了对应数据库的结构信息（数据库名称和SID表示。数据文件和日志文件列表，包括文件名称和对应路径信息。数据库创建的时间戳。表空间信息。当前重做日志文件序列号。归档日志信息。检查点信息。回滚段UNDO SEGMENT 的起始和结束。备份数据文件信息。）备份控制文件alter databas

原创 11、触发器

触发器：由“触发事件”来执行，（区别于存储过程的调用或执行是由用户或应用程序来进行的）。能够引起触发器运行的操作就被称作“触发事件”，如执行DML语句（insert 、update 、delete对表或视图执行数据处理操作），执行DDL语句（create、alter、drop 语句在数据库中创建、修改、删除模式对象），引发数据库系统事件（如系统启动或退出、产生的异常错误等）引发用户事件（如登录或退出数据库操作）都可以引起触发事件的运行。格式：CREATE OR REPLACE TRIGGER 触发器名称

原创 10、函数

CREATE OR REPLACE FUNCTION 函数名称【(参数)】 return data_type is 【函数内部变量】BEGIN pl/sql语句;【EXCEPTION】 【异常pl/sql语句】;END;注： 函数参数 （num_pay number） 参数名 参数类型data_type 返回值类型 number/varchar2…删除函数drop function 函数名称;...

原创 9、存储过程

CREATE OR REPLACE PROCEDURE 存储过程名称【(存储过程参数)】IS BEGIN pl/sql语句;【EXCEPTION】 【异常pl/sql语句】;END 存储过程名称；注：1、存储过程参数是存储过程被调用或执行时用到的参数，不是存储过程定义的内部变量，内部变量是is/as 关键字后面定义。2、执行存储过程：sql*plus 中 execute 存储过程名称;pl/sql块 中 begin 存储过程名称; end;3、存储过程的参数包括 IN、OUT

原创 8、PL/SQL流程控制语句

选择语句1、IF (条件表达式) THEN(PL/SQL语句);END IF;2、IF (条件表达式) THEN(PL/SQL语句1);ELSE(PL/SQL语句2);END IF;3、IF (条件表达式1) THEN(PL/SQL语句);ELSEIF (条件表达式2) THEN…(PL/SQL语句2);ELSE(PL/SQL语句3);END IF;4、CASE (变量，存储监测的值即选择器)WHEN (表达式1，通常为常量) THEN(PL/SQL语句1)

原创 7、PL/SQL中特殊数据类型

1 %type2 record3 %rowtype

原创 6、ORACLE常用函数

1 ASCII（c） 返回一个字符的ascii码。2 CHR(i) 返回给出ascii码对应的字符。CONCAT(S1,S2) 字符串S2连接到S1后面。3 INITCAP(s) 使每个单词第一个字母大写，单词间用逗号、空字符、空格区分。4 INSTR(S1,S2[,i][,j]) 返回字符S2 在字符S1中第j 次出现时的位置，搜索从字符S1的第i个字符开始。5 LENGTH(s) 返回字符串s的长度。6 LOWER(s) ； UPPER(s) 返回字符串的小写、大写形式。7

原创 5、SQL语言分类

SQL语言分类数据查询语言DQL :检索数据库中的数据 select数据操作语言DML :改变数据库中的数据 insert update delete事务控制语言TCL :维护数据一致性，commit rollback savepoint数据定义语言DDL :建立修改删除数据库对象 alter drop create数据控制语言DCL :执行权限授予回收，grant revoke...

原创 4、oracle结构、服务器结构

oracle服务器主要由实例、数据库、程序全局区、前台进程组成。实例由系统全局区（SGA）、后台进程（PMON、SMON）组成，其中SGA使用操作系统的内存资源，而后台进程需要使用CPU与内存资源；数据库包含数据文件（data file）、控制文件（control file）、重做日志文件（redo log file）存放在硬盘中；**程序全局区(PGA)**是一个非共享的内存区域，用于管理用户进程的私有资源；前台进程可以划分为用户进程和服务器进程，他们需要使用CPU与内存资源。...

原创 3、逻辑存储结构、物理存储结构

逻辑存储结构：从逻辑的角度分析数据库的构成。oracle存储结构是一种层次结构包括表空间、数据段、数据区、数据块。数据块 data block：由块头、表目录、行目录、空余空间、行数据组成。数据区 extent：由一组连续的oracle数据块构成，一个或多个数据块组成一个数据区、一个或多个数据区组成一个段。段 segment：用于存储表、索引、簇等占用空间的数据对象。表空间 tablespace：将相关逻辑结构（段、数据区）组合在一起，数据块最大的逻辑划分区域，通常用来存放数据表、索引、回滚段等

原创 2、oracle结构、体系结构

实例（instance）:一组ORACLE后台进程及服务器分配的共享区域。数据库（database）:基于磁盘的数据文件、控制文件、日志文件、参数文件、归档日志文件…物理文件集合。数据库服务器：管理数据库的软件工具。数据库的主要功能是存储数据，存储数据的方式为存储结构。分为：物理结构:oracle中操作系统物理文件组成。逻辑结构:oracle内部组织管理数据的方式。用户连接数据库即连接数据库实例，实例与数据库通讯，结果返回给用户。...

原创 1、启动关闭数据库实例

启动实例**startup nomount**;启动实例、不加载数据库**startup mount;**启动实例、加载数据库、且数据库保持关闭状态startup open;启动实例、加载打开数据库（默认）startup force；终止实例、强制重新启动。startup restrict;用于指定受限制的回话方式启动数据库startup pfile;用于指定启动实例时所使用的文本参数文件、filename为文件名。关闭数据库实例shutdown normal;正常关闭shutdown t