ITeye博客

2011 年文章归档

共 26 篇文章

STARTUP PFILE=

1.vi initdbs.ora spfile="/u01/oracle/dbs/spfilelsh" lock_sga=false 2.sqlplus /nolog conn /as sysdba startup pfile=initdbs.ora note: the values of the lock_sga in the initdbs.ora overridde the values of the spfilelsh.ora

2011-12-31 14:11 · oracle

LEARN SHELL

1.ORACLE_SID=lsh 2.export ORACLE_SID 3.#!/bin/bash 4.Shell variables are un-typed and may contain integer or text,Number with a decimal point will be treated as text(3.14) 5.variable scope: local script or all session. when export variable, variable are used in subsequently. 6.$ORACLE_SID use varia…

2011-12-28 15:14 · shell

MANAGE TABLE

1.heap table IOT PARTITION TABLE HEAP TABLE CLUSTER 2.create table t( id number, varchar2(80), name char2(10) ) pctfree 20 pctused 40 storage( initial 180k next 180k pctincrease 10 miniextents 3 maxextents 5 ) tablespace users create global temporary table temp(id number,name char(10)); alter table…

2011-12-26 16:50 · oracle

MONITOR redo size

1.set autot on stat 2.unsing v$statname,v$mystat set echo off set verify off column value new_val V define S="&1" set autotrace off select a.name, b.value from v$statname a, v$mystat b where a.statistic# = b.statistic# and lower(a.name) like '%' || lower('&S')||'%' DML operations set echo off set v…

2011-12-21 17:48 · oracle

What do rollback and commit

When we COMMIT, all that is left to happen is the following: • An SCN is generated for our transaction. In case you are not familiar with it, the SCN is a simple timing mechanism Oracle uses to guarantee the ordering of transactions and to enable recovery from failure. It is also used to guarantee …

2011-12-21 11:21 · oracle

What is the schema ?

A schema is a collection of database objects owned by a specific database user,or schema objects. Schema has the same name as that user,every user has a single schema,so the two terms(Schema,User) are synonymous. Schema objects include the segments(tables,indexes..etc) as we can see in the tablespa…

2011-12-20 15:18 · oracle

MANAGE UNDOTABS

1.manual undo_management=manual transactions transactions_per_rollback_segment rollback_segments =('rbs1','rbs2') create rollback segment rbs1 tablespace undotbs1; privs seg create public rollback segment prbs1 tablespace undotbs1 public seg alter rollback segment rbs1 online; note: create rollback…

2011-12-19 17:15 · oracle

DBA SQL

1.select a.name,b.status from v$rollname a,v$rollstat b where a.name in (select segment_name from dba_segments where tablespace_name='UNDOTBS1')and a.usn=b.usn; 2. select s.username,t.xidusn,t.ubafil,t.ubablk,t.used_ublk from v$session s,v$transaction t where s.saddr=t.ses_addr; 3.select a.tablespa…

2011-12-19 15:21 · oracle

SEGMENT EXTENTS ORACLEBLOCK

1.SEGMENT: allocated for a specific data structure Oracle allocates space for segments in units of one extent. A segment and all its extents are stored in one tablespace segment can span datafiles table,index,temporary,undo 2.EXTENTS: An extent is a specific number of contiguous data blocks. Oracle…

2011-12-15 16:11 · oracle

MANAGE TABLESPACE AND DATAFILES

1. tablespace,segment,extent,block tablespace: database logical is divided into more tablesapces. segment: a special storage structure(table,index),span more datafiles extents:allocate unit block: use unit,write/read unit db_block_size, default block size. standand block size,system tablespace use.…

2011-12-13 15:28 · oracle

ORACLE NET

1.net_service_name: servive_name: The SERVICE_NAME is the global database name .=sid+domain net_service_name= (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=port) ) (CONNECT_DATA= (SERVICE_NAME=service_name) ) ) 2. locate port: oracle_home/install/portlist.int 3. jdbc oci driver: java appica…

2011-12-12 09:49 · oracle

SQLPLUS TIPS

1.SQLPLUS : a tool that execute sql ,sqlplus ,sqlplus command. 1.sqlplus command-line cmd-sqlplus /nolog 2.sqlplus GUI sqlplusw /nolog 3.sqlplus web http://127.0.0.1:5560/isqlplus/ http://ip:5560/isqlplus/dba isqlplusctl start how to start sqlplus dba login [oracle@Toshiba ~]$ JAVA_HOME=$ORACLE_HOM…

2011-12-09 17:51 · oracle

ORACLE ENVIRONMENT VARIABLES

ORACLE_HOME ORACLE_SID : oracle instance PATH: LD_LIBRARY_PATH:dynamic lib TNS_ADMIN : the location of tnsname.ora SQLPATH: the location of SQL scripts;

2011-12-09 17:15 · oracle

Exam Test1

1.utl_file_dir: indicate the directory which the pl/sql packages and procedure can access and use 2.V$SHARED_POOL_RESERVED :lists statistics that help you tune the reserved pool and space within the shared pool. SHARED_POOL_RESERVED_SIZE: specifies (in bytes) the shared pool space that is reserved …

2011-12-09 16:18 · oracle

MANAGE ARCHIVED REDOLOGS

1. objective: copy online redologs to offline desitination database recovery update standby database 2.change redolog mode(archive log mode) in oracle 10g startup mount alter database archivelog/noarchivelog in oracle 9i startup mount alter database archivelog/noarchivelog alter system archive log …

2011-12-08 09:31 · oracle

RESUMABLE SESSION

RESUMABLE SESSION Let you enable and disable resumable space allocation. This feature allows an operation to be suspended in the event of an out-of-space error condition and to resume automatically from the point of interruption when the error condition is fixed. Using RESUMABLE Session to Avoid Tr…

2011-12-07 16:56 · oracle

Managing the Redo Log

1.What Is the Redo Log? store all changes made to the database as they occur. Redo log files are filled with redo records. A redo record, also called a redo entry, is made up of a group of change vectors, describe changes to the data segment block for the table, the undo segment data block, and the…

2011-12-07 10:40 · oracle

Data Integirty

1.Constraints type: Not null ,PK,FK,Check,Unique SQL> create table mm( 2 id number constraint con_pk primary key, 3 name char(10) not null, 4 uniqueid number unique, 5 sal number check(sal>100)); alter table mm modify name not null; alter table mm add primary key(id); alter table mm add unique(name…

2011-12-01 17:47 · oracle

Temporary Tablespace and Temporary Table

Temporary Tablespaces: (sort segments and temp table data segments) 1.contains transient data that persists only for the duration of the session. 2.improve the concurrency of multiple sort operations, reduce their overhead, and avoid Oracle Database space management operations. 3.A temporary tables…

2011-11-30 14:40 · oracle

ORA-25153: Temporary Tablespace is Empty

Today, I learned Temporary Table, I encountered for ORA-25153: Temporary Tablespace is Empty Following Step: 1.create global temporary table tt(id number,name varchar2(10)); 2.insert into tt values(1,'lsh'); ORA-25153: Temporary Tablespace is Empty 3.I check the temp tablespace of the current user,…

2011-11-29 17:22 · oracle