1 / 77

Oracle Database Programming

Oracle Database Programming. By Seonggyu Kim. Oracle 을 사용하기 위한 순서. Install 을 하기 위한 유저 등록 Oracle 을 Install 하기 위한 환경 설정 ORACLE Server & Client Install Server Manager 기동 ( svrmgrl ) Oracle Instance 기동 및 종료 (Startup and Shutdown) Tablespace 생성 및 변경 User 생성 및 변경

halden
Download Presentation

Oracle Database Programming

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. OracleDatabase Programming By Seonggyu Kim

  2. Oracle 을 사용하기 위한 순서 • Install 을 하기 위한 유저 등록 • Oracle 을 Install 하기 위한 환경 설정 • ORACLEServer & Client Install • Server Manager 기동 (svrmgrl) • Oracle Instance 기동 및 종료 (Startup and Shutdown) • Tablespace생성 및 변경 • User 생성 및 변경 • 오브젝트 생성 및 변경 (table, index, view, sequence, ...) • DML 명령문 실행 (select, insert, update, delete) • 관련 유틸리티 활용 (Server manager, SQL*Loader, Export/Import) • SQL*Net 기동 및 종료 및 SQL*Net 관련 파일 지정 (tnsnames.ora) • Client 에서 SQL*Net 지정 • Client Tool 에서 Server 접속 • 기본 Background Processes 이해 • Database Architecture 이해

  3. 설치 유형선택

  4. 언어 설정 및 진행화면

  5. 방화벽 액세스 허용

  6. SQL Developer • A SQL administration and development frontend with a graphical user interface Written in Java

  7. 데이터베이스 접속 보기 -> 접속

  8. SQL Execution

  9. 계정 생성 1 • DBA 계정으로 로그인 • SQL Developer – 기본 및 임시 데이터베이스 테이블스페이스 지정 SQL> create user myuser identified by myuserpw default tablespace USERS temporary tablespace TEMP; SQL> grant connect, resource to myuser; grant, connect, 을(를) 성공하였습니다. SQL> alter user myuser quota 50k on users; alter user myuser을(를) 성공했습니다.

  10. 계정생성 2 CREATE USER user IDENTIFIED [BY password | EXTERNALLY] [DEFAULT TABLESPACE tablespace] [TEMPORARY TABLESPACE tablespace] [QUOTA [integer [K | M] | UNLIMITED] ON tablespace] [PROFILE profile] SQL> create user user1 identified by upass default tablespace system   temporary tablespace temp   quota 1M on system; • SQL> DROP USER user_nameCASCADE;

  11. Privileges & Roles • A privilege is a right to execute an SQL statement or to access another user's object • system privileges and object privileges • A privileges can be assigned to a user or a role • A role allows one to localize the administration of objects GRANT RightsTO [user | public] WITH ADMIN OPTION; GRANT [object rights] (column1, column2, …) ON [object name] TO [user] WITH GRANT OPTION; REVOKE [Rights| ROLE] FROM [User| ROLE | PUBLIC] CASCADE CONSTRAINTS; CREATE ROLE Role_name [NOT IDENTIFIED | IDENTIFIED BY [Password| EXTERNALLY]];

  12. Tablespace CREATE TABLESPACE Name ALTER[DATAFILE datafile path]           [MINIMUM EXTENT clause]           [DEFAULT STORAGE storage]           [BLOCKSIZE integer [K]]           [LOGGING | NOLOGGING]           [ONLINE | OFFLINE]           [PERMANENT | TEMPORARY]           [AUTOEXTEND clause]           [extent_management_clause]           [segment_management_clause]; SQL> create tablespace firstbank   2 datafile 'c:\mydb\orcl\first01.dbf' size 10M; • SQL>SELECT table_name, tablespace_name FROM ALL_TABLES;

  13. 계정 공간 및 암호 관리 SQL> select * from dba_ts_quotas; SQL> alter user myuser password expire;

  14. Dictionary 조회 SQL> select resource_name, limit from dba_profiles  where profile='DEFAULT' and resource_type='PASSWORD';

  15. 예제 테이블 생성 Enrollment Student Class 데이터 타입 Number(4) : 숫자 네자리     Number(p,s) : p => 전체자리수, s => 소수점 자리 수     date : 날자 타입     varchar2(10) : 들어갈 수 있는 글자수 명시 SQL> select * from tab; => 등록되어 있는 테이블 확인

  16. 테이블 생성 및 삭제 SQL> create table emps (empno number(4),enamevarchar2(10)); SQL> drop table emps; SQL> renameemps to myemps; 테이블 생성은 SQL중 DDL(create)에 속하므로 auto commit이 실행

  17. 테이블 변경 • 기존 테이블에 컬럼 추가 SQL> alter table empsadd(addrs varchar2(20)); • 기존 테이블의 컬럼 내용 변경 - 주소의 길이를 20자리에서 30자로 늘이기SQL> alter table emps  modify (addrs varchar2(30)); • 테이블 컬럼 삭제SQL> alter table emps  drop column addrs; • 테이블 구조 보기 SQL> descemps; - 기존테이블에새로운컬럼이추가되면추가된컬럼은 Null 값으로채워진다.- 그러므로 Null 값을전부 Update 해주어야한다.- Modify명령으로이미입력된데이터가없다면, 데이터타입도변경할수있다.- 서비스운영중컬럼삭제가일어나면안된다.

  18. 데이터적재 load data infile * => data 가ctl file 끝에 있다는 의미ortst.dat 지정replace => 이구문이없으면 빈 테이블일 때만 load into table mydeptfields terminated by ',' optionally enclosed by '"' (deptno, dname, loc) begindata12, research, "saratoga" 10, "accounting", “cleveland” 11, "art", "salem" 13, "finance", "boston" mydept생성 후 메모장으로 tst.ctl로 저장 c:\oracle\bin>sqlldr scott/tiger@MyDBcontrol=tst.ctl log=tst.log bad=tst.bad

  19. 데이터 Import

  20. SQL 1 • SQL> set timing on • SQL> SELECT Major FROM STUDENT; • SQL> SELECT SSID, SName, Major, GradeLevel, Age FROM STUDENT WHERE Major = ‘Math’; • SQL> SELECT SSID,S Name, Major, GradeLevel, Age FROM STUDENT WHERE Major IN (‘Math’, ‘Accounting’); • SQL> SELECT SName, Major, Age FROM STUDENT WHEREGradeLevel IN (‘GR’, ‘SO’, ‘SN’) ORDER BY Name ASC, Age DESC; • SQL> SELECT COUNT(DISTINCT Major) FROM STUDENT;

  21. SQL 2 • SQL> SELECT Major, COUNT(*) FROM STUDENT GROUP BY Major HAVING COUNT(*) > 2; • SQL> SELECT Major, AVG(Age) FROM STUDENT WHEREGradeLevel = ‘SN’ GROUP BY Major HAVING COUNT(*) > 1; • SQL> SELECT DISTINCT ClassName FROM ENROLLMENT WHERE StudentNumber IN (SELECT SSID FROM STUDENT WHERE GradeLevel <> ‘GR’);

  22. SQL 3 • SQL> SELECT DISTINCT ENROLLMENT.ClassName FROM ENROLLMENT, STUDENT WHEREENROLLMENT.StudentNumber= STUDENT.SSID ANDSTUDENT.GradeLevel <> ‘GR’; • SQL> SELECT DISTINCT StudentNumber FROM ENROLLMENT A WHERE EXISTS (SELECT * FROM ENROLLMENT B WHEREA.StudentNumber = B.StudentNumber ANDA.ClassName NOT = B.ClassName); • 세 과목이상을 듣는 학생을 구하는 SQL 작성

  23. SQL4 • SQL> INSERT INTO ENROLLMENT VALUES (400, ‘BD445’, 44); • SQL> ROLLBACK; • SQL> COMMIT; • SQL> DELETE ENROLLMENT WHEREENROLLMENT.StudentNumberIN (SELECT STUDENT.SID FROM STUDENT WHERESTUDENT.Major = ‘Accounting’); • SQL> TRUNCATE TABLE ENROLLMENT; • SQL> UPDATE CLASS SETClassName = ‘BD564’ WHEREClassName = ‘BD445’;

  24. PlanTable create table PLAN_TABLE (statement_id varchar2(30), timestamp date, remarks varchar2(80), operation varchar2(30), options varchar2(255), object_node varchar2(128), object_owner varchar2(30), object_name varchar2(30), object_instance numeric, object_type varchar2(30), optimizer varchar2(255), search_columns number, id numeric, parent_id numeric, position numeric, cost numeric, cardinality numeric, bytes numeric, other_tag varchar2(255), partition_start varchar2(255), partition_stop varchar2(255), partition_id numeric, other long, distribution varchar2(30), cpu_cost numeric, io_cost numeric, temp_space numeric, access_predicates varchar2(4000), filter_predicates varchar2(4000)); EXPLAIN PLAN FOR SELECT * FROM STUDENT; SELECT LPAD(' ', 2*(Level-1))||OPERATION||' '||OPTIONS||' '|| DECODE (OBJECT_INSTANCE, NULL, OBJECT_NAME, TO_CHAR(OBJECT_INSTANCE)) ||'*'||OBJECT_NAME PLAN FROM PLAN_TABLE START WITH ID=0 CONNECT BY PRIOR ID = PARENT_ID ORDER BY ID;

  25. Oracle Architecture Component • Oracle Instance 확인: v$instanceSQL> select instance_name from v$instance; • datafile들의 경로 및 정보: v$datafileSQL> select name from v$datafile; • control file의 경로 및 정보: v$controlfile; SQL> select name from v$controlfile; • logfile의 경로 및 정보: v$logfileSQL> select member from v$logfile; • System Global Area 내용 조회SQL> select * from v$sga; • 현재 수행중인 background process 확인SQL> select paddr, name, description from v$bgprocess where paddr>'00'; • database log 모드 확인SQL> select log_mode from v$database;

  26. Oracle Database • Basic Database Administration • Roles of Database Administrator • Creating a Database • Starting Up and Shutting Down • Database Structures • Oracle Architecture • Data Access • Program Global Area • Background Processes • Dedicated Server / Multi Thread Server • Oracle Server Configuration • Managing Oracle Processes, Oline Redo Log, Control Files, Job Queues • Database Storage • Managing Tablespaces, Datafiles • Guidelines for Managing Schema Objects • Managing Tables, Views, Synonyms, Indexes • General Management of Schema Objects • Managing Rollback Segments, Users & Resources Tablespace DDL User Account DCL User Rights Administrator DDL Table Constraints DML Data Manipulation user

  27. Basic Database Administration • Roles of Database Administrator – sys, system • Install and upgrade of Oracle Server &Tools • SELECT * FROM product_component_version • Allocate system storage and plan future storage requirements for the database • Establish database structure(including Tablespace) • Createtable, view, index • Update database structure if necessary • Register users and manage security • Monitor database accesses • Tune database performance • Establsih backup and recovery • Manage archived data Hardware evaluation

  28. Creating a Database • Considerations Before Creating a Database • Calculate space requirement for tables and indexes • Establish archive redo log file and backup strategy • Select databasecharacter set • To change,modify sys.props$ table when no data in database • Creating a Database Example – NOARCHIVELOG CREATE DATABASE test LOGFILE GROUP 1 (‘test_log1a’, ‘test_log1b’) SIZE 500K, GROUP 2 (‘test_log2a’, ‘test_log2b’) SIZE 500K, DATAFILE ‘test_system’ SIZE 10M;SYSTEM Tablespace • Dropping a Database • Delete datafile, redo log file, parameter file and etc. • Refer V$DBFILE, V$LOGFILE for the file names to be deleted • Parameters • DB_NAME, DB_ DOMAIN, CONTROL_FILES, DB_BLOCK_SIZE, DB_BLOCK_BUFFERES, Process, Rollback_segments, DB_BLOCK_LRU_LATCHES, I/O separation Initial Tuning Guidelines

  29. Starting Up and Shutting Down $ORACLE_BASE/admin/SID/bdump/alert_SID.log Open All file opened as described by the control file for this instance Startup Up an Instance Mount Control File opened for this instance Nomount Instance Started Close the Instance Shutdown Shutdown normal Shutdown immediate Shutdown abort

  30. SERVERS USERS Oracle Architecture Background Processes instance System Global Area Data Files Redo Log Files Parameter Files Control Files characteristics

  31. SGA Shared Pool Database Buffer Cache SERVERS DBWR(Database Writer) USERS Data Files Data Access Dirty bit, LRU DB_BLOCK_SIZE DB_BLOCK_BUFFERS Shared SQL Area Parse Execute SQL Data Pro C

  32. Adjusting Parameters 1 cache miss ratio: Select sum(getmisses) / sum(gets) "Miss ratio“ From v$rowcache; The miss ratio should be less than 15%. If this is not the case, increase the SHARED_POOL_SIZE cache hit ratio for the library cache: Select sum(pinhits) / sum(pins) "Hit Ratio", sum(reloads) / sum(pins) "Reload percent"From v$librarycacheWhere namespace in ('SQL AREA', 'TABLE/PROCEDURE', 'BODY', 'TRIGGER'); The hit ratio should be at least 85% (i.e. 0.85). The reload percent should be very low, 2% (i.e. 0.02) or less. If this is not the case, increase the SHARED_POOL_SIZE. Teinit.ora parameter OPEN_CURSORS may also need to increased.

  33. Adjusting Parameters 2 To tune the value for LOG_BUFFER: Select Round(e.values/s.values,5) "Redo Log Ratio" From v$sysstat s, v$sysstat eWhere s.name = 'redo log space requests‘ and e.name = 'redo entries'; If the ratio is greater than 1:5000 (i.e. 0.0002 in the above), then increase the redo log buffer size until the space request ratio stops falling To reduce the number of times that a process wait for the log to be flushed: Select name, value from v$sysstatWhere name = 'redo log space requests'; The number of waits should always be zero. If not, increase the size of LOG_BUFFER, until the number returns to zero. Typically, there is no advantage in setting this beyond 1M. Select name, value from v$sysstatWhere name in ('redo log space requests', 'redo log space wait time');

  34. Adjusting Parameters 3 • select name, value From v$sysstatwhere name in ('db block gets', 'consistent gets', 'physical reads'); • If the cache-hit ratio goes below 90% then • For Oracle 9 onwards: increate the initialisation parameter DB_CACHE_SIZE • select 100*(1 - (v3.value / (v1.value + v2.value))) "Cache Hit Ratio [%]“ • from v$sysstatv1, v$sysstat v2, v$sysstat v3where v1.name = 'db block gets' and v2.name = 'consistent gets' and v3.name = 'physical reads‘ • If a large number of sorts require I/O to disk, increase the initialisation parameter SORT_AREA_SIZE • select name, value from v$sysstatwhere name in ('sorts (memory)', 'sorts (disk)');

  35. PGA PGA Oracle without the Multi-threaded Server Oracle with the Multi-threaded Server Stack Space Session Information Stack Space SGA SGA Session Information Shared SQL Areas Shared SQL Areas Program Global Area (PGA)

  36. SMON PMON RECO LCKn SGA Redo Log Buffer Database Buffer Cache Offline Storage Device Shared Servers DBWR LGWR ARCH Dnnn User Process CKPT Control Files Users Redo Log Files Data Files Background Processes System monitor Lock Recover Dedicated Server Process Archiver Dispatcher For Multi server

  37. SGA Request Queue Response Queue Shared Servers Dispatchers Users Listner Data Files Users Users Data Files Dedicated / Multi Threaded Server SGA Dedicated Servers

  38. Server Configuration Managing Control Files • Store the status of the physical structure of the database • Create / Drop CREATE CONTROLFILE SET DATABASE prod LOGFILE GROUP 1 (‘log1a’, ‘log1b’) SIZE 50K, GROUP 2 (‘log2a’, ‘log2b’) SIZE 50K NORESETLOGS DATAFILE ‘datafile1’ SIZE 3M, ‘datafile2’ SIZE 5M MAXLOGFILES 50 MAXLOGMEMBERS 3 MAXDATAFILES 200 MAXINSTANCES 6 ARCHIVELOG;

  39. Server Configuration Managing Job Queues • Schedule jobs to be run periodically SVRMGR> VARIABLE jobno number SVRMGR> begin 2 DBMS_JOB.SUBMIT(:jobno, 3 ‘dbms_ddl.analyze_object(‘‘TABLE’’, 4 ‘‘DQUON’’, ‘‘ACCOUNTS’’, 5 ‘‘ESTIMATE’’, NULL, 50);’ 6 SYSDATE, ‘SYSDATE + 1’); 7 end; 8/ Statement processed. SVRMGR> print jobno JOBNO -------------- 14144 SVRMGR> SELECT job, next_date, next_sec, failures, broken 2 FROM user_jobs; JOB NEXT_DATE NEXT_SEC FAILURES B -------- --------- --------- --------- -- 9125 01-NOV-09 00:00:00 4 N 14144 24-OCT-09 16:35:35 0 N 41762 01-JAN-10 00:00:00 16 Y

  40. Database Database Storage Tablespace • Block • - physical file block for allocating datafile • - smallest unit of data to be stored • - logical block, ORACLE block, page • Extent • - set of consecutive database blocks • Segment • - container for objects • - table, index, cluster segment • Tablespace • - a logical storage unit Tablespace Segment 112K Extent 84K Extent 28K Table T T 2k I I T I T I Database Blocks File Database Files Objects

  41. Structure Relationship Physical BLOCK Logical SYSTEM dba_tables RBS EXTENT TEMP • ALTER TABLE table_name DEALLOCATE UNUSED; USERS SEGMENT INSA01 emp TABLESPACE FILE DATABASE DISK

  42. Tablespaces • Specify only the database storage locations, not the logical database structure, or database schema CREATE TABLESPACE rb_segs DATAFILE ‘datafile_1’ SIZE 50M DEFAULT STORAGE ( INITIAL 50K NEXT 50K MINEXTENTS 2 MAXEXTENTS 50 PCTINCREASE 0) OFFLINE; • Temporary Tablespace for SORT

  43. Managing Tablespaces • Managing Tablespace Allocation • Altering Storage Settings for Tablespaces ALTER TABLESPACE rb_segs DEFAULT STORAGE ( INITIAL 70K NEXT 70K MINEXTENTS 2 MAXEXTENTS 50 PCTINCREASE 50); • Coalsecing Free Space ALTER TABLESPACE tablespace COALESCE; • Viewing Information about Tablespace • DBA_FREE_SPACE_COALESCED • Altering Tablespace Availablity ALTER TABLESPACE tablespace ONLINE; ALTER TABLESPACE tablespace OFFLINE NORMAL; • Dropping Tablespace DROP TABLESPACE tablespace INCLUDING CONTENTS;

  44. Managing Datafiles • Datafile • A tablespace in an Oracle database consists of one or more physical datafiles • Guideline for Managing Datafiles • Number of Datafiles • Set the Size of Datafiles • Store Datafiles Separately From Redo Log Files • Creating and Adding Datafiles to a Tablespace ALTER TABLESPACE rb_segs ADD DATAFILE ‘filename’ SIZE 10M • Changing a Datafile’s Size • Altering Datafile Availability • Bringing Datafiles Online in ARCHIVELOG Mode ALTER DATABASE DATAFILE ‘filename’ ONLINE; • Taking Datafiles Offline in NORACHIVELOG Mode ALTER DATABASE DATAFILE ‘filename’ OFFLINE DROP; • Renaming and Relocating Datafiles • Verifying Data Blocks in Datafiles

  45. Data Block Format Header Table Dictionary Row Dictionary Free Space BLOCK information (Block position, type of Segment) 85 ~ 100 bytes information about table in cluster Row information in Block (2 bytes for each row) For New Row Insertion or Update (affects pctfree, pctused) Table orIndex Data Row Data

  46. PCTFREE / PCTUSED Header Header Table Dictionary Table Dictionary Row Dictionary Row Dictionary PCTUSED PCTFREE 20% Free Space Row Data 61% Free Space Row Data PCTFREE specifies how much space should be left in a database block for future updates. PCTUSED specifies when Oracle should consider a database block to be empted enough to be added to the freelist. Oracle insert new rows in blocks enqueued on the freelist.

  47. Managing Schema Objects • Setting Storage Parameters CREATE TABLE test_table ( . . . . . . . . . . ) STORAGE (INITIAL 100K NEXT 50K MINEXTENTS 2 MAXEXTENTS 5 PCTINCREASE 50) • Deallocating Space ALTER TABLE table DEALLOCATE UNUSED • Understanding Space Use of Datatypes

  48. Managing Tables • Creating Tables CREATE TABLE emp ( empno NUMBER(5) PRIMARY KEY, empname VARCHAR2(15) NOT NULL, job VARCHAR2(10), mgr NUMBER(5), hiredate DATE DEFAULT(sysdate), sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(3) NOT NUL CONSTRAINT dept_pkey REFERENCE dept) PCTFREE 10 PCTUSED 40 TABLESPACE users STORAGE ( INITIAL 50K NEXT 50K MAXEXTENTS 10 PCTINCREATE 25); • Altering Tables ALTER TABLE emp PCTFREE 30 PCTUSED 60; • Dropping Tables DROP TABLE emp CASCADE CONSTRAINTS;

  49. Managing View • A logical set of tables • For security, simple usage, performance, temporary usage • Creating Views CREATE VIEW sales_staff as SELECT empno, ename, deptno FROM emp WHERE deptno = 10 WITH CHECK OPTION CONSTRAINT sales_staff_cnst; • Modifying a Join View • Exclude disntinct, union, count, group by, .., etc. • Replacing Views CREATE OR REPLACE VIEW sales_staff AS • Dropping Views DROP VIEW sales_staff;

  50. Managing Synonym • A synonym • is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects – alias • Can be generated as private orpublic use • Creating Synonyms • CREATE SYNONYM orCREATE ANY SYNONYM CREATE PUBLIC SYNONYM public_emp FOR jward.emp; • Dropping Synonyms DROP SYNONYM emp; DROP PUBLIC SYNONYM public_emp;

More Related