D1印刷用.PDF

Size: px

Start display at page:

Download "D1印刷用.PDF"

さなえふじした
6 years ago
Views:

1 [ D-1 ] Windows

2 Oracle8i for Windows Oracle8i for Windows / / Visual Basic - Oracle8i SQL Oracle

3 Oracle8i for Windows Oracle8i Enterprise Edition Oracle8i Personal Edition Oracle8i Workgroup Server Oracle8i Lite Windows 95/98/NT/2000 Windows CE Palm Windows NT 4.0 Windows 2000 Windows 98 Windows NT4.0 Windows 2000 Windows NT 4.0 Windows 2000

4 Oracle EMP DEPT EMP) DEPT DEPTNO

5 SQL(Structured Query Language) Oracle SQL POINT SQL Oracle SQL PL/SQL

6 Net8 Net8 OS OS NT UNIX Oracle8 Oracle8 SJISEUC Net8 Net8 Net8 (SJIS)

7 Oracle8i --Oracle Enterprise Manager(OEM)-- Oracle GUI Oracle

8 Oracle8i --SQL*Plus(GUI) Oracle / as sysdba startup [DB_NAME][PFILE=myinit.ora] / as sysdba shutdown [normal/ immediate/ transactional/ abort] SYSDBA DB_NAME PFILE = myinit.ora

9 Oracle8i --SQL*Plus( ) sqlplus /nolog connect / as sysdba startup [DB_NAME][PFILE=myinit.ora] sqlplus /nolog connect / as sysdba shutdown [normal/ immediate/ transactional/abort] Windows NT

10 Oracle8i --shutdown normal ( ) transactional immediate abort

11 Oracle8i --Windows NT-- Windows NT / Oracle8i Oracle Windows NT Oracle Oracle / Oracle --> Oracle Oracle8 Oracle Oracle immediate

12 Oracle8i --Oracle Enterprise Manager -- OEM /Instance Manager

13 Oracle8i ( ) (PGA) Oracle (SGA) REDO

14 Oracle8i Oracle (SGA) /REDO / Oracle (SGA)

15 Oracle8i (SGA) REDO SQL REDO

16 Oracle8i SGA shared_pool_size db_block_size db_block_buffers REDO log_buffer db_block_size

17 Oracle8i ( ) (DBWR) (LGWR) SMON PMON RECO (CKPT) (ARCH) (SMON) (PMON) (RECO) DBWR (LCKn) SGA CKPT LGWR LCK0 ARCH REDO

18 Oracle8i VB -SGA -SQL - SGA DB DB - SQL Oracle (SQL PL/SQL )

19 Oracle8i (USER_DATA) (SYSTEM) (TEMPORARY_DATA) (ROLLBACK_DATA) USR1ORCL.ORA, SYS1ORCL.ORA, TMP1ORCL.ORA RBS1ORCL.ORA USR2ORCL.ORA SYS2ORCL.ORA ( )

20 Oracle8i etc. EX.)CREATE TABLESPACE TEST_DATA DATAFILE D: ORADATA TEST_DATA.ora' SIZE 200K AUTOEXTEND ON NEXT 100K MINIMUM EXTENT 10K DEFAULT STORAGE ( INITIAL 10K NEXT 10K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 ); EX.)ALTER TABLESPACE TEST_DATA ADD DATAFILE E: ORADATA TEST2_DATA.ORA' SIZE 100K AUTOEXTEND ON NEXT 50K;

21 Oracle8i sort_area_size (PGA) DISTINCT, UNION, MINUS, GROUP BY, ORDER BY INDEX HASH JOIN INDEX EX.)CREATE TABLESPACE temp_data DATAFILE E: ORADATA temp_data.ora' SIZE 20M TEMPORARY;

22 Oracle8i OEMOracle Storage Manager

23 Oracle8i ) init<sid>.ora ( ) (TEXT) POINT (db_block_buffers, shared_pool_size )

24 Oracle8i Oracle REDO REDO REDO!!

25 Oracle8i Oracle LGWR CKPT ARCH 1 2

26 Oracle8i REDO REDO A B POINT REDO

27 Oracle8i ARCH POINT ( )

28 Oracle8i Oracle / / ( ) EX. CREATE USER taro IDENTIFIED BY taro DEFAULT TABLESPACE USER1 TEMPORARY TABLESPACE TEMP2 QUOTA 100k ON USER1; POINT SYSTEM

29 Oracle8i DB SCOTTEMP SELECT TARO SP1 EX. GRANT CREATE SESSION,CREATE TABLE to TARO;( ) GRANT SELECT on SCOTT.EMP to TARO;( ) GRANT DBA to TARO;(DBA )

30 Oracle8i OEMOracle Security Manager

31 Oracle8i SQL/OEM CREATE TABLE / / / ex.)create TABLE dept (id name region_id NUMBER(7) CONSTRAINT dept_key PRIMARY KEY, VARCHAR2(25), NUMBER(7) CONSTRAINT dept_fk_reg_id REFERENCES region (id), CONSTRAINT dept_ung_region_id UNIQUE(name, region_id)); / 30 (_) ( )

32 Oracle8i SQL/OEM CREATE INDEX CREATE [UNIQUE] INDEX ON ( [, ] ); ex.)create INDEX I_emp_name ON emp(first_name,last_name); I_emp_name empno First_name Last_name grade salary mgr deptno

33 Oracle8i Oracle Enterprise Manager OEMOracle Schema Manager / /

34 Windows Visual Basic/ ASP / MTS Data Control ODBC Direct RDC ODC DAO RDO ADO Oracle Objects for OLE Jet RDO ODBC Driver Manager OLE DB Oracle ODBC Driver Oracle Provider OCI C Net8 Oracle8i

35 Oracle Objects for OLE (OO4O) :oo4o( ) Oracle Microsoft COM Visual BasicVisual C++ ExcelVBA IIS Active Server PagesVB Script JavaScript Microsoft COM Automation

36 oo4o OO4O Visual Basic Oracle Data Control Visual C++ Oracle Objects C++ VB,ASP,Excel COM/DCOM Oracle Object Server Net8 Oracle8

37 Oo4o Automation Objects Model OraClient OraSessions OraSession OraConnections OraConnection OraDatabase OraFields OraDynaset OraField OraParameters OraParamArray OraSqlStmt OraParameter OraMetaData OraAQ OraMDAttribute OraAQMsg

39 Dim objlion as Object Set objlion = CreateObject( LionClass ) objlion.color = White objlion.walk Set objlion = Nothing

40 OraSession Public OraSession as Object Set OraSession = _ CreateObject("OracleInProcServer.XOraSession ) OraDatabase ( ) Public OraDatabase as Object Set OraDatabase = _ OraSession.OpenDatabase("exampledb","scott/tiger", 0&)

41 Oracle VB Oracle OraSession.LastServerErr OraDatabase.LastServerErr ERR.Number=440 OIP-NNNN LastServerErrTextLastServerErrReset

42 On Error GoTo erroo4o erroo4o: If objorasess.lastservererr = 0 Then If objoradb.lastservererr = 0 Then 'VB If Err.Number = 440 Then 'OO4O Else End If strerr = Error(Err.Number) Else 'Oracle strerr = objoradb.lastservererrtext objoradb.lastservererrreset End If ' VB Else strerr = objorasess.lastservererrtext objorasess.lastservererrreset End If MsgBox strerr 'Oracle

43 VB SQL SQL

44 DB Dim OraDynaset as Object... Set OraDynaset = _ OraDatabase.CreateDynaset("select * from emp", 0&)

45 (1) OraDynaset OraDynaset.MoveFirst OraDynaset.MovePrevious OraDynaset.MoveNext OraDynaset.MoveLast

46 (2) OraDynaset OraDynaset.MovePrevious EOF: FALSE BOF: TRUE OraDynaset.MoveNext EOF: TRUE BOF: FALSE EOF: TRUE BOF: TRUE

47 (3) () Private Sub cmdnext_click() On Error Goto ErrHandler:... OraDynaset.MoveNext If OraDynaset.EOF = True Then MsgBox End If End Sub OraDynaset.MoveLast

48 Text1.Text = OraDynaset.Fields( ename ).value OraField OraFields OraField OraFields( Column ).Value OraFields(0Column-1).Value

49 OraDynaset.Addnew OraDynaset.Fields( empno ).value = 100 OraDynaset.Fields( ename ).value = OraDynaset.Update OraDynaset.Delete

50 OraDynaset.Edit OraDynaset.Fields( empno ).value = 100 OraDynaset.Fields( ename ).value = OraDynaset.Update

51 SQL ExecuteSQL rowcount = OraDatabase.ExecuteSQL _ ("Update EMP Set SAL = SAL * 1.5 Where JOB = SALESMAN ) MsgBox rowcount +

52 Oracle8i SGASQL SQL SQL SGA SGA

53 Oracle8i SGASQL SQL SQL SQL LRU V$LIBRARYCACHE V$SQLAREA

54 Oracle8i SGASQL SQL SQL SQL etc.

55 Oracle8i SQL SQL SQL select * from emp where empno = 7788; select * from emp where empno = 1244; Select * From emp Where empno = 7788; Select * From emp Where empno = 7788; 7788 select * from emp where empno = :empno; select * from emp where empno = :empno; 1244

56 Oracle8i SQL optimizer_mode = RULE optimizer_mode = CHOOSE/FIRST_ROWS/ALL_ROWS SQL POINT ANALYZE

58 INSERT UPDATE INSERT DELETE Oracle -----

59 (Commit) (Rollback) OraSession OraDatabase OraSession.BeginTrans OraSession.CommitTrans OraSession.Rollback

60 OraSession.BeginTrans OraDatabase.ExecuteSQL _ ("Update Set = Where = 1") OraDatabase.ExecuteSQL _ ("Update Set = Where = 2") OraSession.CommitTrans Exit Sub ErrHandler: OraSession.Rollback

61 Oracle -- OLTP

62 Oracle -- RDBMS Update EMP set. where age = 30; Update EMP set. where age > 29 and age < 40; Update EMP set. where age < 30;

63 Oracle -- Oracle Ver.6 OLTP

64 Oracle -- Update Commit or Rollback or Select

65 Oracle --

66 Oracle -- RDBMS Read Committed Dirty Read Update Update Commit or Rollback Select Rollback Select!!!!

67 Oracle -- Update Copy Update Copy Copy Update REDO

Oracleの領域管理～トラブル防止のテクニック～

Oracleの領域管理～トラブル防止のテクニック～ THE Database FOR Internet Computing Oracle ... 3... 4... 4... 5... 5... 7... 8... 9... 12... 13... 13... 16... 17... 18... 20... 22... 23... 23... 24... 25... 25... 26... 28... 31... 31... 31... 33...