edwards0307-CSDN博客

原创 from string get number data using pl/sql or sql

declare @aa varchar(80),----INFO列 @bb varchar(80) set @aa ='你好12按时地方'set @bb=''while PATINDEX('%[0-9]%',@aa)<>0 /*每次循环找出一个数字*/begin set @bb = @bb+substring(@aa,PATINDE...

2012-02-16 17:32:04 86

原创 SQL

1.select sal salary from emp; select sal as salary from emp; select sal as "salary" from emp; select comm "comm sal" from emp; column alias can not use where clause 2.DML select,ins...

2012-02-15 18:01:22 123

原创 modify ip

1.netconfig2./etc/sysconfig/network-scripts, find ifcfg-eth0,ifcfg-eth1.... modify IPADDR3./etc/rc.d/init.d/networkifconfig eth0 hw ether 192.168.2.252ifconfig eth0 192.168.2.252 netmask 255.255...

2012-02-10 17:45:10 135

原创 MULTI dbwr or io slaves

thanks dukope of itpub. Consider Multiple Database Writer (DBWR) Processes or I/O SlavesConfiguring multiple database writer processes, or using I/O slaves, is useful when the transaction rates...

2012-02-10 15:21:11 95

原创 FAQS

1.How can I get the largest amount of physical reads by any query? select disk_reads, sql_text from v$sqlarea where disk_reads >1000 order by disk_reads desc ; 2. V$FIXED_TABLEThis view ...

2012-02-09 15:59:06 122

原创 web

http://www.opensoftlab.com/index.php http://www.supportcenter.cn/viewthread.php?tid=55763 http://www.oracle.com/pls/db102/homepage?remark=tahiti http://www.dbapool.com/ oracle db...

2012-02-09 10:26:47 99

原创 web login

http://www.baidu.com/search/url_submit.html https://accounts.google.com/ServiceLogin?service=sitemaps&passive=1209600&continue=https://www.google.com/webmasters/tools/submit-url/?hl%3Dzh-C...

2012-02-09 10:07:50 199

原创 HOW TO STUDY ORACLE FROM Yong Huang

Assuming you want to study oracle database, specifically to prepare yourself as a DBA, not other products such as Apps or Developer Suite...] 1.0 Download from otn.oracle.com, or borrow CDs for, oracl...

2012-01-18 14:48:54 98

原创 RMAN

1.components of the rman rman server process channel rman repository recovery catalog database 2.when using rman connected to target database,default having two rman server pr...

2012-01-14 17:07:28 78

原创 INSTANCE and CRASH RECOVERY

1.type of checkpoint full checkpoint alter system checkpoint incrimental checkpoint(logfile switch) alter system switch logfile. partial checkpoint alter tablespace backup/online/o...

2012-01-12 10:12:49 110

原创 STARTUP PFILE=

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

2011-12-31 14:11:42 335

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 s...

2011-12-28 15:14:35 112

原创 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( init...

2011-12-26 16:50:13 150

原创 MONITOR redo size

1.set autot on stat 2.unsing v$statname,v$mystatset echo offset verify offcolumn value new_val Vdefine S="&1"set autotrace offselect a.name, b.value from v$statname a, v$mystat bwhere a.st...

2011-12-21 17:48:28 81

原创 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 isa simple timing mechanism Oracle uses to guarante...

2011-12-21 11:21:09 74

原创 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 ...

2011-12-20 15:18:24 230

原创 MANAGE UNDOTABS

1.manual undo_management=manual transactions transactions_per_rollback_segment rollback_segments =('rbs1','rbs2') create rollback segment rbs1 tablespace undotbs1; ...

2011-12-19 17:15:46 113

原创 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...

2011-12-19 15:21:16 91

原创 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 ...

2011-12-15 16:11:54 123

原创 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:allocat...

2011-12-13 15:28:03 99

原创 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...

2011-12-12 09:49:37 78

原创 SQLPLUS TIPS

1.SQLPLUS : a tool that execute sql ,sqlplus ,sqlplus command. 1.sqlplus command-line cmd-sqlplus /nolog 2.sqlplus GUI sqlplusw /nolog ...

2011-12-09 17:51:47 98

原创 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:19 100

原创 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 th...

2011-12-09 16:18:46 95

原创 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 st...

2011-12-08 09:31:30 71

原创 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 ...

2011-12-07 16:56:24 85

原创 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 ...

2011-12-07 10:40:00 100

原创 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 ch...

2011-12-01 17:47:31 90

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 concurr...

2011-11-30 14:40:49 72

原创 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.inser...

2011-11-29 17:22:21 70

原创 Temporary Segments

Temporary Segments: including Temporary Segments for Queries and Temporary Tables and Indexes Allocation of Temporary Segments for Queries(sort segments)One or more temporary tablespaces can...

2011-11-29 15:52:44 113

oracle loading and unloading

1.method of loading Conventional path: employ SQL inserts on our behalf to load data. Direct path: does not use SQL in this mode; it formats database blocks directly. The ...

2011-11-29 14:13:21 140

Dedicated Server and Shared Server

1.concepts about DS and SS DS:Oracle will create a new process for me when I log in. This is commonlyreferred to as the dedicated server configuration, since a server process will be dedicated ...

2011-11-17 18:00:59 119

listener errors

2011-11-17 11:32:55 219

manage controlfiles

a binary file 1.what's controlfiles Every Oracle Database has a control file, which is a small binary file that records the physical structure of the database.The database control file is a smal...

2011-11-15 14:53:24 114

user_tab_modifications

USER_TAB_MODIFICATIONS describes modiﬁcations to all tables owned by the current user that have been modiﬁed since the last time statistics were gathered on the tables 1.SQL> select * from user...

2011-11-15 10:40:46 90

killdbs