Oracle migration
You may want to review metalink doc id 188061.1 entitled Upgrading Directly to an 8.1.7 Patch Set. We followed this for an 8.1.6.0 upgrade to 8.1.7.4. on Solaris. However, this does require setting up a new oracle_home. A summary of the steps are: 1) Install the base 8.1.7.0.0 directory tree in a new oracle home. Do NOT use the installer program to upgrade/migrate your existing database, and do NOT create a new 8.1.7.0 database. 2) Download the patch, unzip it, untar it. Use the installer to apply the patch to the new oracle_home. 3) Upgrade the old database to the new database; basically, you copy the old init.ora to the new oracle_home, startup restrict, run the appropriate upgrade script. 4) Clean-up: move all datafiles, rbs, redo, control, dump dirs, from old oracle_home to new oracle_home. This wasn't simple. We practiced this procedure on two test instances before we upgraded our production instance, but it worked OK. We did run into two problems after upgrading. ORA-00164: autonomous transaction disallowed within distributed transaction. Search metalink for ORA-000164 if you'es using pl/sql code with autonomous/distibuted transactions. The other problem happened because we changed character sets to WE8ISO8859P1, and did not have NLS_LANG defined in our export scripts. This resulted in EXP-00041, but was easy to fix by defining NLS_LANG. Hope this helps. Ken: My Two Cents'... It seems to me that you're gonna have to install the 8.1.7.0.0 code in order to apply the 8.1.7.2 patch. So where's the problem? You install 8.1.7, upgrade the DB, apply the .2 patch, and it's Miller Time. Cheers, Mike -Original Message- Sent: Thursday, July 18, 2002 10:43 AM To: Multiple recipients of list ORACLE-L List, One of the DBA's here needs to migrate a fairly sizeable database from 8.1.6.0 to 8.1.7.2 on Solaris. The doc's state that it is advisable to migrate to 8.1.7.0 and then go to 8.1.7.2 but we don't have 8.1.7.0 installed and don't want to install it unless we really have to. Anyone know if it is possible to migrate directly to 8.1.7.2 (docs don't explicitly say it can't be done but don't seem to suggest it either). Ken -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Becker 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).
Cognos
Huh? We use cognos transformer and enterprise server on Solaris, because that's also where our Oracle db resides. Cognos told us they were re-emphasizing the unix side of the business, because they realized it was significant. We evaluated Business Objects/Brio/Cognos back in 1998, and went with Cognos because they gave us Impromptu and PowerPlay (cubes and catalogs) for about the same price as the other two (just catalogs, or equiv). The others have probably improved since then, but their concept of cube was definitely different from cognos' back then. Building Cognos cubes is a resource hog; requires lots of disk space for their sorting temp files, and lots of memory/cpu. We have found problems when we try to build two large (~ 2GB) cubes simultanously, and we try to schedule these off-hours, but it is not unusual for large cubes to take 12 - 24 hrs to build. On the other hand, we have built as many as four small ( 500M) cubes simultaneously without problems. I would agree with the comment that it is not simple to learn these tools, and not simple to administer them. Hope this helps. That's too true. For a while we were building cubes on UNIX (actually HP-UX) systems, but Cognos completely desupported UNIX systems. And even when then did, the Windows cubes built a lot faster than the UNIX cubes did. -Original Message- Sent: Thursday, July 18, 2002 9:37 AM To: Vergara, Michael (TEM); Multiple recipients of list ORACLE-L John, One last point to add to my reply of before. They don't support HP-UX, actually I don't think they support any Unix. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Becker 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).
client-server in 9iR2
Hello, I have heard rumors that version 9.2 of the Oracle rdbms will not support client-server mode. Does anyone have more information regarding this? What exactly does this mean? We are a datawarehouse using a combination of 3rd-party tools, mainly Cognos, SAS, Toad, and we also have MS Access and Sql Server conencting to Oracle. Does not supporting client-server mean that any tool using ODBC to connect to Oracle will not work with 9iR2? I would guess that 3rd-party vendors using ODBC would need to find a different way to connect to Oracle, but those using some other connection method (OCI, for ex) would continue to work, but that's just a WAG, and I'm hoping someone can enlighten me. Thanks to any responders. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Becker 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).
offtopic - unix command
Hello, Apologies for the slightly off-topic listing, but I know there are several unix command gurus out there. (Bambi?) Oracle 8.1.6 on Solaris 2.7. I am trying to execute an rsh command against another unix server; the actual command is rsh pnas1 chkpntmk oradata ckpt1 if [ $? != 0 ]; then... pnas1 is name of other server; chkpntmk is command, rest are args. As you can see, I am checking the $? parameter to test for success/failure of the command. However, I think this is the return code for the rsh command, not the chkptmk command which is executed on the other server. When the chkpnt command fails, I still receive a 0 indicating success, because rsh executed OK. Is there a way to check for the success/failure of the actual remote command when using rsh? Thnaks to any responders. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Becker 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).
offtopic - unix command
But what if command blah does not output anything? In this case, $a is null, as it is when the command fails. Steven Lembark wrote: Is there a way to check for the success/failure of the actual remote command when using rsh? $a=$(rsh blah); and parse $a for output for an indication of the blah command succeeding or failing. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Becker 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).
single clustered tables
Hello, Env: Oracle 8.1.6 on Solaris 2.7 Someone has suggested the following idea, and I'm wondering if this is good/bad. Any comments, pro or con, are appreciated. The idea is, store datawarehouse data in clustered tables, 1 table per cluster. The rationale is that this imposes a physical sort order upon the data; if access is usually via the cluster key, access will be optimized. Objection 1) Most Oracle docs recommend: don't store data in clusters if it's going to be updated frequently. Updating clustered tables is bad. Rebuttal 1) That is true when several tables are in 1 cluster. If only a single table is contained in a cluster, this is of little concern. A simple test was run, executing a series of updates, deletes, inserts against a single-clustered table vs a non-clustered table. The results did not show much difference; in fact, the clustered table was slightly faster. Table had 17 million rows in approx .5GB. Objection 2) Conventional RDBMS theory says: the physical order of rows stored in an RDBMS should not be important. Rebuttal 2) True, if access paths are random. If the majority of access is via a single path, it makes sense to store the data in that order. Objection 3) Clustered tables require more space management, and may be wasteful if avg record size and block size are not reasonably matched. Rebuttal 3) True. But the benefit of faster access outweighs the slight disadvantage of better planning when the table is created and loaded. In the test mentioned above, the space consumed by the clustered table and index was comparable to the non-clustered table and index. Objection 4) Very few places seem to use the clustering feature. Rebuttal 4) That doesn't mean this is a bad idea, just unusual. Objection 5) If you want rows stored in order, use an index-organized table. Rebuttal 5) That does have significant updating problems, and is not practical unless you can drop and rebuild the entire table everytime it receives updates. Single-clustered tables do not appear to have these updating problems. Again, comments regarding the above or other related info is appreciated. Thanks to any responders. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Becker 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).
deferred constraints
Hello, Env: 8.1.6.0.0 on Solaris 2.7 From reading the docs regarding constraint states, it sounds like there is no difference in constraint behaviour between a constraint created as not deferred vs a constraint created as deferred and initially immediate. Is this correct? The only difference I can see is that a constraint created as deferred can subsequently be altered to other constraint states, whereas a constraint created as not deferred cannot. If the above is correct, is it a good practice to make the default for constraint creation deferred and initially immediate? The justification for this would be the flexibility of altering the constraint state, should the need ever arise. How do most of you create your constraints, as deferred or not deferred? Thanks to any responders. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Becker 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).
sqlloader errors
Hello, Env: Oracle 8.1.6.0.0 on Solaris 2.7 Does anyone know what controls sqlloader behaviour regarding the number of records it rejects when an error is encountered? We have a number of daily sqlldr processes; this morning, one sqlldr process rejected 7 records because of unique constraint violations. We are certain that only 1 record really had a violation, and the other 6 did not. The records rejected were all consecutive in the .dat file, and the .log file indicates that sqlloader is using 7 records in each batch commit. (Space allocated for bind array: 57792 bytes(7 rows)) Our conclusion is that sqlloader rejected all records in the same batch because 1 of them had an error. What is puzzling is that we have several other similar sqlloader processes, using multiple records in each batch, but all others only reject individual records with errors, not all records in the same array. The .ctl files for all of these processes are very similar, and contain mostly the same options: ROWS=1024, ERRORS=50 The default bindsize on this machine is 64K. Thanks to any responders. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Becker 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).
sqlloader errors
Oracle got back to me (very promptly) regarding the rejection of several records by sqlloader when only one record in the batch had an error. The short answer is that the violated PK constraint, unlike all others, had been created as DEFERRED. From Metalink: **( Source: BUGREP ) Bug:1744539 BugDB see Bug:1744539.-PBase Bug:1746223 Related Bugs : BugMatrix:1744539 Customer: ARTESIA SERVICES CV Created: 19-APR-01 Component: SQLLOADERComp Ver: 8.1.7Rel St: P Updated: 20-APR-01 Sub Comp: RDBMS Ver: 8.1.7 By: LDENS Status: 96,Closed, Duplicate Bug Sup Pri: 2,Severe Loss of Service Fixed In Ver: O/S: 453 Sun SPARC Solaris PL Group: UNIX Gen/Port: G Error #: - Pub: Y ** Hdr: 1744539 8.1.7 SQLLOADER 8.1.7 PRODID-5 PORTID-453 1746223 Abstract: SQLLDR REJECTS TOO MANY/FEW RECORDS WHEN PK DEFERRED INIT. IMMEDIATE VIOLATION *** LDENS 04/19/01 02:52 am *** = sqlldr rejects too many records when pk deferred initially immediate violation. = ... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Becker 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).
bulk collect in 9i
Hello, In 8i, the BULK COLLECT and FORALL commands are limited to working with scalar arrays. Does this restriction still apply in 9i? We are still on 8.1.6. In my case, I would like to do something like: TYPE tab_type IS TABLE OF oracle_table%ROWTYPE INDEX BY BINARY_INTEGER; plsql_table tab_type; SELECT * BULK COLLECT INTO plsql_table FROM oracle_table; and FORALL j IN plsql_table.FIRST..plsql_table.LAST INSERT INTO oracle_table VALUES (plsql_table(j)); Is this possible in 9i? Thanks to any responders. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Becker 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).
renaming datafiles
Hello, Oracle 8.1.6 on Solaris 2.7. Our test database is not available to play with for a while, and I was wondering about the following: What is the difference between ALTER TABLESPACE RENAME DATAFILE... and ALTER DATABASE RENAME DATAFILE...? The administrator's guide says If the tablespace is offline but the database is open, use the ALTER TABLESPACE...RENAME DATAFILE statement. If the database is mounted but closed, use the ALTER DATABASE...RENAME DATAFILE statement. While researching this issue, I came across Doc ID: Note:115424.1 on metalink, which contains an example of renaming/relocating datafiles while the database is open using the ALTER DATABASE command. This appears to contradict the administrator's guide. Which is correct? Can ALTER DATABASE be used in either case (database open, tblspace offline, or database in starup mount mode), while ALTER TABLESPACE can only be used if the database is open and the tblspc is offline? Also, the same note referenced above lists the following steps for moving datafiles while the database is up: 1) alter tablespace tbname read only; 2) copy the datafile(s) with os utility 3) alter tablespace tbname offline; 4) alter database rename datafile '...' to '...'; 5) alter tablespace tbname online; 6) alter tablespace read write; Would the following also work? I am thinking that the only reason the note lists the above method is to minimize the time the tablespace is offline. 1) alter tablespace tbname offline; 2) copy the datafile(s) with os utility 3) alter database rename datafile '...' to '...'; (or, alter tablespace rename datafile '...' to '...', not sure which should be used) 4) alter tablespace tbname online; Thanks to any responders. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Becker 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).
Default role all
Hello, Env: 8.1.6 on Solaris 2.7 Does anyone know which data dictionary table stores the setting when you issue an ALTER USER user_name DEFAULT ROLE ALL COMMAND? I know that dba_role_privs has a default_role (YES/NO) field, but that is specific to each role. Is there another table that stores a system-wide default role setting per user? The reason I ask, without getting too complicated, is that we used to assign new roles to users without specifying anything to do with default roles, and the newly-assigned role automatically became a default role. Recently, this has changed due to using both toad and sqlplus to administer roles, and I am wondering if there is another table where the system-wide default setting for roles is stored. If not, is there any way to assign new roles as default roles without having to specify alter user user_name default role all every time a new role is granted (or worse, an explicit list of roles)? Thanks to any responders. Bill Becker -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Becker 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).
Oracle on NFS
Jay Hostetter wrote: I know that at one time, Oracle recommended that you do not put files onto an NFS file system. I think the OS needs to guarantee that the data was actually written to disk, and when you use NFS the OS thinks the data was written, when in reality it may still be transferring across the network. Somebody else may be able to shed some more current information on this. Try http://www.oracle.com/ip/deploy/database/storage/content.html or search www.oracle.com for 'Oracle Storage Compatibility'. The last time I looked, there were 5 Oracle-approved vendors that offer NFS-mounted storage products. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Becker 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).
SAME, WAFL and RAID
Hello, I am looking for any pointers to white papers, etc. that discuss the differences/similarities among WAFL, SAME and RAID (shake, rattle and roll? - sorry) file systems. We have some systems people pushing a vendor (Procom) and their WAFL filesystem as a platform for an Oracle data warehouse; showing them parts of Gaja's paper Implementing Raid on Oracle Systems resulted in the response of WAFL is different from Raid - this paper doesn't apply. I found a white paper Optimal Storage Configuration Made Easy on Technet, which advocates the SAME methodology, and am wondering about the differences among these configurations. Are WAFL and SAME the same(sic)? Are WAFL and/or SAME just other variants of Raid, or are the differences greater than the similarities? As always, any advice/comments are appreciated. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Becker 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).
replication
Greetings, I am looking for advice regarding Oracle replication. We are on 8.1.6 EE, and will be upgrading to Oracle9 later this year. At that time, we also plan to establish another Oracle instance on a separate sun machine; 1 instance will serve as a staging area, the second will be a production reporting database. We need a way to quickly move processed data from the stage instance to the production instance on a daily basis. Methods we have discussed, pros and cons (please feel free to comment): Export/Import and flat file transfers have been ruled out due to speed. Transportable Tablespaces: Pros: fastest method of moving large amounts of data Cons: Constraints - our tables are very integrated, lots of foreign keys, just about every tablespace set would have to include a core set of reference tables, or the entire thing (500GB) would need to be in the same tablespace set; not including constraints means re-building them in the production instance, including indexes for PKs and UKs (I think) and probably other problems. How do others handle these problems? Also, this transfers all data, when only a very small percentage of rows ( 1% of total rows) has actually changed that day. Seems inefficient. Oracle Replication: Pros: The documentation seems to address our situation, replicating a small (relative to total db size) batched amount of data daily. (2-4 GB) Cons: Looks complex, 2 books (~760 pgs, ~360 pages in Oracle9), 13 packages. No experience with this - How well does it work? Is it difficult to set up? Any comments regarding speed? Can replication be set up for existing tables, or do they need to be re-created and re-loaded as a materialized view? We are also considering another solution, basically borrowing many of the ideas from Oracle replication and writing it ourselves. This would be a home-grown solution involving table triggers, additional tables to store the daily changes, and scripts to propagate the changes over database links. But before we decide, I wanted to hear what others had to say regarding Oracle replication. Thanks for any advice. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Becker 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).
Oracle on Procom
Hello, We are looking at buying a Procom NetForce box (a Network Attached Storage solution) for running a medium-sized (500GB) Oracle data warehouse (currently, 8.1.6 on Solaris 2.7). I would appreciate any comments regarding Procom NetForce from anyone with experience, pro or con. I found several messages regarding network appliances in the archives, but none about Procom. I have some specific questions: 1) Do Procom's checkpoints integrate in any way with Oracle backup and recovery, or are they useless if Oracle needs to stay up 24x7? 2) How well does their java-based administration GUI work? Are there any system adminstration tasks not included in this tool? 3) Are there any helpful Procom utilities for fast Oracle data transfer among different Oracle instances sharing the same Procom box, or do you still need to use an Oracle-based solution (replication, transportable tablespaces, etc)? 4) Is a gigabit ethernet connection between the Oracle server and Procomm box essential? Any performance measures? 5) Is it best to install Oracle_Home, redo logs, system and rollback tablespaces, and control files on the local Oracle server, and use the Procom box for all other index and data tablespaces? Any other advice or comments is appreciated. Thanks to any responders. Bill Becker - Marshfield Clinic - [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Becker 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).
Oracle on Procom
Thanks, Don. According to this link, there are 6 OSCP vendors: http://www.oracle.com/ip/deploy/database/storage/vendors.html Reply Separator Bill, To the best of my memory, the only network attached storage vendors certified to work with Oracle are NetAppliance and EMC. Anybody else and your on your own. Also be advised that you need a dedicated network between the NAS and Oracle server, and gigabit ethernet is not an overkill item. Dick Goulet Reply Separator Author: Bill Becker [EMAIL PROTECTED] Date: 12/17/2001 11:15 AM Hello, We are looking at buying a Procom NetForce box (a Network Attached Storage solution) for running a medium-sized (500GB) Oracle data warehouse (currently, 8.1.6 on Solaris 2.7). I would appreciate any comments regarding Procom NetForce from anyone with experience, pro or con. I found several messages regarding network appliances in the archives, but none about Procom. I have some specific questions: 1) Do Procom's checkpoints integrate in any way with Oracle backup and recovery, or are they useless if Oracle needs to stay up 24x7? 2) How well does their java-based administration GUI work? Are there any system adminstration tasks not included in this tool? 3) Are there any helpful Procom utilities for fast Oracle data transfer among different Oracle instances sharing the same Procom box, or do you still need to use an Oracle-based solution (replication, transportable tablespaces, etc)? 4) Is a gigabit ethernet connection between the Oracle server and Procomm box essential? Any performance measures? 5) Is it best to install Oracle_Home, redo logs, system and rollback tablespaces, and control files on the local Oracle server, and use the Procom box for all other index and data tablespaces? Any other advice or comments is appreciated. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Becker 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).
Oracle on Procom
Oops, sorry. 5 vendors, not 6. Reply Separator Thanks, Don. According to this link, there are 6 OSCP vendors: http://www.oracle.com/ip/deploy/database/storage/vendors.html Reply Separator Bill, To the best of my memory, the only network attached storage vendors certified to work with Oracle are NetAppliance and EMC. Anybody else and your on your own. Also be advised that you need a dedicated network between the NAS and Oracle server, and gigabit ethernet is not an overkill item. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Becker 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).
views with db links
Hello, Oracle 8.1.6 on Solaris 2.7. Can any one provide me with information or a link regarding performance of views across database links? I am thinking of the case where instance A has a view defined as SELECT FLD1,FLD2,... FROM TABLE_T1@DBLINK. Consider the case when a view such as this is referenced within a sql statement that joins this view with other local tables and also contains some WHERE conditions which reference a field in TABLE_T1. Are the entire contents of TABLE_T1 transferred across the network, then the joins and where conditions are processed within the local instance? Or is the WHERE condition applied in the remote instance, and only rows which satisfy the condition are returned across the network? I was under the impression that the former was true. Did the behaviour change in some previous version? Thanks to any responders. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Becker 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).
collections/plsql tables
I am also interested in this, as my current project sounds quite similar (developing an etl load process for a small Oracle warehouse using pl/sql). I don't have any real help to offer, only that I ran into the same limitation; I could not get FORALL and BULK COLLECT to work with composite record structures. I created several pl/sql tables containing records with a %ROWTYPE identical to an Oracle table, loaded and transformed the records, and tried several variations of FORALL using a record structure. What I would like to do is something like FORALL i IN mtab_records.FIRST..mtab_records.LAST INSERT INTO emp VALUES mtab_records(i); where mtab_records is a pl/sql table containing composite records; the record would have a %ROWTYPE of the target table, and the insert command would match up the record.fields with the table.columns, since they are identical. I would also like to do somewhat the reverse operation using BULK COLLECT. However, I have also concluded that FORALL and BULK COLLECT are very 1-dimensional, and that single dimension cannot be a composite type. If anyone knows different, would appreciate your comments as well. Good morning everyone, well I finally have something to work on. Not being one to whip out shoddy code, I want to write my load scripts utilizing pl/sql tables and caching as much as I can, along with utilizing FORALL and BULK COLLECT. The last time I did this, I was creating table rows in pl/sql INDEX-BY tables. I had one pl/sql table for each column in the target table (that I was going to insert modified rows to) and it worked fine, very fast in fact. However, it was an awful mess because I ended up maintaining many many INDEX-BY tables with one index to refer to each record. What I'm talking about is this table in the db is emp : enum number, ename varchar To represent this table in memory and assemble the records I created the following index-by tables at the module (package) level mtab_ename mtab_enum and inserted values like so mtab_enum(idx) := var1; mtab_ename(idx) := var2; and when it came time to insert, this is what I did FORALL i IN mtab_enum.FIRST..mtab_enum.LAST INSERT INTO emp (enum, ename) VALUES mtab_enum(i), mtab_ename(i); My question is, is there a way I can have one object that represents the structure of the entire emp table? I tried this TYPE emptabtype IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER; mtab_emp emptabtype; But this doesn't seem to work. I can't pull the values out (var := mtab_emp.ename(i)). I also don't want to use varrays just because I have to explicitly set the size. I also want to be able to use BULK COLLECT and FORALL. Otherwise this kind of stuff is a waste of time. I then read in the documentation that Collections can have only one dimension and must be indexed by integers. It sounds like what I want to do isn't possible. Any suggestions or comments are appreciated. Thanks Lisa Koivu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Becker 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).
constraint names
Hello,
Oracle 8.1.6 on Solaris 2.7
I am trying to write an exception handler that extracts the
constraint name from SQLERRM whenever a constraint violation
occurs. I am trying to do this by using the substr and instr
functions to extract just the constraint name; the reason is
that we want to try to handle certain constraint violations
programmatically, without human intervention; the first step is to
id the specific constraint violation.
Here is what I have so far:
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE); -- first 2 lines test only
DBMS_OUTPUT.PUT_LINE(SQLERRM);
IF (SQLCODE = -1) -- UK violation
OR (SQLCODE = -2290)-- CK violation
OR (SQLCODE = -2291) THEN -- FK violation
DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,(INSTR(SQLERRM,'.')+1),
(INSTR(SQLERRM,')')-INSTR(SQLERRM,'.'))-1 ));
ELSIF (SQLCODE = -1400) -- null insert violation
OR (SQLCODE = -1407) THEN -- null update violation
DBMS_OUTPUT.PUT_LINE('Table: '||SUBSTR(SQLERRM,(INSTR(SQLERRM,'',1,3)+1),
(INSTR(SQLERRM,'',1,4)-INSTR(SQLERRM,'',1,3))-1 )
);
DBMS_OUTPUT.PUT_LINE('Field: '||SUBSTR(SQLERRM,(INSTR(SQLERRM,'',1,5)+1),
(INSTR(SQLERRM,'',1,6)-INSTR(SQLERRM,'',1,5))-1 )
);
ELSE
DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,(INSTR(SQLERRM,'.')+1),
(INSTR(SQLERRM,')')-INSTR(SQLERRM,'.'))-1 ));
END IF;
Apologies for the formatting, but as you can see, some of the
expressions are long and ugly, and it depends upon the presence
of periods, parentheses and double quotes in SQLERRM. Moreover,
for not null violations, SQLERRM does not contain the constraint name,
so the code above isolates the table and field names.
This actually works, but my question is:
does anyone know a better way (not parsing SQLERRM) to programmatically determine
which constraint name has been violated using a pl/sql exception handler?
Thanks to all responders.
P.S. Count me in for $20 US for list support.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Bill Becker
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).
extended ascii codes
Hello, We are running Oracle 8.1.6 on Solaris 2.7. The characterset for our database is US7ASCII, as evidenced by: TS1SELECT * FROM NLS_DATABASE_PARAMETERS; PARAMETER VALUE -- NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_CHARACTERSET US7ASCII NLS_CALENDAR GREGORIAN NLS_DATE_FORMATDD-MON- NLS_DATE_LANGUAGE AMERICAN NLS_SORT BINARY NLS_TIME_FORMATHH.MI.SSXFF AM NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZH:TZM NLS_TIMESTAMP_TZ_FORMATDD-MON-RR HH.MI.SSXFF AM TZH:TZM NLS_DUAL_CURRENCY $ NLS_COMP BINARY NLS_NCHAR_CHARACTERSET US7ASCII NLS_RDBMS_VERSION 8.1.6.3.0 Now, when using US7ASCII, I don't understand how I can do this: TS1desc biltmp Name Null?Type - GENDERVARCHAR2(1) AGE NUMBER(3) TS1insert into biltmp (gender) values (CHR(176)); 1 row created. TS1insert into biltmp (gender) values (CHR(248)); 1 row created. TS1commit; Commit complete. TS1SELECT GENDER, ASCII(GENDER) FROM BILTMP; G ASCII(GENDER) - - ø 248 ° 176 The symbols seen above in the first column are not what I see in sqlplus, but cutting and pasting into this msg apparently converts them to the symbols above. My mail app is Windows-based, and the codeset used is apparently the same as the WE8ISO8859P1 codeset. What I see in sqlplus are the symbols that the extended ascii IBM codeset uses (248 is the small circle, 176 is a vertical rectangle filled with dots - hope I'm not getting too technical). Don't know what they will display as when I send this to oracle-l. Anyway, why can I insert ascii values above 127 into an US7ASCII database? In a US7ASCII database, does (server-side) sqlplus actually interpret all 8 bits for values above 127 as the extended ascii IBM codeset? Interestingly, from client sqlplus (8.0.6); TS1SELECT GENDER, ASCII(GENDER) FROM BILTMP; G ASCII(GENDER) - - x 248 0 176 Here, it apparently ignores the 8th bit (248 - 128 = 120, lower-case x). (176 - 128 = 48, zero), so I conclude that net80 somehow knows that the database characterset is 7-bit, and ignores the 8th bit. Is that conclusion correct? Is net80 responsible for this conversion, and is there any way I can tell it to interpret all 8 bits, short of changing the characterset of the database? To everyone who has read this far, thanks. To all who respond, even greater thanks. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Becker 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).
comments on tool
Hello, Anyone using a product called Oros Links, from ABC technologies? Someone apparently did a good sales job on a manager here, and they have requested we "look" at this product. We have an Oracle8i warehouse here running on Solaris, and my understanding is that this product will be used to tranfer data from our warehouse into the Oros product suite. I'm looking for comments, good or bad, on this tool, or any of the Oros suite of tools. Thanks to any responders. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Becker 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).
Hi-speed data link
Hello, For some reason, I was under the impression that it was not that unusual to achieve a data transfer rate of 100-200GB/hour over some sort of data link between separate machines. However, I am finding it hard to confirm this. There is an individual here who claims this is not practical without resorting to extraordinary (read expensive $10K) means. If anyone is actually doing this, I would very much appreciate hearing how fast a rate, and a general description of the hardware involved in the data link setup. In our case, it would be between two Sun machines running Oracle databases. Thanks to any responders. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Becker 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).
DataWarehouse Configuration
Hello, Some data warehousing questions: We are considering a setup where we have two separate machines, 1 for doing the ETL (ExtractTransformLoad)processing, and 1 for the production machine. Our env is Oracle 8.1.6 on Sun. The main idea is to insulate the production machine from the effects of ETL processing; the only impact ETL would have on the production machine would be when the ETL has completed and the data is copied over to the production machine, which leads me to my question: what methods have been used to minimize the time needed for this copy step? The amount of data to be transferred would be around 200GB, but expected to grow very fast. Both machines would be part of an existing ethernet network, and we've considered the following: 1) Just do the transfer over the existing ethernet network (figure about 150GB/hour) 2) Position the machines close to each other, and run a short (6-foot or less) cable between serial ports or parallel ports on both machines 3) Set up a separate network; install an ethernet card in each machine, and connect them with ethernet 4) Go to a "disk-farm" setup - don't know a lot about this, but both machines would access subsets of a large shared disk array (is this EMC? or other vendors?) The consensus is that fiberoptic, although faster, would be a waste since then the limiting factor would be disk read and write speeds. Anyway, I would appreciate any comments/suggestions regarding the above, especially #4, and any other approaches. Thanks to any responders. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Becker 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).
Queries over DB Links
Hello, We seem to have more and more questions regarding query performance across DB Links, and how to improve it. I have the Gurry/Corrigan book, but can't find anything on this specific topic. Can anyone point me to a good whitepaper or other article regarding query performance across DB Links? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Becker 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).
