Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  • Users: sem
  • Order by date
  1. sem

    Simple Update Takes Hours

    BTW how what's your table average row length? Actually your statement needs approximately 1 GB in rollback segment at least, so it's not so simple as it looks like :). Regards, Dima http://seminihin.narod.ru
  2. sem

    Simple Update Takes Hours

    One more guess: locking Regards, Dima http://seminihin.narod.ru
  3. sem

    sequence in which queries run in the data model

    All queries run in turn and regardless on whether the results are to be printed or not. Regards, Dima http://seminihin.narod.ru
  4. sem

    How to Set Scheduler Job to Failed from Chain

    You may utilize Advanced Queuing and execute the real piece ow work only on receiving event from previous successfully completed job. The same way you may implement problem notification etc. Another (actually almost the same) idea is to use separate application for orchestrating your jobs. Or...
  5. sem

    Help Resolving ORA-12505: TNS:listener could not resolve SID given in connect descriptor

    Did you specify database alias? If so then most probably your profile contains information about TNS_ADMIN - a place where your tnsnames.ora locates. Thus without this file you're connecting by host name rather than tns alias. Accidentally connect string coincides with the host name where...
  6. sem

    Use index to avoid full table scan

    Oracle may utilize its INDEX FAST FULL SCAN method and read all fields directly from index not accessing table at all. But only in case your query uses only those 2 fields in index. Regards, Dima http://seminihin.narod.ru
  7. sem

    Oracle REP-51018 Need database user authentification

    DNS? TNS? $local? Regards, Dima http://seminihin.narod.ru
  8. sem

    Query by Function

    barab, is SYS_CONNECT_BY_PATH what you're looking for? Regards, Dima http://seminihin.narod.ru
  9. sem

    Date Formatting Discrepencies between Table and Query

    Alternatively to using explicit conversion you may alter default format mask on session (or even system) level by invoking alter session set nls_date_format='<whatever you need>' After such command you may rely on Oracle implicit conversion and be sure that data selected from date field and...
  10. sem

    How to get data from a LONG data type

    You may utilize old good DBMS_SQL package. It's a bit more cumbersome than already mentioned EXECUTE IMMEDIATE but also a bit more flexible. Regards, Dima http://seminihin.narod.ru
  11. sem

    Date Formatting Discrepencies between Table and Query

    Just a small elaboration (most probably needless but...) Oracle SQL and PL/SQL DATE internal representations slightly differ, so don't be confused by that dump(sysdate) and dump(<date column>) show different lengths and moreover different types Regards, Dima http://seminihin.narod.ru
  12. sem

    execute multiple views within a single stored procedure

    You may use CREATE SCHEMA statement Regards, Dima http://seminihin.narod.ru
  13. sem

    Mysterious Commits / log file syncs on Database

    What is your client application? If you use some middle tier, it's possible that after upgrade/migration/smth. else the autocommit parameter was set to "true" (actually the default behaviour for many systems) and commit is issued after each statement. Regards, Dima http://seminihin.narod.ru
  14. sem

    Need help with WHERE clause to exclude records

    I'd also recommend you to avoid NLS-specific format masks, as Dec is not the common name of 12-th month in any language ^) Regards, Dima http://seminihin.narod.ru
  15. sem

    Query Optimization

    +1. Oracle cost calculations are based on statistics and some initialization parameters describing hardware in used. E.g. optimizer_index_cost_adj, optimizer_index_caching. So the cost is based on some assumptions, not quite correct in some cases. Only tracing provides real numbers though only...
  16. sem

    Equivalent of diagrams in Oracle

    You may use Data Modeler from Oracle SQL Developer. It's both free and cross-platform. As a free bonus you'll obtain import from ERWin and MS XMLA Regards, Dima http://seminihin.narod.ru
  17. sem

    SQL*PLUS - no rows returned on one laptop, 67k+ rows returned on anoth

    In case NLS_LANGUAGE and NLS_SORT are not specified explicitly (in registry or as environment variables or in scripts mentioned by carp), they're derived from NLS_LANG. BTW is this ... and l.period_num >= '04' and l.period_num <= '04' ... not the same as ... l.period_num = '04' ... ...
  18. sem

    Check to see if field has a number

    Don't forget about translate() function, e.g. case when translate(I.INSADDR1, '.1234567890', '.') = I.INSADDR1 then TRIM(I.INSADDR1 || ' ' || I.INSNAME) else I.INSNAME end Using owa_pattern is more readable but less efficient. If you're lucky to be on...
  19. sem

    SQL*PLUS - no rows returned on one laptop, 67k+ rows returned on anoth

    Most such issues are "implicit type conversion" - related (character to number, character to date and vice versa). You may expect similar results in similar NLS environments (don't forget about collating sequence) but IMHO avoiding implicit conversions is a better way to get not just similar but...
  20. sem

    Delete script runs forever

    Another possible reason of slow delete may be some foreign key constraint, especially with non-indexed key. This is not for topicstarter but maybe for anybody else. Dave, you may not only delete but also update [key-preserved] queries. This is especially usefull on queries joining master-detal...

Part and Inventory Search

Back
Top