Bookmark and Share
Oracle DBA
Display: 11 - 20 of 26, Total Pages: 3
<< < 1 2 3 > >>
[Blog] How to install Statspack? [Modified] 2008-8-4 11:49am
1. connect as SYS and run the installation scripts from the $ORACLE/HOME/rdbms/admin directory.

2. To install Statspack, you need to run three scripts: spcuser.sql, spctab.sql, and spcpkg.sql. The three scripts perform the following tasks:

Spcuser.sql creates the perfstat user.

Spctab.sql creates the necessary tables and other objects to store the statistics.

Spcpkg.sql creates the Statspack package.

3. You need to run the three scripts under different schemas. You must run the first script, spcusr.sql, under the SYS schema, and you must run the next two as the Statspack user perfstat.

Read Full Article...

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

[Blog] Oracle DBA常用脚本(3) -- Generate a script to create database users [Copied] 2007-4-3 1:17pm

set pagesize 0
set escape on
spool create_user_ddl.sql
select 'create user ' || U.username || ' identified ' ||
DECODE(password,
NULL, 'EXTERNALLY', ' by values ' ||
'''' || password || '''') || chr(10) ||
'default tablespace ' || default_tablespace || chr(10) ||
'temporary tablespace ' || temporary_Tablespace || chr(10) ||
' profile ' || profile || chr(10) ||'quota ' ||
decode ( Q.max_bytes, -1, 'UNLIMITED', NULL, 'UNLIMITED', Q.max_bytes) ||
' on ' || default_tablespace ||
decode (account_status,'LOCKED', ' account lock',
'EXPIRED', ' password expire',
'EXPIRED & LOCKED', ' account lock password expire', null)
||';' from dba_users U, dba_ts_quotas Q

-- Comment this clause out to include system and default users

where U.username not in ('SYS','SYSTEM',
'SCOTT','DBSNMP','OUTLN','WKPROXY','WMSYS','ORDSYS','ORDPLUGINS','MDSYS',
'CTXSYS','XDB','ANONYMOUS','OWNER','WKSYS','ODM_MTR','ODM','OLAPSYS',
'HR','OE','PM','SH','QS_ADM','QS','QS_WS','QS_ES','QS_OS','QS_CBADM',
'QS_CB','QS_CS','PERFSTAT')
and U.username=Q.username(+) and U.default_tablespace=Q.tablespace_name(+);
set pagesize 100
set escape off

Read Full Article...

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

[Blog] Oracle DBA常用脚本(2) -- dump table DDL from the database [Original] 2007-3-29 4:09pm

###a script to dump a table DDL creation script for a schema provided as the parameter###
下面这个script用来产生一个能生成某个schema的所有objects DDL的script.

$> more extract_objects.sql
-- create_ddl.sql
-- Use this to generate the DDL to create objects
set arraysize 1
set echo off
set heading off
set feedback off
set verify off
set pagesize 0
set linesize 200
set long 2000
set longchunksize 200
set heading off
spool object_ddl.sql

select 'select dbms_metadata.get_ddl(' || '''' ||
OBJECT_TYPE || '''' || ',' || '''' || OBJECT_NAME ||
'''' || ',' || '''' || OWNER || '''' || ') from dual;'
FROM dba_objects
Where Owner = '&schema' and OBJECT_TYPE != 'TABLE PARTITION';

spool off

在sqlplus中运行:

SQL> @extract_objects.sql;
Enter value for schema: CHERRY
select dbms_metadata.get_ddl('TABLE','CITY','CHERRY') from dual;
select dbms_metadata.get_ddl('INDEX','SYS_C0025132','CHERRY') from dual;
select dbms_metadata.get_ddl('TABLE','PHONE','CHERRY') from dual;
select dbms_metadata.get_ddl('INDEX','SYS_C0025133','CHERRY') from dual;

结果会输出到文件object_ddl.sql,编辑object_ddl.sql,加入以下行:

set arraysize 1
set echo off
set heading off
set feedback off
set verify off
set pagesize 0
set linesize 200
set long 2000
set longchunksize 200
set heading off
spool table_ddl.sql

在sqlplus中运行编辑后的object_ddl.sql,结果输出到table_ddl.sql,table_ddl.sql会有所有这个schema的table DDL和index DDL。

Read Full Article...

Section:Oracle DBA | Read:15327 | Comment:6 | Rating:0%

[Blog] Oracle DBA 常用脚本(1) : start and shutdown Oracle database [Original] 2007-3-27 6:31pm

start, shutdown and abort the Oracle database

##########start the database: dbstart.sh##########
#!/bin/ksh
sqlplus /nolog << oxff
connect cherry/cherry as sysdba
startup pfile=/rdbms/oracle/ora1022i/64/dbs/initOra1022a_cherry.ora
exit
0xff

##########shutdown the database: dbstop.sh##########
#!/bin/ksh
sqlplus /nolog << oxff
connect cherry/cherry as sysdba
shutdown immediate
exit
0xff

##########abort the database: dbabort.sh##########
#!/bin/ksh
sqlplus /nolog << oxff
connect cherry/cherry as sysdba
shutdown abort
exit
0xff

Running a sql script/truncate table

##########running sql scripts: dbinsert.sh##########
#!/bin/ksh
sqlplus /nolog << oxff
connect cherry/cherry
@insert_table.sql
exit
0xff

##########truncate table: dbtruncate.sh##########
#!/bin/ksh
sqlplus /nolog << oxff
connect cherry/cherry
truncate table test;
exit
0xff

Read Full Article...

Section:Oracle DBA | Read:14698 | Comment:4 | 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] 过年 [Original] 2007-2-20 1:20am

我父亲喜欢下围棋,耳濡目染,我也喜欢围棋,也会下点,每次假期回家,父亲为了陪我,偶尔也跟我下几盘,父亲和我下,要让我九子,即使这样,我还经常输。

我奶奶也喜欢下围棋,年轻时,跟我爷爷学的,其他娱乐活动不会。我跟我奶奶下围棋,我要让我奶奶九子,多数情况下都是我赢。

每次父亲回老家探亲或者是我奶奶来我家小住,父亲都要陪我奶奶下围棋。当然也是让九子,多数都是我奶奶赢,奶奶输呢,也经常只是输几个子。

年少的我有时想不通,怎么会这样,以父亲的水平,即使让奶奶九子,也能赢奶奶一大片的。

而今的我,在闯荡了几乎大半个世界之后,过年时分,隔着越洋电话,听着我母亲的唠叨声,想像着以前家里的那盘棋,恍然明白了父亲的苦心。

七年没有回家了,也没有归期,即使归去,物是人非,父亲那朗朗的笑声和奶奶那慈祥的面容皆成隔世,也许只有家乡天边的那片云还没有变。

Read Full Article...

Section:Oracle DBA | Read:13592 | Comment:2 | 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:4071 | 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:11962 | 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:2442 | Comment:28 | Rating:0%

<< < 1 2 3 > >>
Tags
No Records
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