Bookmark and Share
Last Updates
Display: 1 - 10 of 11, Total Pages: 2
1 2 > >>
[Blog] what is ACID? [Original] 2008-9-1 12:36pm

Transactional systems often are described as being ACID compliant.

Read Full Article...

Section:Oracle DBA | Read:906 | Comment:0 | Rating:0%

[Blog] Delete Duplicate Rows From an Oracle Table [Original] 2008-8-21 12:55pm

It's easy to introduce duplicate rows of data into Oracle tables by running a data load twice without the primary key or unique indexes created or enabled.

Here's how you remove the duplicate rows before the primary key or unique indexes can be created:

Read Full Article...

Section:Oracle DBA | Read:1097 | Comment:0 | Rating:0%

[Blog] Implicit cursors & Explicit Cursors [Modified] 2008-8-5 2:00pm

For every SQL statement execution certain area in memory is allocated. PL/SQL allow you to name this area. This private SQL area is called context area or cursor. A cursor acts as a handle or pointer into the context area. A PL/SQL program controls the context area using the cursor. Cursor represents a structure in memory and is different from cursor variable.

Implicit cursors

For SQL queries returning single row PL/SQL declares implicit cursors. Implicit cursors are simple SELECT statements and are written in the BEGIN block (executable section) of the PL/SQL. Implicit cursors are easy to code, and they retrieve exactly one row. PL/SQL implicitly declares cursors for all DML statements. The most commonly raised exceptions here are NO_DATA_FOUND or TOO_MANY_ROWS.

Syntax:

SELECT column_name INTO :block.field_name FROM table_name;

Explicit Cursors

Explicit cursors are used in queries that return multiple rows. The set of rows fetched by a query is called active set. The size of the active set meets the search criteria in the select statement. Explicit cursor is declared in the DECLARE section of PL/SQL program.

Syntax:

CURSOR <cursor-name> IS <select statement>

Sample Code:

DECLARE
CURSOR emp_cur IS SELECT ename FROM EMP;
BEGIN
----
---

END;

Notes: The syntax for implicit cursors is easier to use yet often, less efficient. An explicit cursor is DECLARE'd OPEN'ed FETCH'ed and CLOSE'ed. Note that the user has to explicitly define each step.

Read Full Article...

Section:Oracle DBA | Read:3198 | Comment:0 | Rating:0%

[Blog] 怎么从Oracle数据库中获取table DDL(表定义语句) ? [Copied] 2007-3-26 5:49pm

How do I find the create table statement DDL in Oracle?

There are two easy ways to do this. You can run a query in SQLPLUS or you can export the table.

1. To run the query from SQLPLUS do the following.

SQL> set long 4000;
SQL> select dbms_metadata.get_ddl('TABLE', 'TEST', 'CHERRY') from dual;

Where: TEST is the name of the table.
CHERRY is the name of the schema.
All parameters must be in uppercase.

An example of the type of output that you can expect is shown below. This is the statement that should be copied and sent to GoldenGate.

CREATE TABLE "CHERRY"."TEST"
( "CUST_CODE" VARCHAR2(4),
"NAME" VARCHAR2(30),
"CITY" VARCHAR2(20),
"STATE" CHAR(2),
PRIMARY KEY ("CUST_CODE")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 214748364
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 214748364
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"

2. Another option is to use the export feature in Oracle.

This would be done from the command line.

$> exp cherry/cherry rows=n tables=cherry.test file=test_ddl.sql

Read Full Article...

Section:Oracle DBA | Read:13911 | Comment:5 | Rating:0%

[Blog] Oracle Time Zone [Modified] 2007-1-9 11:47am

--Valid Time Zones--

SQL> select tzname,tzabbrev 
       from V$TIMEZONE_NAMES 
      where tzabbrev = 'MST'


TZNAME                    TZABBREV
------------------------- ----------
America/Denver            MST
America/Edmonton          MST
America/Ensenada          MST
America/Mazatlan          MST
America/Mexico_City       MST
America/Phoenix           MST
America/Regina            MST
America/Shiprock          MST
America/Tijuana           MST
Canada/East-Saskatchewan  MST
Canada/Mountain           MST
Canada/Saskatchewan       MST
Europe/Moscow             MST
Mexico/BajaNorte          MST
Mexico/BajaSur            MST
Mexico/General            MST
MST                       MST
MST7MDT                   MST
Navajo                    MST
US/Arizona                MST
US/Mountain               MST
W-SU                      MST
-- DBTIMEZONE --
SQL> select DBTIMEZONE from dual;
DBTIME
------
-07:00
SQL> ALTER database SET TIME_ZONE = 'America/Denver';
Database altered.
SQL> select DBTIMEZONE from dual;
DBTIMEZONE
---------------
America/Denver
SQL> ALTER database SET TIME_ZONE = '-07:00';
SQL> alter session set TIME_ZONE='-03:00';
Session altered.
(Set time zone at the session level)
SQL> select SESSIONTIMEZONE from dual;
SESSIONTIMEZONE
-------------------------------------------------
-03:00
--SYSTIMESTAMP--
SQL> select SYSTIMESTAMP from dual;
SYSTIMESTAMP
-----------------------------------------------

01-SEP-03 10.53.13.574000 AM -07:00
--CURRENT_TIMESTAMP--
SQL> select CURRENT_TIMESTAMP from dual;
CURRENT_TIMESTAMP
-------------------------------------------------

01-SEP-03 02.53.33.753000 PM –03:00
-- TIMESTAMP datatype with TIME ZONE Information --
create table date_table (
time_stamp_tz TIMESTAMP WITH TIME ZONE,
time_stamp_ltz TIMESTAMP WITH LOCAL TIME ZONE);
SQL> select dbtimezone,sessiontimezone from dual;
DBTIMEZONE SESSIONTIMEZONE
---------- ----------------
-07:00     -07:00

SQL> insert into date_table values (SYSTIMESTAMP,SYSTIMESTAMP);
1 row created.

SQL> alter session set time_zone='-03:00';
Session altered. 

SQL> select dbtimezone,sessiontimezone from dual;
DBTIMEZONE SESSIONTIMEZONE
---------- ----------------
-07:00     -03:00

SQL> insert into date_table values (LOCALTIMESTAMP,LOCALTIMESTAMP );
1 row created.

SQL> select time_stamp_tz, time_stamp_ltz from date_table;
TIME_STAMP_TZ                         TIME_STAMP_LTZ
------------------------------------- ------------------------------
01-SEP-03 10.57.36.642000 AM -07:00   01-SEP-03 02.57.36.642000 PM
01-SEP-03 02.57.48.549000 PM -03:00   01-SEP-03 02.57.48.549000 PM

SQL> alter session set time_zone='-07:00';
Session altered.


SQL> select time_stamp_tz, time_stamp_ltz from date_table;
TIME_STAMP_TZ                         TIME_STAMP_LTZ
------------------------------------- ------------------------------
01-SEP-03 10.57.36.642000 AM -07:00   01-SEP-03
10.57.36.642000 AM
01-SEP-03 02.57.48.549000 PM -03:00   01-SEP-03
10.57.48.549000 AM
-- insert ASCII time zone --
SQL> insert into date_table values
2 (timestamp '2003-02-17 09:00:00 America/Sao_Paulo',
	timestamp '2004-08-08 09:00:00.123456789');
1 row created.

Read Full Article...

Section:Oracle DBA | Read:13836 | Comment:5 | Rating:0%

[Blog] How can I find unused columns in Oracle? [Original] 2006-11-13 11:28am

1. To find the unused columns

SQL> create table t2 (a number, b number);
Table created.

SQL> alter table t2 set unused column b;
Table altered.

SQL> select * from DBA_UNUSED_COL_TABS where TABLE_NAME='T2';
OWNER TABLE_NAME COUNT
---------- -------------------- ------------------------------ ----------
CHERRY T2 1
SQL> select u.name owner, o.name table_name, c.name column_name,
2 decode(c.type#, 1, decode(c.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'),
3 2, decode(c.scale, null,
4 decode(c.precision#, null, 'NUMBER', 'FLOAT'),
5 'NUMBER'),
6 8, 'LONG',
7 9, decode(c.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'),
8 12, 'DATE', 23, 'RAW', 24, 'LONG RAW',
9 69, 'ROWID',
10 96, decode(c.charsetform, 2, 'NCHAR', 'CHAR'),
11 105, 'MLSLABEL',
12 106, 'MLSLABEL',
13 111, 'unknown',
14 112, decode(c.charsetform, 2, 'NCLOB', 'CLOB'),
15 113, 'BLOB', 114, 'BFILE', 115, 'CFILE',
16 121, 'unknown',
17 122, 'unknown',
18 123, 'unknown',
19 178, 'TIME(' ||c.scale|| ')',
20 179, 'TIME(' ||c.scale|| ')' || ' WITH TIME ZONE',
21 180, 'TIMESTAMP(' ||c.scale|| ')',
22 181, 'TIMESTAMP(' ||c.scale|| ')' || ' WITH TIME ZONE',
23 231, 'TIMESTAMP(' ||c.scale|| ')' || ' WITH LOCAL TIME ZONE',
24 182, 'INTERVAL YEAR('||c.precision#||') TO MONTH',
25 183, 'INTERVAL DAY('||c.precision#||') TO SECOND(' ||
26 c.scale || ')',
27 208, 'UROWID',
28 'UNDEFINED') column_type,
29 c.length, c.precision#, c.scale
30 from sys.user$ u, sys.obj$ o, sys.col$ c where c.obj# = o.obj#
31 and bitand(c.property,32768) = 32768 --is unused column
32 and bitand(c.property, 1) != 1 --not ADT attribute col
33 and bitand(c.property, 1024) != 1024 --not NTAB's setid col
34 and u.user# = o.owner#
35 /

OWNER TABLE_NAME COLUMN_NAME COLUMN_TYPE LENGTH PRECISION# SCALE
---------- ---------- ------------------------------ --------------- ---------- ---------- ----------
CHERRY T2 SYS_C00002_06111309:34:38$ NUMBER 22

2. To drop unused columns

SQL> alter table t2 drop unused columns;

Read Full Article...

Section:Oracle DBA | Read:4086 | Comment:0 | Rating:0%

[Blog] Oracle's V$ Views (archive log 部分) [Original] 2005-11-7 8:39am

The following views are part of the data dictionary.

See also Oracle's x$ tables

v$archive_dest

Shows all archived redo log destinations. Use this view to find out to which place archived redo logs are copied: select dest_id,destination from v$archive_dest

These values correspond to the init parameter log_archive_dest_n.

v$archive_dest_status

This view allows to find status and errors for each of the defined

v$archived_log

Displays successfully archived redo logs.

shows received logs on a primary standby database.

v$archive_gap

Lists sequence numbers of the archived los that are known to be missing for each thread on a (physical?) standby database (highest gap only).

v$archive_processes

This view provides information on the archive processes. It can be used to find out if an ARCH process is active or not.

v$controlfile

Displays the location and status of each controlfile in the database.

v$controlfile_record_section

See sections in a controlfile.

v$buffer_pool

See buffer pools.

This view's column id can be joined with x$kcbwds.indx

See also x$kcbwbpd

v$buffer_pool_statistics

v$database

This view lets you access database information. For example, you can check (using log_mode) whether or not the database is in archivelog mode:

ADPDB>select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG

v$logfile

This view can be queried to find the filenames, group numbers and states of redo log files. For example, to find all files of group 2, use select member from v$logfile where group# = 2

v$logmnr_contents

See dbms_logmnr.

v$log_history

This view contains an entry for each Log Switch that occured. The column first_time indicates the time of the first entry???

On physical standby databases, this view shows applied logs.

v$logstdby

Can be used to verify that archived redo logs are being applied to standby databases.

http://www.adp-gmbh.ch/ora/misc/dynamic_performance_views.html

Read Full Article...

Section:Oracle DBA | Read:11963 | Comment:39 | Rating:0%

[Blog] 向clob、long中插入字符的pl/sql [Original] 2005-11-1 3:21pm

insert_clob01.sql

Declare
v_long1 long;
v_long2 long;
v_long3 long;
v_lob4 clob;
Begin
v_long1 := v_long1 || RPAD('TEST1', 1000,'DOG');
v_long2 := v_long2 || RPAD('TEST2', 40,'CAT');
v_long3 := v_long3 || RPAD('TEST3', 3000,'FISH');
v_lob4 := v_lob4 || RPAD('TEST4', 3964,'DUCK');
insert into t166_t(c1,c2,c3,c4,c5,c6,c7,c8,
C230000010,C240000007,C240000008,
C240000012,C309,c310,c311,c312,c318,C321,C600000704)
values ('g', v_long2, 1, v_long2,v_long2,1,1,'a',
v_lob4,v_lob4,v_lob4,v_lob4,
v_lob4,v_lob4,v_long2,v_long2,v_long2,v_long2,v_lob4);
commit;
End;
/

Read Full Article...

Section:Oracle DBA | Read:2443 | Comment:28 | Rating:0%

[Blog] 怎样计算oracle表中一个row的长度,如何知道CLOB的长度 [Original] 2005-11-1 3:15pm

很简单啦。

SQLPLUS> analyze table XXX compute statistics;

SQLPLUS>select AVG_ROW_LEN from user_tables where table_name='XXX';

如果是要计算CLOB长度:

SQLPLUS>select length(C230000010) from t166;

或者用dbms_lob.getlength(LOB type);

如果要计算VARCHAR的长度用length(trim(Column name)) 。

Read Full Article...

Section:Oracle DBA | Read:9442 | Comment:29 | Rating:0%

[Blog] Oracle CLOB datatype [Original] 2005-11-1 3:14pm

在定义CLOB时有两种方式,一种是in-row, 另一种是out-row。如果是out-row,那只有CLOB locator存放在row中,一般使用这种方式来定义CLOB。例子:

CREATE TABLE T166

(

...

C230000010 CLOB NULL,
...

)

LOB (C230000010) STORE AS
( TABLESPACE USERS
DISABLE STORAGE IN ROW
CHUNK 8192
PCTVERSION 10
NOCACHE
NOLOGGING)

如果想定义为in-row,那就把disable改为enable就可以啦。

Read Full Article...

Section:Oracle DBA | Read:5516 | Comment:0 | Rating:0%

1 2 > >>
Latest Comments
eozzrixrlo

on line..
- By admin, 2008-4-24 2:34am
rvaqlckrdu

free nud..
- By admin, 2008-4-24 2:33am
xndfjmwcpj

- By admin, 2008-4-5 1:02pm
vsjblsgatm

somali p..
- By admin, 2008-4-5 1:01pm
jknsdsmvyi

- By admin, 2008-3-12 1:48am
fkgyuchizk

tramadol..
- By admin, 2008-3-12 1:48am
wnniqpohsg

- By admin, 2008-4-24 2:32am
unxnmahoya

in phent..
- By admin, 2008-4-24 2:29am
bkgulrjbtt

- By admin, 2008-3-12 1:47am