オレンジ練習1-データ処理

オレンジ実習

-----create, rename, drop-----
create table a ( b number, c char(10));
alter table a rename to tb_a;
select * from user_tables;
drop table tb_a;


------commit-------
create table tb_a ( b number, c char(10));
select * from user_tables where table_name = 'TB_A';
insert into tb_a (b, c) values (1, 'idb');
select * from tb_a;
commit;


------rollback-------
insert into tb_a (b, c) values (1, 'idb');
select * from tb_a;
commit;
add constraint pk_tb_a primary key (b);
delete from tb_a where b=1 and c ='idb';
select * from tb_a;
rollback;
select * from tb_a;


------delete by rowid(물리적 주소값)--------
select rowid, b, c from tb_a;
delete from tb_a where rowid = 'AAAFuEAAHAAAHJMAAB';
select rowid, b, c from tb_a;
commit;


------create, drop pk key--------
alter table tb_a
add constraint pk_tb_a primary key (b);
select * from user_constraints where table_name = 'TB_A';
alter table tb_a
drop constraint pk_tb_a;
select * from user_constraints where table_name = 'TB_A';


------insert, truncate data-------
select * from tb_a;
insert into tb_a (b, c) values (2, 'Ima');
insert into tb_a (b, c) values (3, 'kkk');
select * from tb_a;
commit;
truncate table TB_A; // auto commit, truncate은 복구 불가


------drop, flashback data-------
insert into tb_a (b, c) values (2, 'Ima');
insert into tb_a (b, c) values (3, 'kkk');
drop table tb_a;
select * from tb_a;
flashback table TB_A to before drop; // drop은 복구 가능
select * from tb_a;


----constraint 제약조건 등등----
select * from user_tables where table_name like 'EMP%';
DESC EMPLOYEES; -- F4
DESC EMP;
create table tb_c
(a number primary key, 
b varchar(20) default 'kkk', 
c date default sysdate,
d varchar2(30)
);
select sysdate from dual; -- dual == dummy table
select * from TB_C;
desc TB_C;
insert into TB_C(a, d) values(1, 'xxx');
insert into TB_C(a, d) values(1, 'yyy');
select * from TB_C;
select * from user_constraints where table_name = 'TB_C'; 
-- P == Primary Key
-- PK 속성 1. Not Null 2. Unique 3. Index
insert into TB_C(a, d) values(2, 'yyy');
select * from TB_C;
insert into TB_C(a, d) values(NULL, 'yyy');
select * from TB_C;
select 'Constraint_name  : ' || CONSTRAINT_NAME, CONSTRAINT_TYPE from user_constraints where table_name = 'TB_C'
union
select 'Index_name  :      ' || INDEX_NAME, INDEX_TYPE from user_indexes where table_name = 'TB_C';


------???------
alter table TB_C
add( e varchar(3) default 'zzz');
select * from TB_C;
alter table TB_C
add( f varchar(3) );
alter table TB_C
add( g varchar(3) not null); -- 바로 not null 불가능
alter table TB_C
add( g varchar(3) );
update TB_C
set g = 'kkk' ;
select * from TB_C;

注意事項

select * from user_tables where table_name = 'REGIONS';
-- 주의사항
-- 1. tablespace_name에 해당 테이블 있어야 함
-- 2. degree default 1이어야 하며, 임의로 변경하지 말 것
-- 3. logging default yes
-- 4. num_rows의 값은 데이터 건수와 근사값이어야 함
-- 5. last_analyzed의 날짜는 근사날짜여야 함


alter table TB_C
add( e varchar(3) default 'zzz');
-- 주의사항
-- default 데이터 col 추가했을때
-- 3억건의 데이터를 다루면 모두 default 값이 들어가야 해서 DB 다운
-- 살릴라면 DB 중지를 IMMEMIDATELY 말고 ABORT로?

DB業務に必要