HighLight in your life~

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.htm#sthref912

1. ADD_MONTHS
2. CURRENT_DATE
3. CURRENT_TIMESTAMP
4. DBTIMEZONE
5. EXTRACT (datetime)
6. FROM_TZ
7. LAST_DAY
8. LOCALTIMESTAMP
9. MONTHS_BETWEEN
10. NEW_TIME
11. NEXT_DAY
12. ROUND (date)
13. SESSIONTIMEZONE
14. SYSDATE
15. SYSTIMESTAMP
16. TO_CHAR (date)
17. TO_TIMESTAMP
18. TRUNC (date)
19. TZ_OFFSET

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.htm#sthref912

SQL Functions(10g) - Character Functions

1. CHR(N) - 바이너리 값 N을 VARCHAR2 값으로 변환한 값을 반환한다.

SQL> SELECT CHR(72)||CHR(69)||CHR(76)||CHR(76)||CHR(79) AS "HELLO" FROM DUAL;

A(65), B(66), C(67), D(68), E(69), F(70), G(71), H(72), I(73)
J(74), K(75), L(76), M(77), N(78), O(79), P(80), Q(81), R(82)
S(83), T(84), U(85), V(86), W(87), X(88), Y(89), Z(90)

2. CONCAT(S1, S2) - 문자열 S1과 문자열 S2를 연결하여 합친다.

SQL> SELECT CONCAT('Hello',' World') AS "Hello World" FROM DUAL;

SQL> SELECT CONCAT(CONCAT(LAST_NAME, '''s salary is '), SALARY ) AS "Salary"  FROM EMPLOYEES;
 

3. INITCAP(S) - 문자열 S에서 각 단어에서 시작하는 첫 문자를 대문자로 시작한다.

SQL> SELECT INITCAP('hello world') AS "Hello World", INITCAP('HELLO WORLD') AS "HELLO WORLD", INITCAP('hElLO wOrLd') AS "hElLO wOrLd" FROM DUAL;

4.  LOWER(S) - 문자열 S의 알파벳을 모두 소문자로 변환하여 반환한다.

SQL> SELECT LOWER('HELLO WORLD') AS "HELLO WORLD" FROM DUAL;

SQL> SELECT LAST_NAME AS "LAST_NAME", LOWER(LAST_NAME) AS "LOWERCASE" FROM EMPLOYEES WHERE ROWNUM < 3;

5. UPPER - 문자열 S의 알파벳을 모두 대문자로 변환하여 반환한다.

SQL> SELECT UPPER('hello world') AS "hello world" FROM DUAL;

SQL> SELECT LAST_NAME AS "LAST_NAME", UPPER(LAST_NAME) AS "UPPER" FROM EMPLOYEES WHERE ROWNUM < 3;

6. LPAD(S, N, P) - 문자열 S의 길이가 N보다 작으면 왼쪽에 P문자를 N-LENGTH(S) 만큼 추가한다.

SQL> SELECT LPAD('Star',7,'*') AS "Star 7", LPAD('Star',4,'*') AS "Star 4", LPAD('Star',3,'*') AS "Star 3" FROM DUAL;

7. RPAD(S, N, P) - 문자열 S의 길이가 N보다 작으면 오른쪽에 P문자를 N-LENGTH(S) 만큼 추가한다.

SQL> SELECT RPAD('Star',7,'*') AS "Star 7", RPAD('Star',4,'*') AS "Star 4", RPAD('Star',3,'*') AS "Star 3" FROM DUAL;

SQL> SELECT LAST_NAME, RPAD(' ', SALARY/1000/1, '*') "SALARY" FROM EMPLOYEES
ORDER BY LAST_NAME;

8. RELPACE(S, S1, S2) - 문자열 S에서 문자 혹은 문자열 S1을 찾아 S2로 바꾼 결과를 반환한다.

SQL> SELECT REPLACE('There is no blank!.', ' ', '*') "Changes" FROM DUAL;

SQL> SELECT REPLACE('JACK and JUE','J','BL') "Changes" FROM DUAL;

9. TRIM - LEADING(왼쪽 제거), TRAILING(오른쪽 제거), BOTH(왼쪽, 오른쪽 제거) 사용 가능

              제거 해야 할 문자가 없으면, blank space 제거, 그리고 BOTH(양쪽)에서 blank space 제거

SQL> SELECT TRIM(LEADING '*' FROM '***Hello***World***') AS "LEADING" FROM DUAL;

SQL> SELECT TRIM(TRAILING '*' FROM '***Hello***World***') AS "TRAILING" FROM DUAL;

SQL> SELECT TRIM(BOTH '*' FROM '***Hello***World***') AS "BOTH" FROM DUAL;

SQL> SELECT TRIM('   Hello   World   ') AS "BOTH" FROM DUAL;

10. LTRIM(S, S1) - 문자열 S에서 문자 혹은 문자열 S1을 왼쪽에서 모두 지운 값을 반환한다.

                          제거 해야 할 문자가 없으면, 왼쪽만 blank space 제거

SQL> SELECT '***Hello***World***' AS "Hello World", LTRIM('***Hello***World***', '*') AS "LTRIM" FROM DUAL;

SQL> SELECT '   Hello   World   ' AS "Hello World", LTRIM('   Hello   World   ') AS "LTRIM" FROM DUAL;

11. RTRIM(S, S1) - 문자열 S에서 문자 혹은 문자열 S1을 오른쪽에서 모두 지운 값을 반환한다.

                          제거 해야 할 문자가 없으면, 오른쪽만 blank space 제거

SQL> SELECT '***Hello***World***' AS "Hello World", RTRIM('***Hello***World***', '*') AS "RTRIM" FROM DUAL;

SQL> SELECT '   Hello   World   ' AS "Hello World", RTRIM('   Hello   World   ') AS "RTRIM" FROM DUAL;

SQL> SELECT RTRIM('BROWNING: ./=./=./=./=./=.=','/=.') "RTRIM example" FROM DUAL;

12. SUBSTR(S, M, N) - 문자열 S를 M번째 CHAR부터 N의 길이로 잘라낸다.

SQL> SELECT SUBSTR('Hello World', 1, 5) AS "Hello", SUBSTR('Hello World', 7, 5) AS "World", SUBSTR('Hello World', -5, 5) AS "Hello"  FROM DUAL;

SQL>SELECT SUBSTR('Hello World', 0, 1) FROM DUAL;
SQL>SELECT SUBSTR('Hello World', 0, 2) FROM DUAL;
SQL>SELECT SUBSTR('Hello World', 1, 2) FROM DUAL;

SQL>SELECT SUBSTR('Hello World', 0) FROM DUAL;
SQL>SELECT SUBSTR('Hello World', 1) FROM DUAL;
SQL>SELECT SUBSTR('Hello World', 5) FROM DUAL;

13. INSTR(S,S1, M, N) - 문자열 S에서 문자 혹은 문자열 S1의 시작 위치를 반환한다.
                                 단, 시작 위치는 M이고 N번째 S1의 시작 위치를 반환한다.

SQL> SELECT INSTR('HELLO WORLD','O', 1, 1) "Instring1", INSTR('HELLO WORLD','O', 1, 2) "Instring2", INSTR('HELLO WORLD','O', 1, 3) "Instring3" FROM DUAL;

SQL>SELECT INSTR('HELLO WORLD','O', -1, 1) "Instring" FROM DUAL;

SQL>SELECT INSTR('HELLO WORLD','O', -7, 1) "Instring" FROM DUAL;

14. TRANSLATE(S, S1, R) - 문자열 S에서 문자 S1을 찾아 R로 바꾼 값을 반환한다.

SQL> SELECT TRANSLATE('0123456789', '0123456789', 'helloworld') FROM DUAL;
SQL> SELECT REPLACE('0123456789', '1', 'x') FROM DUAL;

SQL> SELECT TRANSLATE('A0B1CDEF2G3H9', '+.0123456789', ' ') FROM DUAL;
SQL> SELECT TRANSLATE('A0B1CDEF2G3H9', '1234567890', ' ') FROM DUAL;

SQL> SELECT NVL(LENGTH(TRANSLATE('A0B1CDEF2G3H9', '+.0123456789', ' ')), 0) FROM DUAL;
SQL> SELECT NVL(LENGTH(TRANSLATE('123456789', '+.0123456789', ' ')), 0) FROM DUAL;

SQL> SELECT TRANSLATE('123전화번호', '0123456789'||'전화번호', 'ABCDEFGHIJKLMN') FROM DUAL;

15. LENGTH(S) - 문자열 S의 길이를 반환한다.

SQL> SELECT LENGTH('HelloWorld') FROM DUAL;

SQL>SELECT LENGTH(HelloWorld) FROM DUAL;

SQL>SELECT LENGTH('01234') FROM DUAL;
SQL>SELECT LENGTH(01234) FROM DUAL;

16. REGEXP_REPLACE

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions130.htm#i1305521

SQL> SELECT
  REGEXP_REPLACE(PHONE_NUMBER,
                 '([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})',
                 '(\1) \2-\3') "REGEXP_REPLACE"
  FROM EMPLOYEES;

SQL> SELECT PHONE_NUMBER FROM EMPLOYEES;

SQL> SELECT
  REGEXP_REPLACE(COUNTRY_NAME, '(.)', '\1 ') "REGEXP_REPLACE"
  FROM COUNTIRES;

17. REGEXP_SUBSTR

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions131.htm#i1239858

18. SOUNDEX(S) - 문자열 S와 소리가 비슷한 것들 나타낸다.

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions148.htm#i78853

SQL> SELECT LAST_NAME, FIRST_NAME
         FROM HR.EMPLOYEES
         WHERE SOUNDEX(LAST_NAME) = SOUNDEX('SMYTHE');

SQL Functions(10g) - Numeric Functions

1. ABS(N) : N의 절대 값을 반환한다.

SQL> SELECT ABS(3) AS "양수", ABS(-3) AS "음수" FROM DUAL;

         양수          음수

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

             3               3

2. CEIL(N) : N 이상의 최초의 정수를 반환한다.

SQL> SELECT CEIL(0.9), CEIL(1.3), CEIL(1.5), CEIL(1.7), CEIL(1.9), CEIL(2.1)  FROM DUAL;

 CEIL(0.9)  CEIL(1.3)  CEIL(1.5)  CEIL(1.7)  CEIL(1.9)  CEIL(2.1)
---------- ---------- ---------- ---------- ---------- ----------
         1          2          2          2          2          3

3. FLOOR(N) : N 이하의 최초의 정수를 반환한다.

SQL> SELECT FLOOR(0.9), FLOOR(1.3), FLOOR(1.5), FLOOR(1.7), FLOOR(1.9), FLOOR(2.1)  FROM DUAL;

FLOOR(0.9) FLOOR(1.3) FLOOR(1.5) FLOOR(1.7) FLOOR(1.9) FLOOR(2.1)                                                                                                                                      
---------- ---------- ---------- ---------- ---------- ----------

0          1          1          1          1          2

4. MOD(M,N) : M을 N으로 나누었을 때의 나머지를 반환한다.

SQL> SELECT MOD(10,1), MOD(10,2), MOD(10,3), MOD(10,4), MOD(10,5) FROM DUAL;

MOD(10,1)  MOD(10,2)  MOD(10,3)  MOD(10,4)  MOD(10,5)
---------- ---------- ---------- ---------- ----------
         0          0          1          2          0

5. POWER(M,N) : M의 N 승을 반환한다.

SQL> SELECT POWER(2,1), POWER(2,2), POWER(2,3) FROM DUAL;

POWER(2,1) POWER(2,2) POWER(2,3)                                                                                                                                                                       
---------- ---------- ----------

 2          4          8    

6. ROUND(M,N) : M을 소수점 N이 양수일 때는 소수 'N+1' 자리에서 반올림한 값을 반환한다.
                       단, N이 음수일 때는 -(N) 자리에서 반올림한다. -(N) 미만의 자리수는 버린다.

SQL> SELECT ROUND(143.456,1), ROUND(143.456,2), ROUND(143.456,3), ROUND(143.456,-1), ROUND(143.456,-2) FROM DUAL; 

ROUND(143.456,1) ROUND(143.456,2) ROUND(143.456,3) ROUND(143.456,-1) ROUND(143.456,-2)                                                                                                                 
---------------- ---------------- ---------------- ----------------- -----------------              

143.5                  143.46               143.456                    140               100 

7. SQRT(N) : '루트 N'을 의미한다.

SQL> SELECT SQRT(25), SQRT(36) FROM DUAL;

  SQRT(25)   SQRT(36)                                                                                                                                                                                  

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

 5          6

8. TRUNC(M,N) : M을 소수점 N이 양수일 때는 소수 'N+1' 이하의 자리를 절삭한 값을 반환한다.
                       단, N이 음수일 때는 -(N) 이하의 자리를 절산한 값을 반환한다.

SQL> SELECT TRUNC(143.456,1), TRUNC(143.456,2), TRUNC(143.456,3), TRUNC(143.456,-1), TRUNC(143.456,-2) FROM DUAL;

TRUNC(143.456,1) TRUNC(143.456,2) TRUNC(143.456,3) TRUNC(143.456,-1) TRUNC(143.456,-2)

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

143.4                  143.45                 143.456               140                      100

9. WIDTH_BUCKET(P, M, N, L) : P컬럼 값을 기준으로 L개의 그룹으로 나눈다. 범위는 M부터 N사이의 값이 기준이다.

예를 들면, 아래의 WIDTH_BUCKET(SAL, 100, 5000, 5)의 집단은 아래와 같다.
1집단: 100~1000
2집단: 1001~2000
3집단: 2001~3000
4집단: 3001~4000
5집단: 4001~5000

SQL> SELECT EMPNO, SAL, WIDTH_BUCKET(SAL, 100, 5000, 5) FROM EMP;

Oracle Database 10g SQL Fundamentals II - Practice 6
6) Retrieving Data Using Subqueries

1.
SELECT last_name, department_id, salary
FROM employees
WHERE (salary, department_id) IN (SELECT salary, department_id
     FROM  employees
     WHERE  commission_pct IS NOT NULL)
;

2.
SELECT e.last_name, d.department_name, e.salary
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND (salary, NVL(commission_pct,0)) IN
                               (SELECT salary, NVL(commission_pct,0)
          FROM employees e, departments  d
          WHERE e.department_id = d.department_id
          AND d.location_id = 1700)
;

3.
SELECT last_name, hire_date, salary
FROM employees
WHERE (salary, NVL(commission_pct,0)) IN
         (SELECT salary, NVL(commission_pct,0)
         FROM employees
         WHERE last_name = 'Kochhar')
AND last_name != 'Kochhar'
;

4.
SELECT last_name, job_id, salary
FROM employees
WHERE salary > ALL (SELECT salary
     FROM  employees
     WHERE job_id = 'SA_MAN')
ORDER BY salary DESC
;

5.
SELECT employee_id, last_name, department_id
FROM    employees
WHERE department_id IN ( SELECT department_id
    FROM departments
    WHERE location_id IN  ( SELECT  location_id
               FROM locations
               WHERE city LIKE  'T%'))
;

6.
SELECT e.last_name ename, e.salary salary,
e.department_id deptno, AVG(a.salary) dept_avg
FROM    employees e, employees a
WHERE   e.department_id = a.department_id
AND     e.salary > (SELECT AVG(salary)
                   FROM   employees
             WHERE  department_id = e.department_id)
GROUP BY e.last_name, e.salary, e.department_id
ORDER BY AVG(a.salary);

7.
SELECT outer.last_name
FROM    employees outer
WHERE  NOT EXISTS (SELECT 'X'
                   FROM employees inner
                   WHERE inner.manager_id = outer.employee_id);

SELECT outer.last_name
  FROM   employees outer
  WHERE  outer.employee_id NOT IN (SELECT inner.manager_id
         FROM   employees inner);

8.
SELECT last_name
FROM    employees outer
WHERE outer.salary < (SELECT AVG(inner.salary)
                                 FROM employees inner
                                WHERE inner.department_id = outer.department_id)
;

9.
SELECT  last_name
FROM    employees outer
WHERE EXISTS
      (SELECT 'X'
       FROM employees inner
       WHERE inner.department_id = outer.department_id
       AND inner.hire_date > outer.hire_date
       AND inner.salary > outer.salary);

10.
SELECT employee_id, last_name,
 (SELECT department_name
  FROM departments d
  WHERE   e.department_id =
d.department_id ) department
FROM employees e
ORDER BY department;

11.
WITH
summary AS (
  SELECT d.department_name, SUM(e.salary) AS dept_total
  FROM employees e, departments d
  WHERE e.department_id = d.department_id
  GROUP BY d.department_name)
SELECT department_name, dept_total
FROM summary
WHERE dept_total > (
                    SELECT SUM(dept_total) * 1/8
                    FROM summary  )
ORDER BY dept_total DESC;

Oracle Database 10g SQL Fundamentals II - Practice 5
5) Managing Data in Different Time Zones
1.
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

2.
SELECT TZ_OFFSET ('US/Pacific-New') from dual;
SELECT TZ_OFFSET ('Singapore') from dual;
SELECT TZ_OFFSET ('Egypt') from dual;

ALTER SESSION SET TIME_ZONE = '-7:00';

SELECT CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP
FROM DUAL;

ALTER SESSION SET TIME_ZONE = '+8:00';

SELECT CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP
FROM DUAL;

3.
SELECT DBTIMEZONE,SESSIONTIMEZONE
FROM DUAL;

4.
SELECT last_name, EXTRACT (YEAR FROM HIRE_DATE)
FROM employees
WHERE department_id = 80;

5.
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';

6.
SELECT * FROM sample_dates;

ALTER TABLE sample_dates MODIFY date_col TIMESTAMP;
SELECT * FROM sample_dates;

ALTER TABLE sample_dates MODIFY date_col
TIMESTAMP WITH TIME ZONE;

7.
SELECT e.last_name
   ,     (CASE extract(year from e.hire_date)
           WHEN 1998 THEN 'Needs Review'
           ELSE 'not this year!'
           END )           AS "Review "
FROM   employees e
ORDER BY e.hire_date;

8.
SELECT e.last_name, hire_date,sysdate
   ,     (CASE
      WHEN  (sysdate -TO_YMINTERVAL('15-0'))>= hire_date
  THEN '15 years of service'
             WHEN (sysdate -TO_YMINTERVAL('10-0'))>= hire_date
  THEN '10 years of service'
             WHEN (sysdate - TO_YMINTERVAL('5-0'))>= hire_date
  THEN '5 years of service'
      ELSE 'maybe next year!'
          END) AS "Awards"
FROM   employees e;

Oracle Database 10g SQL Fundamentals II - Practice 4
4. Generating Reports by Grouping Related Data

1)
SELECT MANAGER_ID
, JOB_ID
, SUM(SALARY)
FROM EMPLOYEES
WHERE MANAGER_ID < 120
GROUP BY ROLLUP(MANAGER_ID, JOB_ID)
;

2)
SELECT MANAGER_ID
, JOB_ID
, SUM(SALARY)
, GROUPING(MANAGER_ID)
, GROUPING(JOB_ID)
FROM EMPLOYEES
WHERE MANAGER_ID < 120
GROUP BY ROLLUP(MANAGER_ID, JOB_ID)
;

3)
SELECT MANAGER_ID
, JOB_ID
, SUM(SALARY)
FROM EMPLOYEES
WHERE MANAGER_ID < 120
GROUP BY CUBE(MANAGER_ID, JOB_ID)
;

4)
SELECT MANAGER_ID AS MGR
, JOB_ID AS JOB
, SUM(SALARY)
, GROUPING(MANAGER_ID)
, GROUPING(JOB_ID)
FROM EMPLOYEES
WHERE MANAGER_ID < 120
GROUP BY CUBE(MANAGER_ID, JOB_ID)
;

5)
SELECT DEPARTMENT_ID
, MANAGER_ID
, JOB_ID
, SUM(SALARY)
FROM EMPLOYEES
GROUP BY
GROUPING SETS
((DEPARTMENT_ID, MANAGER_ID, JOB_ID)
, (DEPARTMENT_ID, JOB_ID)
, (MANAGER_ID, JOB_ID)
)
;

Oracle Database 10g SQL Fundamentals II - Practice 3
3) Manipulating Large Data Sets

7.
a)
INSERT ALL
WHEN SAL > 20000 THEN
INTO SPECIAL_SAL VALUES(EMPID, SAL)
ELSE
INTO SAL_HISTORY VALUES(EMPID, HIREDATE, SAL)
INTO MGR_HISTORY VALUES(EMPID, MGR, SAL)
SELECT EMPLOYEE_ID AS EMPID
, HIRE_DATE AS HIREDATE
, SALARY AS SAL
, MANAGER_ID AS MGR
FROM EMPLOYEES
WHERE EMPLOYEE_ID < 125
;

8.
g)
INSERT ALL
 INTO SALES_INFO VALUES(EMPLOYEE_ID, WEEK_ID, SALES_MON)
 INTO SALES_INFO VALUES(EMPLOYEE_ID, WEEK_ID, SALES_TUE)
 INTO SALES_INFO VALUES(EMPLOYEE_ID, WEEK_ID, SALES_WED)
 INTO SALES_INFO VALUES(EMPLOYEE_ID, WEEK_ID, SALES_THUR)
 INTO SALES_INFO VALUES(EMPLOYEE_ID, WEEK_ID, SALES_FRI)
 SELECT EMPLOYEE_ID, WEEK_ID, SALES_MON, SALES_TUE, SALES_WED, SALES_THUR, SALES_FRI
 FROM SALES_SOURCE_DATA
 ;

9. 아직 잘 모르겠음....
CREATE TABLE emp_data
  (first_name  VARCHAR2(20)
  ,last_name   VARCHAR2(20)
  , email     VARCHAR2(30)
  )
ORGANIZATION EXTERNAL
(
 TYPE oracle_loader
 DEFAULT DIRECTORY emp_dir
 ACCESS PARAMETERS
 (
  RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
  NOBADFILE
  NOLOGFILE
  FIELDS
  ( first_name POSITION ( 1:20) CHAR
  , last_name POSITION (22:41) CHAR
  ,  email   POSITION (43:72) CHAR )
 )
 LOCATION ('emp.dat') ) ;

10.
Alter table emp_hist modify email varchar(45);

MERGE INTO EMP_HIST f USING EMP_DATA h
 ON (f.first_name = h.first_name
 AND f.last_name = h.last_name)
WHEN MATCHED THEN
 UPDATE SET f.email = h.email
WHEN NOT MATCHED THEN
 INSERT (f.first_name
    , f.last_name
    , f.email)
 VALUES (h.first_name
    , h.last_name
    , h.email);

select * from EMP_HIST;

11.
a)
update emp3 set department_id =60 where last_name ='Kochhar';
commit;
update emp3 set department_id =50 where last_name ='Kochhar';
commit;

b)
SELECT VERSIONS_STARTTIME "START_TIME"
, VERSIONS_ENDTIME "END_TIME"
, DEPARTMENT_ID
FROM EMP3 VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
WHERE LAST_NAME ='Kochhar'
;

Oracle Database 10g SQL Fundamentals II - Practice 1
1) Controlling User Access

CREATE USER
GRANT
CREATE ROLE
ALTER USER
REVOEK

1. What privilege should a user be given to log on to the Oracle server?
Is this a system or an object privilege?
 ---> System privilege

2. What privilege should a user be given to create tables?
 ---> Resource

3. If you create a table, who can pass along privileges to other users on your table?
 sys, system, and you

4. You are the DBA. You are creating many users who require the same system privileges.
What should you use to make your job easier?
 ---> Role

5. What command do you use to change your password?

6.
 GRANT SELECT ON DEPARTMENTS TO OTHER

7.
SELECT *
FROM DEPARTMENTS
;

8.
INSERT INTO DEPARTMENTS(DEPARTMENT_ID, DEPARTMENT_NAME)
VALUES(500, 'Education')
;
COMMIT
;

INSERT INTO DEPARTMENTS(DEPARTMENT_ID, DEPARTMENT_NAME)
VALUES(510, 'Human Resources')
;
COMMIT
;

9.
CREATE SYNONYM AR09_DEPTS
FOR DEPARTMENTS
;

10.
SELECT *
FROM AR09_DEPTS
;

11.
SELECT TABLE_NAME
FROM USER_TABLES
WHERE TABLE_NAME IN ('JOB_HISTORY'
,'EMPLOYEES'
,'JOBS'
,'DEPARTMENTS'
,'LOCATIONS'
,'REGIONS'
,'COUNTRIES')

12.
SELECT TABLE_NAME, OWNER
FROM ALL_TABLES
;

13.
REVOKE SELECT ON DEPARTMENTS
FROM AR09
;

14.
DELETE FROM DEPARTMENTS
WHERE DEPARTMENT_ID IN (500, 510)
;

COMMIT
;

1. ORACLE GRANT CONNECT, RESOURCE

http://kohseok21.blog.me/150004459576

DB에 접속하려면 최소한 CREATE SESSION 권한이 있어야 함.

CONNECT : ALTER SESSION, CREATE SESSION, CREATE DATABASE LINK, CREATE SEQUENCE,
CREATE SESSION, CREATE SYNONUM, CREATE VIEW

- 11g에는 connect에 create view 권한은 없습니다

RESOURCE : CREATE CLUSTER/INDEXTYPE/OPERATOR/PROCEDURE/SEQUENCE/TABLE/TRIGGER/TYPE

GRANT ALL : DROP/CREATE/ALTER

2. FLASHBACK 사용법

SQL> DROP TABLE T1;
(만약, SQL> DROP TABLE T1 PURGE; 했다면 FLASHBACK을 사용해서 복구 불능)

SQL> SELECT * from tab;

BIN$.....

SQL> show recyclebin
SQL> select * from "BIN$각자이름이 상이";

SQL> flashback table t1 to before drop;

SQL> select * from tab;
SQL> show recyclebin

SQL> DESC USER_TABLES;
 Name                                                                                                              Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 TABLE_NAME                                                                                                        NOT NULL VARCHAR2(30)
 TABLESPACE_NAME                                                                                                            VARCHAR2(30)
 CLUSTER_NAME                                                                                                               VARCHAR2(30)
 IOT_NAME                                                                                                                   VARCHAR2(30)
 STATUS                                                                                                                     VARCHAR2(8)
 PCT_FREE                                                                                                                   NUMBER
 PCT_USED                                                                                                                   NUMBER
 INI_TRANS                                                                                                                  NUMBER
 MAX_TRANS                                                                                                                  NUMBER
 INITIAL_EXTENT                                                                                                             NUMBER
 NEXT_EXTENT                                                                                                                NUMBER
 MIN_EXTENTS                                                                                                                NUMBER
 MAX_EXTENTS                                                                                                                NUMBER
 PCT_INCREASE                                                                                                               NUMBER
 FREELISTS                                                                                                                  NUMBER
 FREELIST_GROUPS                                                                                                            NUMBER
 LOGGING                                                                                                                    VARCHAR2(3)
 BACKED_UP                                                                                                                  VARCHAR2(1)
 NUM_ROWS                                                                                                                   NUMBER
 BLOCKS                                                                                                                     NUMBER
 EMPTY_BLOCKS                                                                                                               NUMBER
 AVG_SPACE                                                                                                                  NUMBER
 CHAIN_CNT                                                                                                                  NUMBER
 AVG_ROW_LEN                                                                                                                NUMBER
 AVG_SPACE_FREELIST_BLOCKS                                                                                                  NUMBER
 NUM_FREELIST_BLOCKS                                                                                                        NUMBER
 DEGREE                                                                                                                     VARCHAR2(10)
 INSTANCES                                                                                                                  VARCHAR2(10)
 CACHE                                                                                                                      VARCHAR2(5)
 TABLE_LOCK                                                                                                                 VARCHAR2(8)
 SAMPLE_SIZE                                                                                                                NUMBER
 LAST_ANALYZED                                                                                                              DATE
 PARTITIONED                                                                                                                VARCHAR2(3)
 IOT_TYPE                                                                                                                   VARCHAR2(12)
 TEMPORARY                                                                                                                  VARCHAR2(1)
 SECONDARY                                                                                                                  VARCHAR2(1)
 NESTED                                                                                                                     VARCHAR2(3)
 BUFFER_POOL                                                                                                                VARCHAR2(7)
 ROW_MOVEMENT                                                                                                               VARCHAR2(8)
 GLOBAL_STATS                                                                                                               VARCHAR2(3)
 USER_STATS                                                                                                                 VARCHAR2(3)
 DURATION                                                                                                                   VARCHAR2(15)
 SKIP_CORRUPT                                                                                                               VARCHAR2(8)
 MONITORING                                                                                                                 VARCHAR2(3)
 CLUSTER_OWNER                                                                                                              VARCHAR2(30)
 DEPENDENCIES                                                                                                               VARCHAR2(8)
 COMPRESSION                                                                                                                VARCHAR2(8)
 DROPPED                                                                                                                    VARCHAR2(3)

SQL> SELECT TABLE_NAME FROM USER_TABLES;

TABLE_NAME                                                                                                                                                                                             
------------------------------                                                                                                                                                                         
T_MASTER                                                                                                                                                                                               
T_TX                                                                                                                                                                                                   
T2                                                                                                                                                                                                     
BONUS                                                                                                                                                                                                  
DEPARTMENTS                                                                                                                                                                                            
DEPT                                                                                                                                                                                                   
DUMMY                                                                                                                                                                                                  
EMPLOYEES                                                                                                                                                                                              
EMP                                                                                                                                                                                                    
JOBS                                                                                                                                                                                                   
JOB_GRADES                                                                                                                                                                                             
JOB_HISTORY                                                                                                                                                                                            
LOCATIONS                                                                                                                                                                                              
REGIONS                                                                                                                                                                                                
SALGRADE                                                                                                                                                                                               
EMP2                                                                                                                                                                                                   
T1                                                                                                                                                                                                     
T_SIMIN                                                                                                                                                                                                
T_BOOK                                                                                                                                                                                                 
T3                                                                                                                                                                                                     
T_NEWS                                                                                                                                                                                                 
T4                                                                                                                                                                                                     
T5                                                                                                                                                                                                     
DEPT_TEST                                                                                                                                                                                              
EMP_TEST                                                                                                                                                                                               
EMPLOYEES2                                                                                                                                                                                             
EMP_TEST2                                                                                                                                                                                              
MY_EMPLOYEE                                                                                                                                                                                            
COUNTRIES                                                                                                                                                                                              

29 rows selected.

SQL> SHOW RECYCLEBIN
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME                                                                                                                                 
---------------- ------------------------------ ------------ -------------------                                                                                                                       
DEPT_TEST        BIN$Wc9zbTsjSWm48vAWRFfw9g==$0 TABLE        2012-11-13:13:52:58                                                                                                                       
EMP_TEST2        BIN$H1PFfYGRR/OqW8DQyWoFIQ==$0 TABLE        2012-11-13:15:05:50                                                                                                                       
SQL> DROP TABLE T1;

Table dropped.

SQL> SHOW RECYCLEBIN
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME                                                                                                                                 
---------------- ------------------------------ ------------ -------------------                                                                                                                       
DEPT_TEST        BIN$Wc9zbTsjSWm48vAWRFfw9g==$0 TABLE        2012-11-13:13:52:58                                                                                                                       
EMP_TEST2        BIN$H1PFfYGRR/OqW8DQyWoFIQ==$0 TABLE        2012-11-13:15:05:50                                                                                                                       
T1               BIN$M+dwv6RYRrCyaU89JNpK4A==$0 TABLE        2012-11-13:19:01:06                                                                                                                       
SQL> FLASHBACK TABLE T1 TO BEFORE DROP;

Flashback complete.

SQL> SHOW RECYCLEBIN
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME                                                                                                                                 
---------------- ------------------------------ ------------ -------------------                                                                                                                       
DEPT_TEST        BIN$Wc9zbTsjSWm48vAWRFfw9g==$0 TABLE        2012-11-13:13:52:58                                                                                                                       
EMP_TEST2        BIN$H1PFfYGRR/OqW8DQyWoFIQ==$0 TABLE        2012-11-13:15:05:50                                                                                                                       
SQL> SELECT * FROM TAB;

TNAME                          TABTYPE  CLUSTERID                                                                                                                                                      
------------------------------ ------- ----------                                                                                                                                                      
BIN$H1PFfYGRR/OqW8DQyWoFIQ==$0 TABLE                                                                                                                                                                   
BIN$Wc9zbTsjSWm48vAWRFfw9g==$0 TABLE                                                                                                                                                                   
BONUS                          TABLE                                                                                                                                                                   
COUNTRIES                      TABLE                                                                                                                                                                   
DEPARTMENTS                    TABLE                                                                                                                                                                   
DEPT                           TABLE                                                                                                                                                                   
DEPT50                         VIEW                                                                                                                                                                    
DEPT_TEST                      TABLE                                                                                                                                                                   
DUMMY                          TABLE                                                                                                                                                                   
EMP                            TABLE                                                                                                                                                                   
EMP01                          SYNONYM                                                                                                                                                                 
EMP10                          SYNONYM                                                                                                                                                                 
EMP2                           TABLE                                                                                                                                                                   
EMPLOYEES                      TABLE                                                                                                                                                                   
EMPLOYEES2                     TABLE                                                                                                                                                                   
EMPLOYEES_VU                   VIEW                                                                                                                                                                    
EMP_DETAILS_VIEW               VIEW                                                                                                                                                                    
EMP_TEST                       TABLE                                                                                                                                                                   
EMP_TEST2                      TABLE                                                                                                                                                                   
JOBS                           TABLE                                                                                                                                                                   
JOB_GRADES                     TABLE                                                                                                                                                                   
JOB_HISTORY                    TABLE                                                                                                                                                                   
LOCATIONS                      TABLE                                                                                                                                                                   
MY_EMPLOYEE                    TABLE                                                                                                                                                                   
REGIONS                        TABLE                                                                                                                                                                   
SALGRADE                       TABLE                                                                                                                                                                   
SAWON                          SYNONYM                                                                                                                                                                 
SAWON_IR                       VIEW                                                                                                                                                                    
SAWON_R                        VIEW                                                                                                                                                                    
SA_R                           VIEW                                                                                                                                                                    
T1                             TABLE                                                                                                                                                                   
T2                             TABLE                                                                                                                                                                   
T3                             TABLE                                                                                                                                                                   
T4                             TABLE                                                                                                                                                                   
T5                             TABLE                                                                                                                                                                   
T_BOOK                         TABLE                                                                                                                                                                   
T_MASTER                       TABLE                                                                                                                                                                   

TNAME                          TABTYPE  CLUSTERID                                                                                                                                                      
------------------------------ ------- ----------                                                                                                                                                      
T_NEWS                         TABLE                                                                                                                                                                   
T_SIMIN                        TABLE                                                                                                                                                                   
T_TX                           TABLE                                                                                                                                                                   
V1                             VIEW                                                                                                                                                                    

41 rows selected.

SQL> SPOOL OFF

Oracle Database 10g SQL Fundamentals I - Practice 11
SUBJECT : 11. Managing Objects with Data Dictionary Views

DICTIONARY
USER_OBJECTS
USER_TABLES
USRE_TAB_COLUMNS
USER_CONSTRAINTS
USER_CONS_COLUMNS
USER_VIEWS
USER_SEQUENCES
USER_TAB_SYNONYMS

1.
SELECT COLUMN_NAME
, DATA_TYPE
, DATA_LENGTH
, DATA_PRECISION AS PRECISION
, DATA_SCALE SCALE
, NULLABLE
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = UPPER('&TAB_NAME')
;

2.
SELECT UCC.COLUMN_NAME
, UC.CONSTRAINT_NAME
, UC.CONSTRAINT_TYPE
, UC.SEARCH_CONDITION
, UC.STATUS
FROM USER_CONSTRAINTS UC
, USER_CONS_COLUMNS UCC
WHERE UC.TABLE_NAME = UCC.TABLE_NAME
AND UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
AND UC.TABLE_NAME = UPPER('&TAB_NAME')
;

3.
COMMENT ON TABLE DEPARTMENT IS
 'Company department information including name, code, and location'
;

SELECT COMMENTS
FROM USER_TAB_COMMENTS
WHERE TABLE_NAME  = 'DEPARTMENTS'
;

4.
SELECT *
FROM USER_SYNONYMS
;

5.
SET LONG 2000
SELECT VIEW_NAME
, TEXT
FROM USER_VIES
;

6.
SELECT SEQUENCE_NAME
, MAX_VALUE
, INCREMENT_BY
, LAST_NUMBER
FROM USER_SEQUENCES