Re: how to specify tablespace in Designer
Ben, If you had posted this question to designer-L you'd have gotten insight from some real experts. In designer table definition(table columns, indexes, keys, constraints) is different from table implementation(schema, tablespace, storage parameters). The same table definition can be associated with many different table implementation (e.g. different storage for the same table depending on test, prodction environment etc). In design editor's db admin tab you need to define database (under your application folder) then tablespace and/or storage definitions and users under that database. You also need to give users quota on tablespaces. Now generate the table DDL while in D Admin tab. Your DDL will now contain the all the artifacts. This is tedious process if you have large set of tables. If you want to do it repeatedly, create the database, tablespace, user and quotas ahead of time. While converting your ER model (you use ER modeler right?) using database design transformer, you can specify the table space for table and indexes. The transformer will create both table definitions and table implementations for you. Then from Design Editor's DB Admin tab you generate DDL for for your tables. --- Ben [EMAIL PROTECTED] wrote: Hi Can someone who has used Designer to generate DDL let me know how you specify which tablespace a table or index is to be created in. I am using the Design Editor and the DBAdmin tab and can't seem to find it. Thanks in advance. Ben -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ben 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). = Sundeep Maini Consultant Currently on Assignement at Caterpillar Peoria [EMAIL PROTECTED] __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: sundeep maini 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: SQL Query
Mike, Just wanted to supplement your example with another one from my environment (uses nested tables with CAST and MULTISET to achieve the same): SELECT c.customer_id ,customer_name ,utils.code_table_to_string(CAST( MULTISET( SELECT distinct dlr_dealer_code FROM dealer_customers dc ,dlr_dealer_master_t WHERE dc.customer_id = dc_in.customer_id AND dlr_dealer_id = dc.dealer_id ORDER BY dlr_dealer_code ) AS code_table_t )) spans_these_dealers FROM customers c ,( SELECT customer_id FROM dealer_customers GROUP BY customer_id HAVING COUNT(distinct dealer_id) 1 ) dc_in WHERE dc_in.customer_id = c.customer_id; Does eseentially the same thing. In this case it concatenates the dealer_codes per customer (identifies customers spanning multiple dealers) on the same row. Here is a sample of the output: 2969473096 Mountain Enterprises IncD100,D470,D480 2969473121 K C ConstructionB010,B150,B190 2969473195 GOODFELLOW BROS H140,H330 The TYPE code_table_t is defined as: TYPE CODE_TABLE_T IS table of varchar2(3000); And the conversion function is very similar in its functionality to dbms_utility.comma_to_table procedure: FUNCTION code_table_to_string (in_table code_table_t, in_rec_sep varchar2 DEFAULT config.c_rec_sep) RETURN delimited_list_t IS v_list delimited_list_t; BEGIN FOR i IN 1..in_table.COUNT LOOP IF i = 1 THEN v_list := in_table(1); ELSE v_list := v_list||in_rec_sep||in_table(i); END IF; END LOOP; RETURN v_list; END code_table_to_string; As opposed your suggestion of using the following: SELECT id, CONCAT_LIST(CURSOR( SELECT text FROM mike m_in ORDER BY ordr WHERE m_in.id = m.id)) list FROM ( select distinct id from mike) m; --- Mike Spalinger [EMAIL PROTECTED] wrote: Imran, Tom Kyte has a thread that might help: http://asktom.oracle.com/pls/ask/f?p=4950:8:1554835115460038644::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:229614022562, Essentially, you can do this: CREATE or replace FUNCTION CONCAT_LIST( cur SYS_REFCURSOR ) RETURN VARCHAR2 IS ret VARCHAR2(32000); tmp VARCHAR2(4000); BEGIN loop fetch cur into tmp; exit when cur%NOTFOUND; ret := ret || ' ' || tmp; end loop; RETURN ret; END; / SQL select * from mike; ID TEXT ORDR -- -- -- 1 B 2 1 A 1 1 C 3 SELECT id, SUBSTR(CONCAT_LIST(CURSOR( SELECT text FROM (select id, text, ordr from mike order by ordr) ee WHERE e.id = ee.id)),1,40) list FROM ( select distinct id from mike) e; ID LIST -- 1 A B C Mike Imran Ashraf wrote: Hi, I have the following data , table Temp ID Text Order 1 B2 1 A1 1 C3 I want to write a query which says: wherever there is more than 1 occurrence of ID then concatenate the text in the order specified in the order column. So i would get: ID Text 1 A B C Any suggestions? Thanks Imran -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mike Spalinger 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). = Sundeep Maini Consultant Currently on Assignement at Caterpillar Peoria [EMAIL PROTECTED] __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: sundeep maini 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
Re: Executing stored procedure from diff user
To fix this problem, compile your package with AUTHID CURRENT USER
directive in the Package Signature.
Basically when user B executes package of user A, for the course of
the package's run user B becomes user A and the ownership context is
that of user A's. When user B runs show_user(tablename), effectively
ORacle is trying to resolve tablename in user A's namespacewhich
is doomed for failure. Worse if A, B and C both have tables with the
same name, you will be reporting incorrect results (always A's
tables).
HTH
--- Saminathan Seerangan [EMAIL PROTECTED] wrote:
Hi List,
Could you please help me out to resolve this issue?
Basically i have created one stored procedure(show_space) in
SYSTEM schema, then granted execute rights to PUBLIC and created
public synonym.
When i conenct as diff user(MUT) I am not able to execute the
procedure. Any help would be really appreciated.
TIA
SQL connect system/[EMAIL PROTECTED]
Connected.
SQL @D:\share\oracle\Asktom\show_space8i.sql
51 /
Procedure created.
SQL grant execute on show_space to public;
Grant succeeded.
SQL create public synonym show_space for show_space;
Synonym created.
SQL connect mut/[EMAIL PROTECTED]
Connected.
SQL execute show_space('RW_RFX_HEADER');
BEGIN show_space('RW_RFX_HEADER'); END;
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at SYS.DBMS_SPACE, line 55
ORA-06512: at SYSTEM.SHOW_SPACE, line 22
ORA-06512: at line 1
SQL desc show_space
PROCEDURE show_space
Argument Name TypeIn/Out
Default?
-- --- --
P_SEGNAME VARCHAR2IN
P_OWNERVARCHAR2IN
DEFAULT
P_TYPE VARCHAR2IN
DEFAULT
SQL desc dbms_space
PROCEDURE FREE_BLOCKS
Argument Name TypeIn/Out
Default?
-- --- --
SEGMENT_OWNER VARCHAR2IN
SEGMENT_NAME VARCHAR2IN
SEGMENT_TYPE VARCHAR2IN
FREELIST_GROUP_ID NUMBER IN
FREE_BLKS NUMBER OUT
SCAN_LIMIT NUMBER IN
DEFAULT
PARTITION_NAME VARCHAR2IN
DEFAULT
PROCEDURE UNUSED_SPACE
Argument Name TypeIn/Out
Default?
-- --- --
SEGMENT_OWNER VARCHAR2IN
SEGMENT_NAME VARCHAR2IN
SEGMENT_TYPE VARCHAR2IN
TOTAL_BLOCKS NUMBER OUT
TOTAL_BYTESNUMBER OUT
UNUSED_BLOCKS NUMBER OUT
UNUSED_BYTES NUMBER OUT
LAST_USED_EXTENT_FILE_ID NUMBER OUT
LAST_USED_EXTENT_BLOCK_ID NUMBER OUT
LAST_USED_BLOCKNUMBER OUT
PARTITION_NAME VARCHAR2IN
DEFAULT
Free multi-lingual web-based and POP3 email service with a
generous 15MB of storage, a choice of themes for your mailbox,
message filtering, plus spam and virus protection
Sign up now: http://www.gawab.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Saminathan Seerangan
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).
=
Sundeep Maini
Consultant
Currently on Assignement at Caterpillar Peoria
[EMAIL PROTECTED]
__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: sundeep maini
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
Re: Which is beter a cursor or a for loop?
Proof of pudding is in eating Whip up a couple of examples and check out the timings. I suspect the CURSOR FOR LOOPS would run faster. I recall a similar mention by Tom Kyte in Oracle magazine while answering a question about explicit and implicit cusrsors. Unlike the conventional wisdom (a.k.a Feuerstein's recommendation in his best selling books) to use explicit cursors, he showed via an example how and why the implicit cursors are better (Key difference was the processing PL/SQL had to do for the explicit cursor). It is pretty much the same for FOR LOOP (it opens, closes and fetches from the cursor for you) and is likly to run faster. - Sundeep --- Denham Eva [EMAIL PROTECTED] wrote: Hello, I was just asked by one of our developers which is beter to use:- a cursor or a for loop? I must admit I am not sure Anyway the specific piece of code in discussion is similar to the following FOR X IN (SELECT X FROM TABLE_NAME WHERE COL1 = 'Something')) LOOP Do a whole lot of stuff in database here.. LOOP END; I would guess that the cursor would follow similar execution criteria but using the cursor syntax. Any ideas? TIA regards Denham Eva Oracle DBA The real problem is not whether machines think but whether men do. - B. F. Skinner _ DISCLAIMER This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. TFMC, its holding company, and any of its subsidiaries each reserve the right to monitor and manage all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. _ This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal For more information please visit www.marshalsoftware.com _ = Sundeep Maini Consultant Currently on Assignement at Caterpillar Peoria [EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: sundeep maini 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: SQL Tuning Help
Dennis, I tend to treat use of hints for exceptional cases only. With collections CASTed as tables, I seem to have a generalized problem of tables involved being scanned FULL (not using the available indexes) and query response being slow. I can't seem to build a query with collections and have it use the indexes. I get a much better response if I: a)parse the input collection parameter and use dynamic SQL with IN list b) use the RULK hint We also Analyze all tables in the schema once a week and were hoping that the cast based optimizer would have detailed information available to use the correct access path. --- DENNIS WILLIAMS [EMAIL PROTECTED] wrote: Sundeep - Have you tried other hints, like FIRST_ROWS? Or are you trying to avoid hints entirely? Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 05, 2003 5:30 PM To: Multiple recipients of list ORACLE-L Oracle 8.1.7.4 on HP-UX I am using collections to pass multiple values from client to the database to gather values for more than input values. The queries produce the results but the without the RULE hint the response is dramatically slower. Following is just one of the examples but I have many many queries which exhibit the same behavior. Since RULE based optimization is headed for the chopping block we are wondering what is the alternative. SELECT eqp.equipment_id, eqp.manufacturer_code, eqp.model_num, eqp.equipment_serial_num, DECODE(SIGN(eqp.last_pm_performed_at_hrs - eqp.current_meter_reading_hrs),1, 'SMU_ADJUSTED',eqp.last_pm_performed_at_hrs) smu_adjusted, eqp.productlink_equipment_code, ecps.pm_schedule_name, epp.performed_datetime, DECODE(epp.comment_text,NULL,1,0) comments_available, emr.reading_date, emr.meter_reading_value, equipment_event_log.event_status(eqp.equipment_id,2), equipments_next_pm_due.pms_due_list(eqp.equipment_id) pms_due_list, equipments_next_pm_due.perform_at_hrs_cnt(eqp.equipment_id) next_pm_hrs FROM equipments eqp, equipment_meter_readings emr, equipment_pm_performed epp, equipment_class_pm_schedules ecps, TABLE(CAST(id_table_t(100071,100072,100073,100074) AS id_table_t)) eqp_list WHERE eqp.epp_id_last_pm_performed = epp.epp_id (+) AND eqp.emr_id_current_meter_reading = emr.emr_id (+) AND epp.ecps_id = ecps.ecps_id (+) AND eqp.equipment_id = eqp_list.column_value / Plan: SELECT STATEMENT Hint=CHOOSE 8 K 510 HASH JOIN OUTER 8 K 582 K 510 HASH JOIN OUTER 8 K 510 K 497 HASH JOIN OUTER 8 K 390 K 489 HASH JOIN 8 K 279 K 287 COLLECTION ITERATOR CONSTRUCTOR FETCH TABLE ACCESS FULL EQUIPMENTS 192 K 6 M 256 TABLE ACCESS FULL EQUIPMENT_METER_READINGS221 K 2 M 151 TABLE ACCESS FULL EQUIPMENT_PM_PERFORMED 96 1 K TABLE ACCESS FULL EQUIPMENT_CLASS_PM_SCHEDULES2 K 22 K4 Following is the Plan with /*+ RULE */ hint has the expected fast response and the desired plan: SELECT STATEMENT Hint=HINT: RULE NESTED LOOPS OUTER NESTED LOOPS OUTER NESTED LOOPS OUTER NESTED LOOPS COLLECTION ITERATOR CONSTRUCTOR FETCH TABLE ACCESS BY INDEX ROWID EQUIPMENTS INDEX UNIQUE SCAN EQP_PK TABLE ACCESS BY INDEX ROWID EQUIPMENT_PM_PERFORMED INDEX UNIQUE SCAN EPP_PK TABLE ACCESS BY INDEX ROWID EQUIPMENT_CLASS_PM_SCHEDULES INDEX UNIQUE SCAN ECPMS_PK TABLE ACCESS BY INDEX ROWID EQUIPMENT_METER_READINGS INDEX UNIQUE SCAN EMR_PK I have tried both versions IN (TABLE(CAST( as a predicate and as a pseudo-table in FROM (as in the query above) and it made no difference to the plan. I searched askTOM and heard similar sentiments about performance being echoed by other users but no solutions. Any tips or insights as to how to avoid the full table scans (all of which are 10-100M in size) of the large table without the RULE hint. A more thorough explanation of what is happening and why would be a bonus. TIA = Sundeep Maini Consultant Currently on Assignement at Caterpillar Peoria [EMAIL
SQL Tuning Help
Oracle 8.1.7.4 on HP-UX I am using collections to pass multiple values from client to the database to gather values for more than input values. The queries produce the results but the without the RULE hint the response is dramatically slower. Following is just one of the examples but I have many many queries which exhibit the same behavior. Since RULE based optimization is headed for the chopping block we are wondering what is the alternative. SELECT eqp.equipment_id, eqp.manufacturer_code, eqp.model_num, eqp.equipment_serial_num, DECODE(SIGN(eqp.last_pm_performed_at_hrs - eqp.current_meter_reading_hrs),1, 'SMU_ADJUSTED',eqp.last_pm_performed_at_hrs) smu_adjusted, eqp.productlink_equipment_code, ecps.pm_schedule_name, epp.performed_datetime, DECODE(epp.comment_text,NULL,1,0) comments_available, emr.reading_date, emr.meter_reading_value, equipment_event_log.event_status(eqp.equipment_id,2), equipments_next_pm_due.pms_due_list(eqp.equipment_id) pms_due_list, equipments_next_pm_due.perform_at_hrs_cnt(eqp.equipment_id) next_pm_hrs FROM equipments eqp, equipment_meter_readings emr, equipment_pm_performed epp, equipment_class_pm_schedules ecps, TABLE(CAST(id_table_t(100071,100072,100073,100074) AS id_table_t)) eqp_list WHERE eqp.epp_id_last_pm_performed = epp.epp_id (+) AND eqp.emr_id_current_meter_reading = emr.emr_id (+) AND epp.ecps_id = ecps.ecps_id (+) AND eqp.equipment_id = eqp_list.column_value / Plan: SELECT STATEMENT Hint=CHOOSE8 K 510 HASH JOIN OUTER 8 K 582 K 510 HASH JOIN OUTER 8 K 510 K 497 HASH JOIN OUTER 8 K 390 K 489 HASH JOIN 8 K 279 K 287 COLLECTION ITERATOR CONSTRUCTOR FETCH TABLE ACCESS FULL EQUIPMENTS 192 K 6 M 256 TABLE ACCESS FULL EQUIPMENT_METER_READINGS221 K 2 M 151 TABLE ACCESS FULL EQUIPMENT_PM_PERFORMED 96 1 K TABLE ACCESS FULL EQUIPMENT_CLASS_PM_SCHEDULES2 K 22 K4 Following is the Plan with /*+ RULE */ hint has the expected fast response and the desired plan: SELECT STATEMENT Hint=HINT: RULE NESTED LOOPS OUTER NESTED LOOPS OUTER NESTED LOOPS OUTER NESTED LOOPS COLLECTION ITERATOR CONSTRUCTOR FETCH TABLE ACCESS BY INDEX ROWID EQUIPMENTS INDEX UNIQUE SCAN EQP_PK TABLE ACCESS BY INDEX ROWID EQUIPMENT_PM_PERFORMED INDEX UNIQUE SCAN EPP_PK TABLE ACCESS BY INDEX ROWID EQUIPMENT_CLASS_PM_SCHEDULES INDEX UNIQUE SCAN ECPMS_PK TABLE ACCESS BY INDEX ROWID EQUIPMENT_METER_READINGS INDEX UNIQUE SCAN EMR_PK I have tried both versions IN (TABLE(CAST( as a predicate and as a pseudo-table in FROM (as in the query above) and it made no difference to the plan. I searched askTOM and heard similar sentiments about performance being echoed by other users but no solutions. Any tips or insights as to how to avoid the full table scans (all of which are 10-100M in size) of the large table without the RULE hint. A more thorough explanation of what is happening and why would be a bonus. TIA = Sundeep Maini Consultant Currently on Assignement at Caterpillar Peoria [EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: sundeep maini 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: SQL question
SELECT COUNT(*) FROM ( SELECT DISTINCT col1, col2. FROM ..) --- Charu Joshi [EMAIL PROTECTED] wrote: Thanks all, My question was related more to the 'design' of SQL language. To my mind the expression COUNT(DISTINCT a,b) looked a natural extension of the syntax COUNT(DISTINCT a). Even COUNT(DISTINCT(a,b)) would look good enough to me. Probably it's too trivial a thing to bother about. Using the subquery would very well give the desired results. I have been thinking of reading CJ Date and other experts' articles on the design (and limitations) of SQL, but couldn't find any good resources on the net. If you know of any links, then can you please let me know? Thanks once again, Charu. -Original Message- Sent: Wednesday, January 29, 2003 10:04 PM To: Multiple recipients of list ORACLE-L Charu, The COUNT() function requires a single expression. ename, job is not a valid expression. ename||job is a valid expression since it will return a single value. Another alternative would be select count(*) from (select distinct ename, job from emp); Dan Fink -Original Message- Sent: Wednesday, January 29, 2003 11:19 AM To: Multiple recipients of list ORACLE-L Hello Listers, How to find out the COUNT of DISTINCT values of multiple columns? For eg. SQL SELECT DISTINCT ename FROM emp; -- This works. SQL SELECT COUNT(DISTINCT ename) FROM emp; -- So does this. SQL SELECT DISTINCT ename, job FROM emp; -- And this too. SQL SELECT COUNT(DISTINCT ename, job) FROM emp; -- So why does this fail? I can do a COUNT(DISTINCT ename || job), but that doesn't seem to be elegant way of doing it. I have a feeling I might be missing some fairly basic syntax, but feeling dumb is better than suspense. Thanks regards, Charu. * Disclaimer This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. * Visit us at http://www.mahindrabt.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Charu Joshi 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.net -- Author: Fink, Dan 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). * Disclaimer This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. * Visit us at http://www.mahindrabt.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Charu Joshi 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). = Sundeep Maini
RE: Table Naming Conventions
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). = Sundeep Maini Consultant Currently on Assignement at Caterpillar Peoria [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Health - Feel better, live better http://health.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sundeep maini 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: SQL Tuning - How to avoid TOCHAR function against a date
) WEEK_NUMBER_IN_YEAR NOT NULL NUMBER(3) WEEK_NUMBER_OVERALL NOT NULL NUMBER(7) MONTH NOT NULL VARCHAR2(30) MONTH_NUMBER_OVERALLNOT NULL NUMBER(7) YEARNOT NULL NUMBER(5) WEEKDAY_IND NOT NULL CHAR(1) LAST_DAY_IN_MONTH_IND NOT NULL CHAR(1) DATA_WAREHOUSE_MOD_DATETIME NOT NULL DATE DATA_MART_MOD_DATETIME NOT NULL DATE SQL select oracle_date from date_dim where rownum=1; ORACLE_DA - 01-JAN-70 Thanks in advance for any help. Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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 === message truncated === = Sundeep Maini Consultant Currently on Assignement at Marshfield Clinic WI [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sundeep maini 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).
8.1.7 LMTs Autoallocate vs Uniform Extents
Can someone point me to good reading material on this subject. Is one better than the other for performance and manageability? Syntactically the autoallocate is shorter and seems to be more hands off (does that mean worry free also?). TIA = Sundeep Maini Consultant Currently on Assignement at Marshfield Clinic WI [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Movies - coverage of the 74th Academy Awards® http://movies.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sundeep maini 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).
Urgent: Dictionary - Missing Column
We have an issue with compiling PL/SQL code. The compilation gives PLS-00201 identifier must be declared on a certain column which exists in the table (a describe or select from the table works). An export and import of our test db made the problem go away. We had the same bug happen again in QA and again export and import set it right but we can't do this in production. Our environment is 8.1.7 on HP-UX11. Has anyone see this bug or knows a workaround? TIA = Sundeep Maini Consultant Currently on Assignement at Marshfield Clinic WI [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sundeep maini 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).
Searching Inside CLOBs
Our developers store XML docuements inside CLOBS and want to search the CLOB contents based on a search string. I have lot of concern about such queries but could benefit from someone on the list who has already solved the performnce issues with such searches. Typical existing queries I have come across are: Select columns FROM list of tables WHERE join conditions AND UPPER(tab1.varcharcol) LIKE '%:in_string%' OR UPPER(tab2.varcharcol) LIKE '%:in_string%' OR . OR dbms_lob.instr(lobcol,:in_string,1,1) 0; Note: in_string is always upper case but the columns are Concerns: 1. Poor index selection due to UPPER 2. CLOB searches are obviously wrong. To do a mixed case string match one needs to read chunks of the lob in a user defined function of some sort, convert it to upper and then do string matches. Any tips on this one? = Sundeep Maini Consultant Currently on Assignement at Marshfield Clinic WI [EMAIL PROTECTED] __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sundeep maini 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).
Backend Architecture in 3 tier environment
In our current environment most database interaction is via DMLs issued from client or middle tier via embeded SQL calls. Is it always a good idea to go stored procedure (packages) route? In my opinion going with stored procedure route isolates DB design from the upper tiers (creates an API via stored procedure signatures making most DB changes transparent to client least the signature is changed). It also offers the advantage of pre-compiled, pre-tuned logic vs the JAVA/Client coders embedding SQLs of their choice whereever they want. It is also possible to use more Database facilities or features than palin SQL alone would accord. Finally, I can reverse engineer them into Designer/ERWin which allows me to know impact of cahnges on the DB. However, in many cases of small SQLs issued directly from Java the PL/SQL route may have overheads. Also, the OO programmers who are usually responsible for the app design have little experince designing DB structures much less concerened about DB tuning which makes it difficult to get a good seperation between data service layer and other layers. Can I ask the group to share their experience and/or opinion? Are there potential downsides to stored procedures route? TIA Sundeep = Sundeep Maini Consultant Currently on Assignement at Marshfield Clinic WI [EMAIL PROTECTED] __ Do You Yahoo!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sundeep maini 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).
Searching Inside CLOBs
Our developers store XML docuements inside CLOBS and want to search the CLOB contents based on a search string. I have lot of concern about such queries but could benefit from someone on the list who has already solved the performnce issues with such searches. Typical existing queries I have come across are: SELECT columns FROM list of tables WHERE join conditions AND UPPER(tab1.topic) LIKE '%:in_string%' OR UPPER(tab2.subtopic) LIKE '%:in_string%' OR . OR dbms_lob.instr(bodytext,:in_string,1,1) 0; Note: in_string is always upper case but the column contents are not. Concerns: 1. Poor index selection due to UPPER 2. CLOB searches are wrong as the CLOB contents are case sensitive. One way to remedy this would be to build a user defined function which will read the CLOB col. in chunks and look for string matches inside individual chunks till a success or end of CLOB. Either way that is a pretty intense way to search for a string among table data when a user is waiting for a quick response. Keyword searches are supported differently. This mechanism is only for random string searches. Any cues or suggestions? Especially for case sensitive searches inside CLOBS? TIA Sundeep = Sundeep Maini Consultant Currently on Assignement at Marshfield Clinic WI [EMAIL PROTECTED] __ Do You Yahoo!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sundeep maini 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: How to stop access to prod instance ...
Raj, If you are using 8i or above, I would highly recommend you go through fine grained access features. They are a bit exhaustive to cover here but I have used them previously for some very complex stratifications and they will work for your requirements too. Here is a brief note for each of your criterion but you will have to build the procedures. Use log on triggers with fine grain access (dbms_rls and sys_context/user_context) to enable roles from forms application and impose user/role restrictions at logon. You can design appropriate roles to segregate users to control their behavior. This solution is not profile dependent and not tool specific. Albit it will have the context of an application(s) and alter the queries and set the initial environment/restrictions trough a trusted PL/SQL package whichever client the queries may be issued from. You also have the potential to add dynamic predicates to further control query behavoir. Your reporting tool doesn't have to do anything special like invoking a runtime role. The logon triggers the environment change or enabling of specialized roles. You can store policies with tables and you can segregate behavior for selects vs updates (upd,ins,del - by encapsulating them in packages if required or simply by adding appropriate dynamic predicate to your table and Oracle will rewrite the queries involving those tables and views by including the predicate). In a rare departure, the Oracle manuals (application developers guide and dbms_rls documentation in pl/sql packages reference and sys_context documentation) will give you all the fodder to feed your fancy. Not only what you propose is doable, I am sure you'll find more flexibility than you have asked for. HTH Sundeep --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: Thanks all ... The problem doesn't end here ... the requirements (loosely specified) are as follows 1. Developers and end users can access production ONLY through FORMS APPLICATION. 2. End users can't get to sqlplus so I am not even worried about that. It is developers that I am worried about. 3. Enabling roles at runtime is an option as long as we are not talking about reports. We use SQR and Oracle reports, a reports agent looks up requests and creates shell files on Unix server which are then scheduled to run. This scheme should work there as well. 4. How about people accessing tables through db links? I don't think there is a 100% proof method, but basically I want to be able to restrict developers accessing the instance except through forms application. Dropping developer's accounts is not an option. I thought over it, discussed it with my senior DBAs and finally I am turning to you guys. All the suggestions are helpful but I'd like to avoid as many loose ends as possible. Thanks in advance Raj __ Rajendra JamadagniMIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! *1 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *1 = Sundeep Maini Consultant Currently on Assignement at Marshfield Clinic WI [EMAIL PROTECTED] __ Do You Yahoo!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sundeep maini 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: Fwd: Re: How to select and display 10 records at a time from a table using sql query
You can use the Rank () function to rank your rows based on a simple or complex criteria in a subquery or an inline view and then extract the requisite set by using a simple condition on the Rank col. Below is a simple example just to give you some idea of what is possible. You may want to take a look at some ref manual to explore this for meeting your particular requirement (Best place is Datawarehousing Guide): SELECT keycol, some_other_col(s) FROM (SELECT keycol, some_other_col(s), RANK() OVER (ORDER BY keycol) AS Ranking FROM mytable WHERE condition(s) ) WHERE ranking between 11 and 20; == alter this cond. to get a different set. You can use more complex criteria in ranking/ordering your rows. --- Jan Pruner [EMAIL PROTECTED] wrote: Does anybody know how to do it better way? But this will return only the first ten rows not subsequent sets. How do I display rows from 11 to 20, 21 to 30 and so on? ROWNUM is counting output tuples. You cannot use ROWNUM 10. I think you have to order select by your primary key, make filter based on your primary key and take only first 10 tuples using ROWNUM. JP -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Jan Pruner Sent: Wednesday, August 29, 2001 4:01 PM To: Multiple recipients of list ORACLE-L Subject: Re: How to select and display 10 records at a time from a table using sql query select * from ( select column1, column2 from my_table order by 1) where ROWNUM 11; Look at manual how to use ROWNUM. It's ease to make mistakes with it. JP Dne st 29. srpen 2001 10:55 jste napsal(a): Dear DBA Gurus, How do I select and display 10 records at a time from a table using sql query only? Thanks and Regards, Ranganath DISCLAIMER: This correspondence is confidential and intended for the named recipient(s) only. If you are not the named recipient and receive this correspondence in error, you must not copy, distribute or take any action in reliance on it and you should delete it from your system and notify the sender immediately. Unless otherwise stated, any views or opinions expressed are solely those of the author and do not represent those of Subex Systems Limited. www.subexgroup.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner 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). DISCLAIMER: This correspondence is confidential and intended for the named recipient(s) only. If you are not the named recipient and receive this correspondence in error, you must not copy, distribute or take any action in reliance on it and you should delete it from your system and notify the sender immediately. Unless otherwise stated, any views or opinions expressed are solely those of the author and do not represent those of Subex Systems Limited. www.subexgroup.com --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner 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). = Sundeep Maini Consultant Currently on Assignement at Marshfield Clinic WI [EMAIL PROTECTED] __ Do You Yahoo!? Get email alerts NEW webcam video instant messaging with Yahoo! Messenger http://im.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sundeep maini 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
Re: OLAP question
Bala, Couple of things, first you can find lots of documentation and examples in Oracle 8i Data Warehousing Guide (ch-9 Dimesions, ch-15-summary advisor and ch-19 Query Rewrite). Also, dimensaions are not used in query syntax but used by Optimizer for query rewites and for better understanding of the innate nature of the data. In an OLAP environment you can't go wrong declaring dimensions and hierarchies but they are just declarative constructs for the Optimizer much like Optimizer Hints. Im not sure if there is more to your question but would the following select get you the mobil number transactions? SELECT co.company_code, co.company_desc, co.parent_company_code, NVL(txn.mobil_num,'No mobile usage on '||txn_date) FROM company_dim co WHERE co.company_code = desired_company_co AND co.parent_company_code = txn.company_code (+) AND txn.activity_date = txn_date; --- Bala, Prakash [EMAIL PROTECTED] wrote: Hi, I am a newbie to this OLAP area. I have this table (company_dimension) where every company has a parent, which in turn can be a child to another parent and so on. It is about 10 levels deep. TEST@D0MARS desc company_dimension Name Null?Type - COMPANY_CODE NOT NULL VARCHAR2(50) --- primary key COMPANY_DESC VARCHAR2(100) PARENT_COMPANY_CODEVARCHAR2(50) --- foreign key pointing to company_code The following table maintains the mobile phone numbers for every company. TEST@D0MARS desc DLY_TRANSACTION_DETAIL Name Null? Type -- -- ACTIVITY_DATE DATE COMPANY_CODE VARCHAR2(10) --- foreign key to the company_dimension table MOBILE_NUMBER NUMBER(10) The requirement is to get all the phone numbers for a given company and its children for a given day. Since I cannot join these 2 tables using the 'connect by' clause, how can I accomplish it, without using a sub-query? (actually 2 other dimensions play a role in the above table and I have simplified the structure) Created a dimension on the company_dimension table and used the dbms_olap.validate_dimension procedure to validate it. Everything looks good, but none of the manuals explains how to use the dimension in a query. This is Oracle 8.1.7 on Sun Solaris. Thanks in advance. Prakash -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bala, Prakash 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). = Sundeep Maini Consultant Currently on Assignement at Marshfield Clinic WI [EMAIL PROTECTED] __ 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: sundeep maini 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: Stored procedures that return multiple rows
I was about to write a similar testimonial when I saw Steve Orr's message. Don't use OCI based class111.zip. It has Oracle properity classes. The pure java class111.zip works great and is portable. Passing ref cursors from stored procedures makes the solution simple and elegent to implement. The seperation between the client rendering (HTML/Javascript/XML crowd) and middle tier Java servlets and Middle tier and RDBMS developer (using ref cursors over thin JDBC) is perhaps the best way to go. Avoid embeded SQL in Java if possible. Access to RDBMS via ref_cursor hides the DB schema from the Java developers making DB schema changes less painful and also allowing future switch of the databases if required, lot less painful. No meddling from Java folks too. I don't know how anybody decides on application partitioning issues as this division between business/domain objects and their persistence in the database raises all sorts of interesting design issues Regds --- Orr, Steve [EMAIL PROTECTED] wrote: On my previous gig all DB access was via stored procedures returning ref cursors through JDBC. The Java dweebs could do just about anything with the ref cursor. It worked really well. I could actually tune SQL queries on the running production application without any recompilation. Nice seperation of code. The DBA can tune real SQL code and the developers won't even know. No embedded SQL. Yeah!! OCI can also accommodate ref cursors. Steve Orr -Original Message- Sent: Thursday, August 23, 2001 2:11 PM To: Multiple recipients of list ORACLE-L We're considering a mandate that all database access be via stored procedures (probably in packages). These would be called either via OCCI (the C++ call interface) or JDBC. My question is whether anyone's had experience in returning a result set from a PL/SQL procedure under these circumstances, and how it was implemented: did you return a ref cursor, an index-by table, a set of arrays? Any advice will be appreciated. Thanks! Paul Baumgartel MortgageSight Holdings, LLC [EMAIL PROTECTED] = Sundeep Maini Consultant Currently on Assignement at Marshfield Clinic WI [EMAIL PROTECTED] __ 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: sundeep maini 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: dimension time question
Joe, If you had only 1..4 values for quarters you can compare quarters across years or to each other in your SQL queries. If users wanted to compare particular quarters e.g year 2000 Q1 to year 1999 Q4 they could always use both Year and Quarter fields in the queries. However if they wanted to compare Q1 in last 5 years with Q2 or Q3 over the same period you will make their life in the quering tool difficult. You'd be better off using 1..4 or Q1..Q4 values over 20011 etc. --- JOE TESTA [EMAIL PROTECTED] wrote: my latest assignment takes me into the world of DW. the dm_time table has: dm_time_id number .. .. .. sales_year number(4) sales_qtr number(1) sales_month number(2) sales_week number(2) sales_day number(3). so an entry looks like: time_id 10456 sales_year=2001 sales_qtr=1 sales_month=1 sales_week=1 sales_day=1 to rollup the data we define dimensions. I was asked this: In a normal time_dimension, doesn't qtr need to have the year as part of it, so that it looks like 20011 for the 1st qtr? thanks, joe = Sundeep Maini Consultant Currently on Assignement at Marshfield Clinic WI [EMAIL PROTECTED] __ Do You Yahoo!? Send instant messages get email alerts with Yahoo! Messenger. http://im.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sundeep maini 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: rename foreign keys from system-assigned constraint names to more
|| ')'; DBMS_OUTPUT.put_line 'alter table ' || cons_rec.owner || '.' || cons_rec.table_name ); DBMS_OUTPUT.put_line 'drop constraint ' || cons_rec.constraint_name || ';' ); DBMS_OUTPUT.put_line 'alter table ' || cons_rec.owner || '.' || cons_rec.table_name ); IF v_ctr 0 THEN v_global_name := 'FK_' || cons_rec.table_name || '_' || cons_rec.RTable || to_char(v_ctr); ELSE v_global_name := 'FK_' || cons_rec.table_name || '_' || cons_rec.RTable; END IF; IF length( v_global_name ) 29 Then IF v_ctr 0 then v_global_name := 'FK_' || cons_rec.RtrimTable || '_' || cons_rec.RtrimRTable || to_char(v_ctr); ELSE v_global_name := 'FK_' || cons_rec.RtrimTable || '_' || cons_rec.RtrimRTable; END IF; END IF; DBMS_OUTPUT.put_line 'add constraint ' || v_global_name || ' foreign key ' || v_fklist ); DBMS_OUTPUT.put_line 'references ' || cons_rec.ROwner || '.' || cons_rec.Rtable || ' ' || v_fklist_R || ';' ); DBMS_OUTPUT.new_line (); END LOOP; END; -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Glenn Travis 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 === message truncated === = Sundeep Maini Consultant Currently on Assignement at Marshfield Clinic WI [EMAIL PROTECTED] __ 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: sundeep maini 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: Unique Index or Constraint??
Vishak, Unique constraints by default leads to creation of a unique index by Oracle. This index is used for imposing uniqueness. If you have a unique index in mind then constraint is the way to go. 1. An explicit constraint allows you to use Oracle mechanisms to trap exceptions in another table while ENABLEing a previously disabled constraint or trap errors in your code. 2. Optimizer can make use of constraint declarations for query rewrite. 3. If you are 8i you can define a unique constraint in DISABLEd VALIDATE mode and have Oracle skip the automatic creation of Index. If you are a datawarehouse environment you can still have the optimizer make use of the constraint for query rewite even if the index is not there. 4. Constraint declaration gives you lots of flexibility on when to kick in the check. For OLTP environment you can defer constraint checks depending on your transaction's needs (in 8i). There is gobs of information in the SQL ref manual on syntax and the options I have mentioned above. HTH - Sundeep === PS: I used to work for Wipro in 1987. I am wondering if the likes of Ashok Soota and Manoj Chug are still part of the company. You can send me email at my personal email address. Thanks --- Vishak [EMAIL PROTECTED] wrote: Hello Gurus, I would like to know whether creating an unique-index on a column or a unique constraint on a column(during creation of the table) would be fine. Which option would be better? TIA Regards Vishak -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vishak 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). = Sundeep Maini Consultant Currently on Assignement at Marshfield Clinic WI [EMAIL PROTECTED] __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sundeep maini 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: Script to Compare table structures
Download free version of TOAD from www.toadsoft.com. Once installed and logged into look under View---Schema Differences you can compare many aspects of two schemas (eg compare your test and production schemas). Table comparisons are one of the many things you can compare. HTH --- Johnson Poovathummoottil [EMAIL PROTECTED] wrote: Hi, I would appreciate if someone could pass me a script which compares table structures? Johnson __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/?.refer=text -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnson Poovathummoottil 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!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/?.refer=text -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sundeep maini 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).
