随笔分类 - Oracle 11g
OCP读书笔记(27) - 题库(ExamG)
摘要：601.You need to perform a block media recovery on the tools01.dbf data file in the SALES database byusing Recovery Manager (RMAN).Which two are the pr
阅读全文
OCP读书笔记(26) - 题库(ExamF)
摘要：501.Note the output of the following query;SQL> SELECT flashback_archieve_name, status FROM dba_flashback_archieve;FLASHBACK_ARCHIEVE_NAME STATUSFLA1Y
阅读全文
OCP读书笔记(25) - 题库(ExamE)
摘要：401.Which of the following are correct about block media recovery? (Choose all that apply.)A. Physical and logical block corruption is recorded automa
阅读全文
OCP读书笔记(24) - 题库(ExamD)
摘要：327.You have a database with the following tablespaces: SYSTEM, SYSAUX, UNDO, USERS, TEMP.You want to"roll back" the data in the USERS tablespace to t
阅读全文
OCP读书笔记(23) - 题库(ExamC)
摘要：200.Which operation requires that you create an auxiliary instance manually before executing the operation? (Choose all that apply.) A. Backup-based d
阅读全文
OCP读书笔记(22) - 题库(ExamB)
摘要：101.Identify two situations in which you can use Data Recovery Advisor for recovery. (Choose two.) A. The user has dropped an important table that nee
阅读全文
OCP读书笔记(21) - 题库(ExamA)
摘要：Administer ASM disk groupsBack up the recovery catalogConfigure backup settingsConfigure, Monitor Flashback Database and Perform Flashback Database op
阅读全文
Oracle SQL Lesson (11) - 创建其他数据库对象(试图/序列/索引/同义词)
摘要：schema(模式)一个用户下一组对象的集合，一般与用户名一致。视图CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view[(alias[, alias]...)]AS subquery[WITH CHECK OPTION [CONSTRAINT constraint]][WITH READ ONLY [CONSTRAINT constraint]];create view empvu80 as select employee_id, last_name, salary from employees where department_id = 80;desc
阅读全文
Oracle SQL Lesson (10) - 使用DDL语句创建和管理表
摘要：数据库对象TableViewSequenceIndexSynonym对象名称最长30个字符，不能与当前用户下其他对象重名。create table "select" as select * from emp;select * from "select";必须有创建表的权限CREATE TABLE [schema.]table (column datatype [DEFAULT expr][, ...]);conn / as sysdba;create user test identified by test default tablespace user
阅读全文
Oracle SQL Lesson (9) - 操作数据(增删改)
摘要：使用INSERT语句INSERT INTO table [(column [, column...])]VALUES (value [, value...]);INSERT INTO departments(department_id, department_name, manager_id, location_id)VALUES (70, 'Public Relations', 100, 1700);字符和日期需要用单引号INSERT INTO employeesVALUES (114, 'Den', 'Raphealy', ...
阅读全文
Oracle SQL Lesson (8) - 使用集合操作符(Union,Intersect,Minus)
摘要：集合操作符UNION/UNION ALLINTERSECTMINUSUnion All不排序，不去重，其余均升序且去重。create table e1 as select * from emp where deptno in (10,20);create table e2 as select * from emp where deptno in (20,30);select * from e1 union select * from e2 order by 8select * from e1 union all select * from e2 order by 8select * from
阅读全文
Oracle SQL Lesson (7) - 使用子查询
摘要：使用子查询简单子查询SELECT select_listFROM tableWHERE expr operator (SELECT select_list FROM table);子查询可以出现在select,from和where之后，都是可以的。SELECT last_name, salaryFROM employeesWHERE salary > (SELECT salary FROM employees WHERE last_name = 'Abel');SELEC...
阅读全文
Oracle SQL Lesson (6) - 使用Join进行联合查询
摘要：使用连接SQL 1999SELECT table1.column, table2.columnFROM table1[NATURAL JOIN table2] |[JOIN table2 USING (column_name)] |[JOIN table2 ON (table1.column_name = table2.column_name)]|[LEFT|RIGHT|FULL OUTER JOIN table2 ON (table1.column_name = table2.column_name)]|[CROSS JOIN table2];自然连接：SELECT departme...
阅读全文
Oracle SQL Lesson (5) - 使用组函数输出聚合数据
摘要：组函数AVGCOUNTMAXMINSUMVARIANCE：方差STDDEV：标准差SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary)FROM employeesWHERE job_id LIKE '%REP%';SELECT MIN(hire_date), MAX(hire_date)FROM employees;COUNT(expr) 返回expr为非空值的行数:SELECT COUNT(commission_pct)FROM employeesWHERE department_id = 80;select cou
阅读全文
Oracle SQL Lesson (4) - 使用转换函数和条件表达式
摘要：隐式转换select * from emp where empno='7788'字符(char,varchar2)转换为数字(number)或日期(date)数字或日期转换为字符显式转换字符转换为数字或日期(TO_NUMBER, TO_DATE)数字或日期转换为字符(TO_CHAR)TO_CHAR(date, 'format_model')select sysdate, to_char(sysdate,'yyyy') yyyy, to_char(sysdate,'year') year,from dual;select sysda
阅读全文
Oracle SQL Lesson (3) - 使用单行函数自定义输出
摘要：大小写转换函数LOWER('SQL Course') = sql courseUPPER('SQL Course') = SQL COURSEINITCAP('SQL Course') = Sql CourseSELECT employee_id, last_name, department_idFROM employeesWHERE last_name = 'higgins';SELECT employee_id, last_name, department_idFROM employeesWHERE LOWER(last_na
阅读全文
Oracle SQL Lesson (2) - 限制和排序数据
摘要：重建scott用户@?/rdbms/admin/utlsampl.sql@--执行?--$ORACLE_HOME字符区分大小写：SELECT last_name, job_id, department_idFROM employeesWHERE last_name = 'Whalen' ;使用字符函数：SELECT last_name, job_id, department_idFROM employeesWHERE upper(last_name) = 'WHALEN' ;SELECT last_name, job_id, department_idFROM
阅读全文
Oracle SQL Lesson (1) - 使用SQL Select语句获取数据
摘要：第一节课：启动数据库并且使用特定用户连接:su - oracle;启动sqlplus并且使用sys连接：conn / as sysdba;启动数据库：startup;解锁用户：alter user hr identified by hr;alter user hr account unlock;使用hr连接：conn hr/hr;select * from tab;desc employees;select * from departments;开发工具：plsql developer;toad特殊字符：SELECT department_name || q'[ Department&
阅读全文
OCA读书笔记(18) - 使用Support工具
摘要：调查和解决问题 问题：数据库中的任一严重的错误定义为一个问题,一般来说,这些错误包括大家熟悉的ORA-600错误和ORA-04031(共享池超出)错误,涉及数据库问题的所有元数据都存储在ADR中,每个问题分配一个问题键(problem key),它帮助标示和描述该问题,问题键包含oracle错误编码
阅读全文
OCP读书笔记(20) - 复制数据库
摘要：没有连接到target的复制 将orcl数据库的备份复制为orcl1 一、创建orcl的备份： run{ backup database plus archivelog;} 二、复制数据库为orcl1 1. 为orcl1创建参数文件: cd $ORACLE_HOME/dbs vi initorcl1
阅读全文
浙公网安备 33010602011771号