Oracle Admin1 -4

alter table emp drop column deptno;

1) data dictionary 변경

2) data 삭제

단계로 진행.

 

 

 

1) data dictionary 변경

 alter table emp

 set unused column deptno;

↑unsued 여기까지 진행.

2) data 삭제

 alter table emp

 drop unused cloumn;

 

 

 

12-1.

 

index : quary 성능 향상 객체

 

create index emp_no_idx

on emp(empno)

 

select * from emp

where empno=104;

 

sort -> b-tree 구조 생성 / where data + rowid 입력

             (balanced)

 

 

 

 

12-13.

create index emp_id_idx

on employees (employee_id);

* tablespace 생략

 -> table 의 저장 tablespace 이용

 

 

 

12-25.

1) create table copy_emp

    as select * from employees;

 

2) create index copy_emp_no_idx

   on copy_emp (employee_id)

   tablespace indx;

 

3) delete from copy_emp

    where employee_id > 200;

    commit;

 

4) analyze index copy_emp_no_idx

    validate structure;

   

    select * from index_stats;

 

LF_ROWS -> leaf block 갯수

DEL_LF_ROWS -> 삭제된 갯수

 

5) select * from dba_extents

where segment_name = upper ('copy_emp_no_idx');

 

alter index copy_emp_no_idx rebuild;

                                        (hr.copy_emp_no_idx rebuild;)

 

select * from dba_extents

where segment_name = upper ('copy_emp_no_idx');

 

 

 

 

 

 

 

 

 

 

12-19.

 

1) alter index hr.copy_emp_no_idx

monitoring usage;

 

select * from copy_emp

where employee_id = 100;

           +--> copy_emp_no_idx

 

2) select * from v$object_usage;

 

 

13-6.

1) drop table hr.emp;

 

2) create table hr.emp

(

no number (10),

name varchar2 (20)

) tablespace users;

 

3) alter table hr.emp

add constraint emp_no_pk primary key (no);

 

create index hr.emp_no_idx

on hr.emp (no)

tablespace idx;

 

 

4) alter table hr.emp

add constraint emp_no_pk primary key (no)

deferrable initially immediate

using index (create index hr.emp_no_idx on hr.emp (no) tablespace indx)

enable validate

 

-지연성

-index 수동 정의

-제약조건 상태

 

 

/*

alter table hr.emp

disable constraint emp_no_pk primary key (no);

*/

 

select * from dba_constraints

where table_name = upper ('emp');

 

select * from dba_cons_columns

where table_name = upper ('emp');

 

 

 

 

 

*novalidate

 

-hr-

1) alter table emp

disable constraint emp_no_pk;

 

2) insert into emp

values (100, 'king');

 

insert into emp

values (100, 'able');

 

3) alter table emp

enable constraint emp_no_pk;

          - validate 생략(default)

 

enable novalidate

 

* deferrable

1) truncate table emp;

 

2) set constraint emp_no_pk deferred;

 

3) insert into emp

values (100, 'king');

 

insert into emp

values (101, 'able');

 

insert into emp

values(100, 'smith');

 

4) commit;

 

 

telnet-

hr/hr

 

>@?/rdbms/admin/utlexpt1.sql

-> ex

 

>desc exceptions;

 

 

 

 

 

 

 

 

1) drop table emp;

2) create table emp as select * from employees;

3) alter table emp

    add constraint emp_no_pk primary key (employee_id);

 

4) alter table emp

disable primary key;

 

5) insert into emp (employee_id, last_name, email, hire_date, job_id)

values (100, 'test', 'test@co', sysdate, 'SA_REP');

commit;

 

6) truncate table exceptions;

 

alter table emp

enable primarykey exceptions into exceptions; -> error 날 것임..

                                                                  (table 명)

 

 

7) select * from exception;

 

8) select rowid, e.*

from emp e

where rowid in (select row_id from exceptions);

 

9) update emp

set employee_id = 900

where rowid='AAbbxx.....'

commit;

 

10) alter table emp

table primary key;

 

 

 

 

 

 

 

 

14-4.

 

-sys-                                                                      -sys-  (cdm)

select * from dba_profiles;

 

 

                                                                  os] $ORACLE_HOME/rdmbs/admin/utlpwdmg.sql

 

                                                                  sqlplus '/as sysdba'

                                                                  >@?/rdbms/admin/utlpwdmg.sql

 

 

                                                                  alter profile default limit

                                                                  password_lock_time unlimited

                                                                  passowrd_verify_function null;

 

 

 

 

 

 

 

 

15-9.

1) drop user sh cascade;   -> 접속 되어 있으면 삭제 안됨. logout 후 정상 삭제.

 

2) create user sh

identified by sh

quota 10m on users

quota 10m on userdata

default tablespace users

temporary tablespace temp

//password expire -> 최초 로그인시 암호 변경.

account unlock

profile default;

 

// grant connect, resource to sh;

 

lock -> unlock

alter user sh

account unlock;

 

 

 

 

 

-sh-

create table test -> extent 할당.

(

no number(10)

);

 

select * from user_ts_quotas;

 

 

 

 

 

 

 

 

 

 

 

 

*privilege (권한)

 

select * from system_privilege_map;  

 

grant create session, create table, create view to sh;

 

 

 

* role 권한의 묶음

drop role clerk;

 

1) create role clerk;

2) grant create session, create table, create view to clerk;

3) grant clerk to sh;

 

select * from dba_sys_privis -> system 권한 부여 대상 정보

where grantee = upper ('clerk');

 

 

*grant connect, resource to sh;