[oracle] 잡다 쿼리

작성자:ninano 최초 작성일:2007년 10월 20일 마지막 수정일:

혼자보려고 쌓아놓은것

* 환경 확인 view
select * from v$datafile;     (dbf 파일위치, 크기 등..)
select * from v$logfile;       (redo log)
select * from v$controlfile;   (control file)

* user 생성
SQL> create user test
  2  identified by testpass
  3  default tablespace data01
  4  temporary tablespace temp
  5  quota unlimited on data01;

User created.

SQL> grant connect, resource to test;

Grant succeeded.

* 비밀번호 변경
SQL> alter user test identified by testpass2;

* tablespace 용량증가  (dba_tablespaces, dba_data_files 참고)
(방법1) 파일 추가
SQL> alter tablespace data01
  2  add datafile ‘/ora_edu2/user30/oradata/data02.bdf’ size 5M;

Tablespace altered.

(방법2) datafile size변경
SQL> alter database datafile ‘/ora_edu2/user30/oradata/data01.dbf’ resize 10M;

Database altered.

================
recover datafile ‘C:\ORACLE\ORADATA\ST00\EXAMPLE01.DBF’   (open때)
alter tablespace example online;

(datafile 위치 옮기기)
alter database datafile ‘/opt/oracle/oradata/FUND/example01.dbf’ offline;
alter database rename file ‘/opt/oracle/oradata/FUND/example01.dbf’ to ‘/home/orahome/oradata/FUND/example01.dbf’;
recover datafile ‘/home/orahome/oradata/FUND/example01.dbf’;  — (recover 하라면 한다.)
alter database datafile ‘/home/orahome/oradata/FUND/example01.dbf’ online;

alter database datafile ‘/opt/oracle/oradata/FUND/example02.dbf’ offline drop

alter database datafile ‘/home/orahome/oradata/FUND/example01.dbf’ resize 150M

(redo log 관련)
alter database add logfile member ‘/home/orahome/oradata/FUND/redo01.log’ to group 1;
alter database drop logfile member ‘/opt/oracle/oradata/FUND/redo02.log’;

alter system switch logfile;
alter system checkpoint;

recover database … 정리할것
쉽게 생각하면.. 백업된 파일을.. control file 및 archive file 등을 활용해서.. 최근 상태까지 돌려놓는것.

create user

create tablespace

redo log group, file 추가삭제

control file 확인, 추가, 삭제

partitioned table 생성방법

CREATE TABLE “SCOTT”.”TESTP” (“ID” NUMBER(6) NOT NULL, “DATA”
     VARCHAR2(100) NOT NULL,
     CONSTRAINT “PK_TESTPID” PRIMARY KEY(“ID”)
     USING INDEX  LOCAL )
     PARTITION BY RANGE (“ID”) (PARTITION “A_TS”
     VALUES LESS THAN  (10000)
     TABLESPACE “A” ,
     PARTITION “B_TS”
     VALUES LESS THAN  (20000)
     TABLESPACE “B” ,
     PARTITION “C_TS”
     VALUES LESS THAN  (30000)
     TABLESPACE “C” ,
     PARTITION “D_TS”
     VALUES LESS THAN  (MAXVALUE)
     TABLESPACE “D” )

====================

* open backup
– control file backup (trace 및 binary)
1) trace 파일로 만들기
SQL> alter database backup controlfile to trace;

Database altered.

2) pid 확인
SQL> show user
USER is “SYS”
SQL>
SQL> select s.username, p.spid
  2  from v$session s, v$process p
  3  where s.paddr=p.addr
  4  and s.username=’SYS’
  5  ;

USERNAME                       SPID
—————————— ————
SYS                            966748

SQL>

3) 파일 복사(pid로 되어있는것)
SQL> !
$ cd $ORACLE_HOME/admin/udump
$ cp user30_ora_966748.trc ../../backup/
$ exit

4) binary 백업
alter database backup controlfile to ‘/ora_edu2/user30/backup/control3.sql’

Loading