Oracle Memorandum 1, oracle Memorandum

Oracle Memorandum 1, oracle Memorandum

Database Administrator: Install and upgrade the Oracle database to create a database, tablespace, table, view, and index... Develop and implement backup and repair plans database permission management, tuning, and troubleshooting. For senior DBAs, they are required to participate in project development. They write SQL statements, stored procedures, triggers, rules, and constraints, package create table emp (empno number (4) primary key, ENAME VARCHAR2 (10), JOB VARCHAR2 (9), mgr number (4), hiredate date, sal number (7, 2 ), comm number (7,2), deptno number (4); create table dept (deptno number (4), DNAME VARCHAR2 (14), LOC VARCHAR2 (13 )); create table salgrade (grade number, losal number, hisal number); create table bonus (ename varchar (10), J OB VARCHAR2 (9), sal number, comm number); insert into dept values (10, 'accounting', 'New YORK '); insert into dept values (20, 'Research ', 'Dallas'); insert into dept values (30, 'sales', 'Chicago '); insert into dept values (40, 'operations ', 'boston '); insert into emp values (7369, 'Smith', 'cler', 7902, TO_DATE ('17-12-1980 ', 'dd-mm-yyyy '), 800, null, 20); insert into emp values (7499, 'allen', 'salesman', 7698, TO_DATE ('20 -2-1981 ', 'dd-mm-yyyy'), 1600,300, 30); insert into emp values (7521, 'ward ', 'salesman', 7698, TO_DATE ('22-2-1981 ', 'dd-mm-yyyy'), 1250,500, 30); insert into emp values (7566, 'Jones ', 'manager', 7839, TO_DATE ('2-4-1981 ', 'dd-mm-yyyy'), 2975, NULL, 20); insert into emp values (7654, 'martin ', 'salesman ', 7698, TO_DATE ('28-9-1981 ', 'dd-mm-yyyy'), 7698, 30); insert into emp values (, 'bucke', 'manager ', 7839, TO_DATE ('1-5-198 1 ', 'dd-mm-yyyy'), 2850, NULL, 30); insert into emp values (7782, 'clark', 'manager', 7839, TO_DATE ('9-6-1981 ', 'dd-mm-yyyy'), 2450, NULL, 10); insert into emp values (7839, 'King', 'President ', NULL, TO_DATE ('17-11-1981 ', 'dd-mm-yyyy'), 5000, NULL, 10); insert into empvalues (7788, 'Scott ', 'analyst ', 7566, '19-December-1987 ', 3000.00, NULL, 20); insert into emp values (7844, 'turner', 'salesman', 7698, TO_DATE ('8-9-1981 ', 'dd-mm-yyyy'), 1500, 7900); insert into emp values (7698, 'James ', 'cler', 950, TO_DATE ('3-12-1981', 'dd-mm-yyyy, NULL, 30); insert into emp values (7902, 'Ford ', 'analyst', 7566, TO_DATE ('3-12-1981', 'dd-mm-yyyy '), 3000, NULL, 20); insert into emp values (7934, 'miller ', 'cler', 7782, TO_DATE ('23-1-1982 ', 'dd-mm-yyyy'), 1300, NULL, 10); insert into salgrade values (1,700,120 0); insert into salgrade values (1400,); INSERT INTO SALGRA De values (2000, 3000); insert into salgrade values (9999,); insert into salgrade values (,); SELECT * from dept; SELECT * from emp; SELECT * from salgrade; create user DB_USER identified by DB_USER_PW; -- CREATE a database/USER and perform the drop user DB_USER (CASCADE); passw; -- modify the USER Password passw DB_USER under system; -- The Administrator changes the USER Password alter user DB_USER identified by DB_USER_PW; -- the Administrator changes the USER Password shutdown; startup; SHOW parameter; and grants Permissions: Method 1: grant dba to DB_USER; -- Method 2: grant connect to DB_USER; -- GRANT the user the connection permission grant resource to DB_USER; -- assign the user the RESOURCE permission method 3: CONNECT DB_USER/DB_USER_PW as sysdba or as sysoper; -- connect to the database and GRANT the permission grant create session to DB_USER; -- Grant the USER the permission to CREATE a session and do NOT know how to use the create user role not identified for the Oracle database; -- when creating a role, you do NOT need to enter a password to log on to display all users in the database: select user name from dba_USER s; displays the system permissions of users in the database: SELECT * FROM dba_sys_pr Ivs WHERE GRANTee = 'Role name; SELECT * FROM role_sys_privs WHERE role = 'role name; show the object permissions of users in the database: SELECT * FROM dba_tab_privs WHERE GRANTee = 'Role name '; show the role of the user in the database: SELECT * FROM dba_role_privs WHERE GRANTee = 'username'; show the permissions of the user in the database SELECT * FROM dba_sys_privs Oracle SELECT * FROM dba_roles; SELECT * FROM system_privilege_map order by name for all Oracle system permissions; select distinct privilege FROM dba_tab_privs for all Oracle Objects; display the full NAME of the current database: SELECT * FROM global_NAME; conn sys/change_on_install as sysdba; -- log on to drop table r as the system database administrator; -- delete the RENAME r TO r1 TABLE; -- RENAME the TABLE name alter table r RENAME column A1 TO A2; -- MODIFY the column name alter table r add a d; -- ADD the column attribute alter table r modify a d; -- alter table r drop a; -- delete insert into r VALUES (); -- INSERT tuples (TABLE content) update table r set a where ...; -- Update tuples (Table content) delete from r WHERE ...; -- delete tuples (TABLE content), write logs, and restore the TRUNCATE TABLE R;__ Delete the tuples (Table content). The speed is fast. If no logs are written, the SHOW linesize cannot be restored. -- display the row width. The default value is 80 SET linesize NUMBER; -- SET the row width to number set pagesize NUMBER; -- SET the NUMBER of lines displayed on each page. The default value is 14 alter session set nls_date_format = 'yyyy-mm-dd '; _ the method used to set the date to NVL (D, 0) -- how to handle null values -- add the constraint alter table r modify a not null; -- non-empty alter table r add constraint cardunique UNIQUE (A); -- ID card unique alter table r add constraint ADDresscheck CHECK (A in ('beijing', 'shanghai ', 'guangzhou '); -- delete the constraint ALTER TABLE r drop constraint a unique/CHECK; alter table r drop constraint primary key CASCADE; UPDATE r set a where ...; -- UPDATE tuples (Table content) UPDATE r SET (a1, a2, a3) = (SELECT (A1, A2, A3) FROM r WHERE ...) WHERE ...; delete from r WHERE ...; -- delete the tuples (Table content) to display all TABLEs under the current USER: SELECT TABLE_NAME from user _ TABLEs; display all TABLEs accessible to the current USER: SELECT TABLE_NAME FROM all_TABLEs; display all tables in the database. It must be dba or have the SELECT any TABLE permission. SELECT TABLE_NAME FROM dba_T ABLEs; CREATE profile limit num1 password_lock_time num2; -- when the number of input password errors reaches num1, the specified time num2CREATE profile password_life_time_NAME limit password_life_time numword1 password_grace_time num2 limit NUMBER3; -- the password is changed in num1, and the grace period is num2. After NUMBER3 days, you can reuse DROP profile profile_NAME (CASCADE); -- delete the file alter user DB_USER profile profile_NAME; -- implement this locking rule for a USER: ALTER USER D B _USER account unclok; -- Unlock savapoint a; -- set the rollback point and save point to prevent the loss of important information in the database, there is no limit on the number of saved points (multiple ROLLBACK points can be set) rollback to a; -- rollback to this point ROLLBACK; -- rollback to the Initial State COMMIT; -- submit, operations required every day, but all storage points will be deleted, the lock will be released, use with caution, make sure that the database does not exist during use! Paging: ROWNUM classic example: SELECT * FROM (SELECT. *, rownum rn FROM (SELECT * from emp) a WHERE rownum <= 10) WHERE rn> = 6; copy part: create table myTABLE (id, NAME) as SELECT EMPno, ename from emp; UNION merge query deduplicated union all do not duplicate INTERSECT intersection query MINUS difference set query, first MINUS the intersection of the two TO_DATE ('2017-12-12 ', 'yyyy-mm-dd') TO_DATE ('1970/12', 'yyyy/mm/dd') -- insert into emp values (1991/12, 'test _ 2', 'manager', 7782, TO_DATE ('2017-9-9 12:10:10 ', 'yyyy-mm- Dd hh24: mi: ss'), 1000,100, 10); insert into emp values (1236, 'test _ 3', 'manager', 7782, sysdate, 1000,100, 10 ); SELECT ename, TO_CHAR (hiredate, 'yyyy-mm-dd hh24: mi: ss') from emp; -- query the specific time, hour, minute, second. The bbs system always uses SELECT ename, TO_CHAR (hiredate, 'yyyy-mm-dd hh24: mi: ss'), TO_CHAR (sal, 'l99, 100') from emp; -- display the local currency SELECT * from emp where TO_CHAR (hiredate, 'yyyy') = 1980; -- display the number of employees who have joined the company in a certain year, one month in mm, yyyy-mmSET translate read is the same for a year in January. ONLY; -- transaction processing set function: LOWER (A); UPPER (A); LENGTH (A); SUBSTR (A, m, n); -- m indicates the starting position, n indicates the number of characters obtained. uppercase letters are obtained. select upper (SUBSTR (A,) from emp; lowercase letters after completion: SELECT lower (SUBSTR (A, 2, LENGTH () -1) FROM r; merge: select upper (SUBSTR (A, 1,1) | lower (SUBSTR (A, 2, LENGTH (A)-1) FROM r; REPLACE (A, search_string, REPLACE_string); -- REPLACE the character INSTR (char1, char2, [, n [, m]; -- Obtain the mathematical function of the string position in the string: ROUND (A, m); -- rounding, retaining m-bit TRUNC (A, m); -- truncating numbers, retaining m-bit, ignoring the remainder MOD (A, n); -- Modulo nFLOOR (A) for A; -- returns the largest integer less than or equal to n, ignoring the remainder CEIL (); -- returns the smallest integer greater than or equal to n select sysdate from dual; -- returns the system time SELECT * from emp where sysdate> ADD_months (hiredate, 8 ); returns the employee SELECT trunc (sysdate-hiredate) "Number of days of employment" from emp; -- returns the number of days of employment SELECT hiredate, ename from emp where last_day (hiredate) -2 = hiredate; -- returns the employee conversion function for the last 2nd days of each month: TO_CHAR (A, ''); TO_DATE (A,''); system function: SELECT sys_context ('user env', 'db _ name') FROM dual; -- langu Age: current language, SESSION_USER: current user, current_schema: current scheme for row migration: insert into r2 (A1, A2, A3) SELECT a1, a2, a3 FROM r1 WHERE ...; export: export the table, export the scheme, export the database EXP command, and enter the bin directory USER id, TABLEs, owner, full = y, inctype, rows, in oracle from the command line, file: export the table structure without importing data: exp user id = learning/123456 @ XE TABLEs = (EMP) file = E: \ DeskTop \ EMP. dmp rows = n export table: exp user id = learning/123456 @ XE TABLEs = (EMP) file = E: \ DeskTop \ EMP. in dmp, export the quick exp user id = learning/123456 @ xe t. ABLEs = (EMP) file = E: \ DeskTop \ EMP. dmp direct = y export scheme: exp user id = learning/123456 @ XE owner = learning file = E: \ DeskTop \ EMP. dmp export all: exp user id = system/123456 @ XE full = y inctype = complete file = E: \ DeskTop \ EMP. the structure of the dmp imported table without importing the table data: imp user id = learning/123456 @ XE TABLEs = (EMP) file = E: \ DeskTop \ EMP. dmp rows = n import table: imp user id = learning/123456 @ XE TABLEs = (EMP) file = E: \ DeskTop \ EMP. dmp imports data. If the table already exists, only import data: imp user id = learning/1 23456 @ XE TABLEs = (EMP) file = E: \ DeskTop \ EMP. dmp ignore y import TABLE to other user imp user id = system/123456 @ xe table = EMP file = E: \ DeskTop \ EMP. dmp TOUSER = learning; import database: imp user id = system/123456 @ XE full = y file = E: \ DeskTop \ EMP. dmp TOUSER = learning; CREATE Table space: create tablespace data01 datafile 'e: \ data01.dbf' size 20 m uniform size 128 k; ADD data file: alter tablespace data01 ADD datafile 'e: \ data02.dbf 'size 20 m; Increase the data file size: ALTER TABLESP ACE data01 'e: \ data01.dbf' resize 20 m; Set automatic file growth: alter tablespace data01 'e: \ data01.dbf' auTOextEND on next 10 m maxsize 500 m; when the disk is damaged: Determine the tablespace where the data file is located: SELECT TABLESPACE_NAME FROM dba_data_files WHERE file_NAME = 'e: \ data01.dbf '; take the TABLESPACE offline: alter tablespace data01 offline; move the TABLESPACE: host move E: \ data01.dbf D: \ data01.dbf logical modification of database files: alter tablespace data01 RENAME datafile 'e: \ data01.dbf to d: \ data01.dbf '; make the TABLESPACE online: AL Ter tablespace data01 online; use the database TABLESPACE: create table r (deptno NUMBER (4), dNAME varchar2 (14), loc varchar2 (13) TABLESPACE data01; query tables in the TABLE space: SELECT * FROM all_TABLEs WHERE TABLESPACE_NAME = 'data01'; // specify the tablespace in which the table is queried in uppercase: SELECT TABLESPACE_NAME, TABLE_NAME from user _ TABLEs WHERE TABLE_NAME = 'emp '; // Delete tablespaces in uppercase: drop tablespace DATA01 including contents and datafiles; Table-Level Definition: After defining a column, add a constraint column-level definition: create a stored procedure by adding constraints while defining columns. Create table mytest (name VARCHAR2 (30), password VARCHAR2 (30); create or replace procedure B ISBEGIN -- execute partial INSERT INTO mytest VALUES ('jack ', '20140901'); END;/create or replace procedure B ISBEGIN -- execute partial DELETE FROM mytest WHERE name = 'jack'; END;/REPLACE indicates that if the same process name exists, REPLACE it, B Indicates the process name view error message: SHOW error; how to CALL this process 1: EXEC process name (parameter value 1, parameter value 2); 2: CALL process name (parameter value 1, parameter Value 2); when defining a constant, we recommend that you use v _ as the prefix v_sal to define a constant, and c _ as the prefix c_rate. when defining a cursor, we recommend that you use _ cursor Suffix EMP_cursor when defining exceptions, we recommend that you use e _ as the prefix e_errorSET serveroutput ON; -- enable the display content SET serveroutput OFF -- disable the display content BEGINDBMS_OUTPUT.PUT_LINE ('helloworld'); END; /-- add this last -- display employee name, salary DECLAREv_ename VARCHAR2 (5); v_sal NUMBER (); begin select ename, sal INTO v_ename, v_sal from emp where EMPno = & no; -- output DBMS_OUTPUT.PUT_LINE ('employee: '| v_ename |', his salary is: '| v_sal ); -- Exception Handling EXCEPTIONWHEN NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE ('the input is incorrect. Please try again! '); END;/case create procedure a1 (a1name VARCHAR2, a1sal NUMBER) ISBEGIN -- execute, modify the salary update emp set sal = a1sal WHERE ename = a1name; END according to the original name; /EXEC a1 ('Scott ', 150); -- So SCOTT's salary is changed to 150 -- use function to query the salary of an employee create function fj_fun1 (fjname VARCHAR2) return number is yearsal NUMBER (); BEGIN -- execute SELECT sal * 12 + NVL (comm, 0) * 12 INTO yearsal from emp where ename = fjname; RETURN yearsal; END; /-- call var abc NUMBER; CALL fj _ Fun1 ('Scott ') INTO: abc; -- create a package. Declare that the PACKAGE has a process update_sal, declare that the PACKAGE has a FUNCTION annual_income: create package fj_package ISPROCEDURE update_sal (name VARCHAR2, newsal NUMBER); FUNCTION annual_income (name VARCHAR2) return NUMBER; /-- Implement the package body for the sp_package to modify the salary of an employee OR query the salary of an employee. create or replace package body fj_package is procedure update_sal (name VARCHAR2, newsal NUMBER) is begin update emp set sal = newsal WHERE ename = name; END; FUNCTION annual_income (n Ame VARCHAR2) return number is annual_salary NUMBER; begin select sal * 12 + NVL (comm, 0) INTO annual_salary from emp where ename = name; RETURN annual_salary; END; /-- CALL fj_package.update_sal ('Scott ', 1400); -- enter the employee NUMBER and display the employee name, salary, and personal income tax. The tax rate is 0.03. For example, DECLARE c_tax_rate NUMBER (0.03): =; v_name VARCHAR2 (5); -- EMP. ename % TYPEv_sal NUMBER (7,2); -- EMP. sal % TYPEv_tax_sal NUMBER (7,2); BEGIN -- execute SELECT ename, s Al INTO v_name, v_sal from emp where EMPno = & no; -- Calculate personal income tax v_tax_sal: = v_sal * c_tax_rate; -- note the colon -- output DBMS_OUTPUT.PUT_LINE ('employee is: '| v_name |', salary: '| v_sal |', the personal income tax to be paid is: '| v_tax_sal); END; /-- similar to the struct in advanced languages -- PL/SQL record instance DECLARE -- indicates that a PL/SQL RECORD type EMP_record_type should be defined. The types include name, salary, titleTYPE EMP_record_type IS RECORD (name EMP. ename % TYPE, salary EMP. sal % TYPE, title EMP. job % TYPE); -- defines a variable whose TYPE is EMP_record_typefj_reco. Rd EMP_record_type; -- v_name VARCHAR2 (45); BEGINSELECT ename, sal, job INTO fj_record from emp where EMPno = 7788; staff ('employee name: '| fj_record.name | ', salary: '| fj_record.salary); END;/-- equivalent to the array DECLARE in the advanced language -- defines a PL/SLQL table type, which is used to store EMP. ename % TYPETYPE fj_TABLE_type is table of emp. ename % typeindex by BINARY_INTEGER; -- indicates that the subscript is sorted BY integer, which can be negative fj_TABLE fj_TABLE_type; begin select ename INTO fj_TABLE (0) FROM EMP WHERE EMPno = 7788; DBMS_OUTPUT.PUT_LINE ('employee name: '| fj_TABLE (0); -- the subscript is the same as the subscript above;/DECLARE -- defines a PL/SLQL table type, this type is used to store EMP. ename % TYPETYPE fj_TABLE_type is table of emp. ename % typeindex by BINARY_INTEGER; -- indicates that the subscript is sorted BY integer, which can be negative fj_TABLE fj_TABLE_type; begin select ename INTO fj_TABLE (0) from emp; DBMS_OUTPUT.PUT_LINE ('employee Name: '| fj_TABLE (0); -- the subscript is the same as the subscript above. END;/-- enter the Department number, show the names of all employees in the Department and their salaries DECLARE -- Define the cursor type fj_EMP_cursorTYPE fj _ EMP_cursor is ref cursor; -- defines the CURSOR variable test_cursor fj_EMP_cursor; -- defines the variable v_ename EMP. ename % TYPE; v_sal EMP. sal % TYPE; BEGIN -- execute -- combine test_cursor and a SELECT statement with OPEN test_cursor for select ename, sal from emp where deptno = & no; -- cyclically retrieve LOOPFETCH test_cursor INTO v_ename, v_sal; -- determine whether test_cursor is null exit when test_cursor % NOTFOUND; DBMS_OUTPUT.PUT_LINE ('employee name: '| v_ename |', salary '| v_sal); end loop; END; /-- Based on the above, if an employee's salary is less than 200 yuan, it will increase $100 DECLARE -- defines the CURSOR type fj_EMP_cursorTYPE fj_EMP_cursor is ref cursor; -- defines the CURSOR variable test_cursor fj_EMP_cursor; -- defines the variable v_ename EMP. ename % TYPE; v_sal EMP. sal % TYPE; BEGIN -- execute -- combine test_cursor and a SELECT statement with OPEN test_cursor for select ename, sal from emp where deptno = & no; -- cyclically retrieve LOOPFETCH test_cursor INTO v_ename, v_sal; -- judge the salary level and determine whether to update -- determine whether test_cursor is empty exit when test_cursor % NOTFOUND; DBMS_OUTPUT.PUT_LINE ('employee name: '| v_ename |', '| V_sal); end loop; END;/-- write a process, enter the employee name, and the employee's salary below 2000 increases by 10% create or replace procedure fj_pro6 (fjname VARCHAR2) IS -- defines v_sal EMP. sal % TYPE; BEGIN -- execute SELECT sal INTO v_sal from emp where ename = fjname; -- judge IF v_sal <2000 thenupdate emp set sal = sal * 1.1 WHERE ename = fjname; end if; END;/-- call EXEC fj_pro6 ('Scott '); -- add 200 if the employee's allowance is zero, and add 100 create or replace procedure fj_pro7 (fjname VARCHAR2) if not zero) IS -- defines v_comm EMP. c Omm % TYPE; BEGIN -- execute SELECT comm INTO v_comm from emp where ename = fjname; -- judge IF v_comm <> 0 thenupdate emp set comm = comm + 100 WHERE ename = fjname; else update emp set comm = comm + 200 WHERE ename = fjname; end if; END;/-- call EXEC fj_pro7 ('Scott '); -- three condition branch IF-THEN-ELSEIF-ELSE -- enter employee number. If the employee's position is PRESIDENT, his salary will be increased by 1000, if the employee is a MANAGER, his salary will be increased by 500, and the salary of other employees will be increased by 200. Create or replace procedure fj_pro8 (fjno NUMBER) IS -- defines v_job EMP. job % TYPE; BEGIN -- execute SELECT job INTO v_job from emp where EMPno = fjno; IF v_job = 'President 'thenupdate emp set sal = sal + 1000 WHERE EMPno = fjno; ELSIF v_job = 'manager' thenupdate emp set sal = sal + 500 WHERE EMPno = fjno; elseupdate emp set sal = sal + 200 WHERE EMPno = fjno; end if; END; /-- call EXEC fj_pro8 (7839); -- in the LOOP statement LOOP -- users TABLE, create table users (userno NUMBER, username VARCHAR2 (40) is added from 1 )); create or replace procedure fj_pro9 (fjname VARCHAR2) IS -- Definition: = indicates the value of v_num NUMBER: = 1; beginloopinsert into users VALUES (v_num, fjname ); -- determine EXIT condition exit when v_num = 10; -- auto-incrementing v_num: = v_num + 1; end loop; END;/-- call EXEC fj_pro9 ('root1 '); -- in this case, the table will have ten username root1 tuples create or replace procedure fj_pro10 (fjname VARCHAR2) IS -- Definition: = indicates the value of v_num NUMBER: = 11; BEGINWHILE v_num <= 20 loopinsert into users VALUES (v_num, fjname); v_num: = v_num + 1; end loop; END;/-- call EXEC fj_pro10 ('root1 ');