Development vs. Production DBA
Group, If this was discussed before, I missed it. There is a discussion going on trying to define the duties of a development vs. production DBA and where in-depth DBA involvement should occur. Is there any papers that anyone can share w/me on this subject. IMHO a DBA should be involved early on in the project to translate the functional requirements into a physical model using the features of the target version. I also think that it should be the DBA's job to create the packages, procedures and triggers in the development and testing phases. To me,this would facilitate the transition from testing to production. Our development DBA's are involved in the production side so are aware of our standards. Comments, opinions please. TIA Al Rusnak DBA - WEB Team/CISIS, Computer Operations * 804-734-8371 * [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rusnak, George A. (SEC-Lee) CTR INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Development vs. Production DBA
Group, If this was discussed before, I missed it. There is a discussion going on trying to define the duties of a development vs. production DBA and where in-depth DBA involvement should occur. Is there any papers that anyone can share w/me on this subject. IMHO a DBA should be involved early on in the project to translate the functional requirements into a physical model using the features of the target version. I also think that it should be the DBA's job to create the packages, procedures and triggers in the development and testing phases. To me,this would facilitate the transition from testing to production. Our development DBA's are involved in the production side so are aware of our standards. Comments, opinions please. TIA Al Rusnak DBA - WEB Team/CISIS, Computer Operations * 804-734-8371 * [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rusnak, George A. (SEC-Lee) CTR INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Database components
Group, Can someone please give me the steps to produce a listing of EE database components installed, ie. partitioning option, data mining, etc. ??? I need to compare what is installed on two machines. Both are 9i, one is Solaris and the other is HP. THANKS, Al Rusnak DBA - WEB Team 804-734-8371 Never take life seriously. Nobody gets out alive anyway -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rusnak, George A. (SEC-Lee) CTR INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Update Teradata table from 9i
Group, I have a requirement to build an On Insert' trigger from my Solaris 9i database and update a table in our Teradata warehouse. Anyone have a laundry list of things I need to set up and watch out for?? Also, just as important, what should I ask of the Teradata folks?? IE. 1) Do I use JDBC drivers? 2) What do I have to do, what does the Teradata side have to do? 3) Can I set up a database link to Teradata table? 4) etc. THANKS for the Help Al Rusnak DBA - WEB Team 804-734-8371 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rusnak, George A. (SEC-Lee) CTR INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: OT ksh day of week yesterday
Barb,
Try this:
#!/bin/ksh
#
# set up arrys of day strings, offset by one from real day
#
set -A days Fri Sat Sun Mon Tues Wed Thurs
#
# determine today's day of week index
#
index=$(date '+%u')
#
# Use index to get previous days abbreviation
#
echo ${days[$index]}
#
grep STRING log.${days[$index]}
HTH
-Original Message-
Sent: Wednesday, November 13, 2002 2:53 PM
To: Multiple recipients of list ORACLE-L
My TZ is EST5EDT and I think the 5 implies GMT-05:00
What does the 26 in EST26EDT imply?
Just curious.
Ed
-Original Message-
Sent: Wednesday, November 13, 2002 2:24 PM
To: Multiple recipients of list ORACLE-L
yesterday=$(TZ=EST26EDT date +%a)
On Wed, 13 Nov 2002, Barbara Baker wrote:
Will someone take pity on this poor VMS'er lost in a unix world??
I'm trying to create a script (ksh) that reads a log file created
yesterday. The log files are created with `date +%a` appended to the
end of the log file name. Last night a log file was created called
arc_indexlog.Tue It's easy enough to get today
TDAY=`date +%a`
grep -i ora /orasrv/ops/maint/logs/arc_indexlog.$TDAY
grep -i ora /orasrv/ops/maint/logs/adv_indexlog.$TDAY
but how do I get yesterday in the same format? (i.e., Tue instead of Wed)
I man'd date, but it was no help.
Thanks for any help.
Barb
-
Do you Yahoo!?
U2 on LAUNCH - Exclusive medley videos from Greatest Hits CD
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Alex
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (or the name of
mailing list you want to be removed from). You may also send the HELP
command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Sherman, Edward
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rusnak, George A. (SEC-Lee)
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
ColdFusion and Oracle
Group, I have an Oracle database 8.1.7.2 on Sun Solaris 5.0. We have another server running ColdFusion. The ColdFusion log has the following representative message (the Unable to instantiate ... - is the same): Fri Jun 07 11:23:33 2002,Oracle Error Code = 0PUnable to instantiate environment for 'ORACLE80.'P PSQL = SELECT sos_name name, conus_flg conus, dibs_region region FROM scan_dodaac Where dodaac=_CF_:?_P Query Parameter Value(s) - PParameter #1 = HQCNEY PData Source = WEBREADPpThe error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (100:6) to (100:56)./p, , /opt/iplanet_web_server.4/docs/log_in/html/cr.cfm?dodaac=HQCNEYCID=2 This message is repeated many, many times. I have nothing in my Oracle logs to indicate any problem. We started an endless loop on the ColdFusion server to the database server that just selects data from a table and then sleeps for 5 seconds, we do not get the above error messages in the ColdFusion log. Checked MetaLink, etc. Oh, bye the way we use Oracle MTS. TIA Al Rusnak 804-734-8453 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rusnak, George A. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Cold NT backups
Group, We are doing a cold backup to a remote server. I am using OCOPY that fails intermittently with the following message: OCOPY - Insufficient disk space on target drive There is more than sufficient space on the drive to hold the file. I filed a TAR and was told that the error message is generic in nature and they could not tell me exactly what is failing. Can anyone PLEASE give me some suggestions. The file is always SYSTEM01.DBF. All the rest of the files copy across without a problem. TIA Al Rusnak 804-734-8453 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rusnak, George A. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
SQL using UTL_FILE
Group, I have a stored procedure that runs from schema A on machine X, joins a table from another schema on machine X and a table from machine Y via a dblink and writes a file via UTL_FILE on machine X that is used in SQL Loader to insert data into a table for research. Pulls about 90 meg. For the past several months the procedure had been running in about 20 mins., in the last 2 weeks the time has jumped to 7 hours. As far as I can determine there have been no changes (I know, yeah, right !!) . My hardware is a SUN Solaris 5.7. ANY (sane) suggestions ? TIA Al Rusnak 804-734-8453 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rusnak, George A. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
OT: Informix User Group
I have just been given responsibility for two Informix databases. Can anyone give me a link to an Informix user group? TIA Al Rusnak 804-734-8453 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rusnak, George A. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Length of LONG datatype - ANSWER !!!
DECLARE
my_long_var LONG;
len_long_var VARCHAR2(10);
CURSOR get_rec
IS
SELECT key_column,
long_column
FROM table_name
ORDER BY key_column;
BEGIN
FOR x_rec IN get_rec
LOOP
my_long_var := x_rec. long_column;
len_long_var := LENGTH(my_long_var);
dbms_output.put_line ('my_long_var = ' || LENGTH(my_long_var));
END LOOP;
END;
I added a VARCHAR2 column to my table and used the key_column to update the
VARCHAR2 column with the length of the long_column. Don't ask why !!!
Al Rusnak
804-734-8453
[EMAIL PROTECTED]
-Original Message-
Sent: Tuesday, March 19, 2002 5:43 PM
To: Multiple recipients of list ORACLE-L
Subject:Re: Length of LONG datatype - ANSWER
I'll address your second question, because I don't know, offhand, the
answer to the first!
IMO, there's almost no advantage to using LONG. There are many
restrictions on its use (e.g., only one LONG in a table), you can't
apply a function to a LONG column, you can't use it in a set operation,
etc. In addition, Oracle says that the LONG datatype will soon be
phased out.
If you can fit your data into 4000 or fewer characters, use VARCHAR2.
If not, go for CLOB/BLOB, which require a little more overhead, but are
much more useable than LONG.
Paul Baumgartel
Adept Computer Associates, Inc.
[EMAIL PROTECTED]
--- Rusnak, George A. [EMAIL PROTECTED] wrote:
Group,
Please share with me a way to determine the actual length of data in
a LONG
data type. As a follow on, what are the pros/cons of using LONG vs.
CLOB or
VARCHAR2 ??
TIA
Al Rusnak
804-734-8453
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rusnak, George A.
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing
Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
__
Do You Yahoo!?
Yahoo! Sports - live college hoops coverage
http://sports.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Paul Baumgartel
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rusnak, George A.
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Length of LONG datatype
Group, Please share with me a way to determine the actual length of data in a LONG data type. As a follow on, what are the pros/cons of using LONG vs. CLOB or VARCHAR2 ?? TIA Al Rusnak 804-734-8453 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rusnak, George A. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
- OFF TOPIC - Corporate Data Model
Group, I realize that this is not a direct Oracle question, but we are moving (inching) toward all Oracle databases and there are a lot of smart people who will have some very good ideas. The organization I work for has several stovepipe applications. All are on a database of some flavor. In converting over to a database system some applications merely converted COBOL flat files over to an Oracle table and modified their COBOL programs to manipulate the data. There has been two edicts given: 1) The stovepipe applications will go through a redesign and use at a minimum Oracle 8i as their database. 2) The organization will move toward an Organization Data Store (ODS). The first initiative has not gotten under way but I have been asked to come up with data standards(?) for the ODS. One thought that occurs to me is that we need a standard way to model each local data store, ie. Erwin, Designer 2000. Once each local data store has started to capture their design, a group at the ODS level can start an analysis to start building an ODS logical data model and also act as an honest broker/devil's advocate to resolve differences between the local data stores. Comments, articles, PLEASE. Thanks Al Rusnak 804-734-8453 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rusnak, George A. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Connection problem
Group, I have a development and a production system. Both are Sun/Solaris, Oracle 8.1.7.2. I have an identical db link to a remote server on both of my servers. I can connect to remote host using the user/password that is in the db link. On my development server: webdev: select count(*) from [EMAIL PROTECTED]; COUNT(*) -- 199494 On my production server: webprod: select count(*) from [EMAIL PROTECTED]; select count(*) from [EMAIL PROTECTED] * ERROR at line 1: ORA-02068: following severe error from IBS_PROD ORA-03114: not connected to ORACLE We ran a trace from our end and saw where we reached the remote site from our production server and then received a disconnect from the remote host. We have no privs on the remote host. I have passed this info on to my POC at the remote host. Can anyone either explain this or suggest places to investigate to resolve this issue. TIA Al Rusnak DeCA: 804-734-8453 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rusnak, George A. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Connection problem
Yes they are the same. The remote site are going to try and flush(?) their routers and they I will try again. Al Rusnak DeCA: 804-734-8453 [EMAIL PROTECTED] -Original Message- Sent: Friday, January 11, 2002 2:27 PM To: Multiple recipients of list ORACLE-L Subject:RE: Connection problem Is tnsnames.ora pointing to the same place on both servers? -Original Message- Sent: Friday, January 11, 2002 1:15 PM To: Multiple recipients of list ORACLE-L Rusnak, George A. wrote: Group, I have a development and a production system. Both are Sun/Solaris, Oracle 8.1.7.2. I have an identical db link to a remote server on both of my servers. I can connect to remote host using the user/password that is in the db link. On my development server: webdev: select count(*) from [EMAIL PROTECTED]; COUNT(*) -- 199494 On my production server: webprod: select count(*) from [EMAIL PROTECTED]; select count(*) from [EMAIL PROTECTED] * ERROR at line 1: ORA-02068: following severe error from IBS_PROD ORA-03114: not connected to ORACLE We ran a trace from our end and saw where we reached the remote site from our production server and then received a disconnect from the remote host. We have no privs on the remote host. I have passed this info on to my POC at the remote host. Can anyone either explain this or suggest places to investigate to resolve this issue. TIA Al Rusnak DeCA: 804-734-8453 [EMAIL PROTECTED] Firewall ? -- Regards, Stephane Faroult Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rusnak, George A. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Convert ACESS to Oracle
Group, Can anyone please give me links, steps, document or step by step procedures to convert an Access database to an Oracle database? Also, please include any 'Gottcha's'. Thanks Al Rusnak DeCA: 804-734-8453 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rusnak, George A. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RECOMPILE INVALID OBJECTS
Group, Does anyone have some SQL handy that will re-compile INVALID objects for a given user, that they would be willing to share?? TIA Al Rusnak 804-734-8453 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rusnak, George A. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
system command from within stored procedure
Group, Is there anyway to execute a system level command from within a stored procedure and also get a return code back ie. A) did some prep on a partitioned table B) used utl_file to unload some new data from remote location C) NOW I want to run SQLLDR - direct path, NORECOVERY D) continue with my stored procedure TIA Al Rusnak 804-734-8453 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rusnak, George A. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
UNIX Shell to monitor redo log space
Before I start creating a 'NEW' script, does anyone have a shell script (and willing to share) that will monitor redo log space and when it fills to a certain level will issue the 'Alter System Archive Log To 'xxx' command in order not to have the instance become quiescent. TIA Al Rusnak 804-734-8453 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rusnak, George A. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Partition tables: Indexes
Group,
I have a partition that has 1 million records that is joined to 4 other
tables. In order to get an acceptable response time I added two additional
indexes. The response time is now acceptable but the load time is
unacceptable. I truncate the partition prior to the load. Is there any way
to drop/set unusable the local index on the partition being loaded, load the
data and then rebuild the local index?
If so PLEASE give me an example !! I am just about burned out on reading the
docs !!!
My table structure:
CREATE TABLE scan_contract
(CONTRACT_BEGIN_DATE DATE,
NSN VARCHAR2(13) ,
CONTRACT VARCHAR2(14) NOT NULL,
CONTRACT_END_DATEDATE,
FUTURE_EFF_DATE DATE,
FUTURE_SELL_PRICENUMBER(11,4),
SELL_PRICE NUMBER(8,2),
UPDATE_DATE DATE,
DODAAC VARCHAR2(6),
VENDOR_NUMBERVARCHAR2(4),
ITEM_UPC VARCHAR2(14),
REGION_FFS VARCHAR2(3)
)
partition by range (REGION_FFS)
SUBPARTITION BY HASH(DODAAC)
SUBPARTITIONS 10
(partition CONTRACT_1 Values less than ('RGC')
tablespace contract_ffs_1,
partition CONTRACT_2 Values less than ('RGD')
tablespace contract_ffs_2,
partition CONTRACT_3 Values less than ('RGE')
tablespace contract_ffs_3,
partition CONTRACT_4 Values less than ('RGF')
tablespace contract_ffs_4,
partition CONTRACT_5 Values less than ('RGG')
tablespace contract_ffs_5,
partition CONTRACT_6 Values less than ('RGH')
tablespace contract_ffs_6
)
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
TABLESPACE scan_down_ts
NOLOGGING
STORAGE(INITIAL 5M
NEXT 2M
MINEXTENTS 1
MAXEXTENTS 99
PCTINCREASE 0);
Create index scan_contract_idx1
ON scan_contract(REGION_FFS,
NSN,
dodaac) STORAGE (initial 10K)
LOCAL
(partition CONTRACT_1 TABLESPACE contract_ffs_1,
partition CONTRACT_2 TABLESPACE contract_ffs_2,
partition CONTRACT_3 TABLESPACE contract_ffs_3,
partition CONTRACT_4 TABLESPACE contract_ffs_4,
partition CONTRACT_5 TABLESPACE contract_ffs_5,
partition CONTRACT_6 TABLESPACE contract_ffs_6);
Create index scan_contract_item_upc_idx
ON scan_contract(ITEM_UPC) STORAGE (initial 25K)
LOCAL
(partition CONTRACT_1 TABLESPACE contract_ffs_1,
partition CONTRACT_2 TABLESPACE contract_ffs_2,
partition CONTRACT_3 TABLESPACE contract_ffs_3,
partition CONTRACT_4 TABLESPACE contract_ffs_4,
partition CONTRACT_5 TABLESPACE contract_ffs_5,
partition CONTRACT_6 TABLESPACE contract_ffs_6);
Create index scan_contract_contract_idx
ON scan_contract(CONTRACT) STORAGE (initial 25K)
LOCAL
(partition CONTRACT_1 TABLESPACE contract_ffs_1,
partition CONTRACT_2 TABLESPACE contract_ffs_2,
partition CONTRACT_3 TABLESPACE contract_ffs_3,
partition CONTRACT_4 TABLESPACE contract_ffs_4,
partition CONTRACT_5 TABLESPACE contract_ffs_5,
partition CONTRACT_6 TABLESPACE contract_ffs_6);
TIA
Al Rusnak
804-734-8453
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rusnak, George A.
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
DYNAMIC SQL
I CREATED PROCEDURE:
CREATE OR REPLACE PROCEDURE PopScanContract (
p_table IN VARCHAR2,
p_ffs IN VARCHAR2) IS
g_statement_txt VARCHAR2(500);
g_cursor_id_num PLS_INTEGER;
g_rows_inserted PLS_INTEGER := 0;
BEGIN
g_cursor_id_num := DBMS_SQL.OPEN_CURSOR;
g_statement_txt := 'INSERT INTO scan_contract ' ||
'SELECT CONTRACT_BEGIN_DATE, ' ||
'NSN, CONTRACT, CONTRACT_END_DATE, ' ||
'FUTURE_EFF_DATE, FUTURE_SELL_PRICE, ' ||
'SELL_PRICE, UPDATE_DATE, DODAAC, ' ||
p_ffs ||
' FROM ' || p_table ;
DBMS_SQL.PARSE(g_cursor_id_num,
g_statement_txt,
DBMS_SQL.NATIVE);
g_rows_inserted := DBMS_SQL.EXECUTE(g_cursor_id_num);
dbms_output.put_line ('ROWS INSERTED: ' || g_rows_inserted);
DBMS_SQL.CLOSE_CURSOR(g_cursor_id_num);
EXCEPTION
WHEN OTHERS THEN
IF DBMS_SQL.IS_OPEN(g_cursor_id_num) THEN
DBMS_SQL.CLOSE_CURSOR(g_cursor_id_num);
END IF;
RAISE;
END PopScanContract;
I EXECUTE AS:
exec PopScanContract('sm_contract_rge', 'RGE')
I GET:
PL/SQL procedure successfully completed.
YET:
The table 'scan_contract' still contains the same number of rows AFTER
the procedureexecutes As BEFORE the procedure executed.
ANY HELP WILL BE GREATLY APPRECIATED !!
TIA
Al Rusnak
804-734-8453
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rusnak, George A.
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
DYNAMIC SQL - Please mod request to trap error when insert fails
I CREATED PROCEDURE:
CREATE OR REPLACE PROCEDURE PopScanContract (
p_table IN VARCHAR2,
p_ffs IN VARCHAR2) IS
g_statement_txt VARCHAR2(500);
g_cursor_id_num PLS_INTEGER;
g_rows_inserted PLS_INTEGER := 0;
BEGIN
g_cursor_id_num := DBMS_SQL.OPEN_CURSOR;
g_statement_txt := 'INSERT INTO scan_contract ' ||
'SELECT CONTRACT_BEGIN_DATE, ' ||
'NSN, CONTRACT, CONTRACT_END_DATE, ' ||
'FUTURE_EFF_DATE, FUTURE_SELL_PRICE, ' ||
'SELL_PRICE, UPDATE_DATE, DODAAC, ' ||
p_ffs ||
' FROM ' || p_table ;
DBMS_SQL.PARSE(g_cursor_id_num,
g_statement_txt,
DBMS_SQL.NATIVE);
g_rows_inserted := DBMS_SQL.EXECUTE(g_cursor_id_num);
dbms_output.put_line ('ROWS INSERTED: ' || g_rows_inserted);
DBMS_SQL.CLOSE_CURSOR(g_cursor_id_num);
EXCEPTION
WHEN OTHERS THEN
IF DBMS_SQL.IS_OPEN(g_cursor_id_num) THEN
DBMS_SQL.CLOSE_CURSOR(g_cursor_id_num);
END IF;
RAISE;
END PopScanContract;
I EXECUTE AS:
exec PopScanContract('sm_contract_rge', 'RGE')
I GET:
PL/SQL procedure successfully completed.
YET:
The table 'scan_contract' still contains the same number of rows AFTER
the procedureexecutes As BEFORE the procedure executed.
ANY HELP WILL BE GREATLY APPRECIATED !!
TIA
Al Rusnak
804-734-8453
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rusnak, George A.
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
moving tables to a different tablespace
Hi Group, Please explain what I am doing wrong. 1) exp outln/outln@webprod mailto:outln/outln@webprod file=exp_file tables = 'OL$' 'OL$HINTS' 2) Dropped the tables: 'OL$' and 'OL$HINTS' on webprod 3) Created tablespace outln_ts on webprod 4) Altered user outln default tablespace outln_ts 5) imp outln/outln@webprod mailto:outln/outln@webprod file=exp_file tables = 'OL$' 'OL$HINTS' 6) SQL select table_name, tablespace_name from dba_tables where owner = 'OUTLN'; TABLE_NAME TABLESPACE_NAME -- -- OL$SYSTEM OL$HINTS SYSTEM XX OUTLN_TS WHY are the tables being re-imported back into SYSTEM tablespace Oracle 8.1.7 on Sun Solaris 5.7 TIA Al Rusnak 804-734-8453 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rusnak, George A. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Solaris 8 and 9i
List, Just wondering if anyone on the list has the combination of Solaris 8 and 9i loaded. Strength and weakness on both products or any comparison?? Currently running Solaris 5.7 and Oracle 8.1.7. My SA wants to go to Solaris 8 and I was thinking about going to 9i on our development box when we upgrade. TIA Al Rusnak -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rusnak, George A. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: OEM Data Gatherer question???
Andrea, Data Gatherer commands: vppcntl -stop -start -ping -status -refresh -stop: shutdown the DataGatherer -start: startup the DataGatherer -ping: determine if the Data Gatherer is running -status: determine if the Data Gatherer is running (same as -ping) -refresh: re-read the Data Cartridge Registry I believe that this may cause a performance hit if left running. Al Rusnak 804-734-8453 -Original Message- Sent: Tuesday, July 24, 2001 4:15 PM To: Multiple recipients of list ORACLE-L Subject:OEM Data Gatherer question??? Hi, I installed both OEM 204 Tuning Pack and Diagnostic Pack, TP runs very fine. But when I tried to connect to DP's Performance Mananger, I got message VTM 0005 and Data Gatherer is not running. What is this Data Gatherer? and how to run it? Thanks a lot! Andrea __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrea Oracle INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rusnak, George A. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
