David Nishimoto

The following concepts explain the relationship between SQL Area and Shared Pool. The data dictionary takes its stake from the shared pool first, then the library cache takes the remainder of the pool. The Library Cache is used to store SQL statements and PL/SQL blocks to be shared by users. The library Cache prevents statement […]

SQL 1: Library Cache Click here for code example 1. SQL 2: Data Cache Hit Ratio Click here for code example 2. SQL 3: Buffer Hit Ratio Click here for code example 3. SQL 4: Shared Pool Free Memory Click here for code example 4. Back to the LSS “How To” Series Main Page

Optimizer Steps: Each step of the execution plan returns a set of rows that are used either by the next step or in the last step. The optimizer calculates the cost based on the estimated computer resources, including but not limited to I/O, CPU time, and memory. The goal of the cost-based approach is the […]

Purpose: The following three steps provide a method to get information back on how the optimizer will evaluate your SQL. Note, the optimizer uses statistics to determine an execution path. Step 1–Create the PLAN_TABLE create table PLAN_TABLE ( statement_id char(30), timestamp date, remarks char(80), operation char(30), options char(30), object_node char(30), object_owner char(30), object_name char(30), object_instance […]

Creating the Backup Control File SQL: alter database backup controlfile {path&name} reuse Recover the Database using the Backup Control File Use the Backup Control File as the control file name in the initialization file. Start the database up in Mount Mode. SQL: startup pfile=”d:orantdatabaseinitsid.ora” mount SQL: recover until cancel using backup controlfile Recovery until a […]

A DBA needs to be able to identify the types of failures that can occur in an Oracle Database environment. Using log and trace filesk, a DBA can diagnose backup and recovery problems and effectively find a solution. Type of failures Statement failure (bad data type, insufficient space) action=>Increase quota limits ALTER USER USER_NAME DEFAULT […]

Types of Media Recovery Complete Media Recovery: All necessary redo or incremental backup ever generated for the particular incarnation of the database being recovered. Incomplete Media Recovery: a point-in-time recovery (PITR), produces a version of the database as it was at some time in the past. Time-based Recovery: Recovers data up to a specified point […]

Tablespace Byte Sizes select distinct a.tablespace_name, sum(a.bytes) as free_bytes, sum(b.BYTES) as Total_Bytes from dba_free_space a, dba_data_files b Where a.tablespace_name = b.tablespace_name group by a.tablespace_name Table Byte Sizes select segment_name,tablespace_name,bytes,blocks FROM DBA_SEGMENTS where segment_type=’TABLE’ AND TABLESPACE_NAME NOT IN(‘SYSTEM’) order by bytes desc

Adding a datafile Indented lines in the following example are intended to be continuations of the lines above them. The lines are wrapped here for page formatting purposes. -Ed. alter tablespace user_data add datafile ‘e:{sid}datausr2.dbf’ size 30M; Autoextend alter tablespace user_data autoextend off; Next and Maxsize are set to zero. alter tablespace user_data add datafile […]

PL/SQL Commands and Syntax–Contents Cursors Functions Procedures Packages Triggers Defining and Using Functions CREATE OR REPLACE FUNCTION {function name} ( {argument} in {datatype} ) return {datatype} IS v_variable {datatype}; BEGIN /* Insert PL/SQL code here */ return v_variable; END {function name}; Back to the Top Defining and Using Procedures See Also: Defining and Using Stored […]

Creating a Table CREATE TABLE {table_name} ( {field1} VARCHAR2(10), {field2} NUMBER(10) ) PCTFREE 30 PCTUSED 60 TABLESPACE (tablespace_name) STORAGE ( INITIAL integer NEXT integer ); Adding a Primary Constraint ALTER TABLE {A_TABLE} ADD ( CONSTRAINT {A_TABLE_PK} PRIMARY KEY ({A_TABLE_IDX}) USING INDEX TABLESPACE {MY_TABLESPACE} PCTFREE 10 STORAGE ( INITIAL 20K NEXT 40K ) ) Adding a […]

Oracle String Functions initcap(char)–Returns a char with the first letter of each word in uppercase instr(char1, char2,n,m)–n=position m=occurance returns the numeric position of char2 in char1 length(char)–Length of char lower(char)–Returns char with all lower case lpad(char1,n,char2)–Return a char with char1 left pad with char2 ltrim(char)–Remove all leading spaces rpad(char1,n,char2)–Returns char with char1 right pad with […]