Skip to main content.

Job Scheduling < Architecture < Databases

Ask Tom MOST RECENT ARTICLES

These are the questions on Ask Tom that have the most comments

Ask Tom MOST RECENT ARTICLES

Need to select fields having only numeric data: Hi, I have a requirement to select only those fields which have numeric value and do not contain any characters SQL> desc priti Name Null? Type ----------------------------------------- -------- (...)
26 June 2009
dbms_metadata.get_ddl - is there a way to get 'dependent triggers' for tables?: Hi Tom, I need to write code to retrieve the DDL of our Production database. (Don't yell at me, I didn't design this application, I inherited it :-) I need to implement a version control mechanism now, and I need the DDL to create a baseline (...)
26 June 2009
Is PGA memory allocated from SGA ?: Hi Tom, Is PGA memory allocated from SGA? In my database SGA_MAX_SIZE is 1504M SQL> sho parameter sga_max_size NAME TYPE VALUE ----------- ----------------------...
26 June 2009
sys password change and orapwd file: Hi Tom, What is the relation between SYS user password and orapwd file ? What are the various methods to change the SYS user password. If I change the SYS user password using from sqlplus use password command or use alter user (...)
26 June 2009
Insert reading UNDO or looking for HWM?: Please see sequence of events below. SESSION 1: SQL> create table t (n number); Table created. SQL> insert into t (select rownum from dual connect by level <= 1000000); 1000000 rows created. SQL> commit; Commit complete. ...
26 June 2009
Parallel Insert and Serialization: Hi Tom, I learned a lot from your site. Now I have a question. We are trying to run multiple processes to insert into a same table with INSERT INTO... SELECT ..., with setting all selected two FROM tables and inserted into table as parallel, (...)
26 June 2009
Controlling cold backup jobs . When to run and when Not to?: Hi Tom, Good Morning. We have different ways of scheduling cold backups,like 1) We use RMAN and schedule jobs thru schedulers 2) Use SQL Back track and have maestro to schedule the jobs. 3) DBAs can also run cold backup job on need basis (...)
26 June 2009
Running Total: Hello Sir, Could you give a simple example of running total on emp table FOr example, running total of sum (sal + comm) department wise? thanks
26 June 2009
Table Space and DataFile: How can we recover the data files of system table space if no backup is available ?
26 June 2009
ASM and Redo Multiplexing: Hi Tom, If we are using ASM (and the data is externally protected (mirrored disks)) --- Should our redo multiplexing strategy change? Currently without ASM our strategy is to multiplex online and archived redo to the flash recovery area (one (...)
22 June 2009
Difference between automatic vs manual PGA setting: In Oracle 9.2.0, Can you please describe briefly the difference between PGA manual and automatic modes? Why oracle recommends Auto allocation?
22 June 2009
Primary key index in Partitioning !: Tom, I have a table accounts which has 80 million records (OLTP system). I would like to partition the table by acct_by_date column. I will be going with range partition and global indexes. My concern is regd the primary key acct_id. (...)
19 June 2009
Password Security During Logins: I understand that setting ORA_ENCRYPT_LOGIN = True in client machine will ensure that password will be sent in an encrypted form from client to server. Does ths works if we are using odbc to communicate between client and server?
17 June 2009
pinnign objects: 1. I have seen some high executions and i/os on some plsql packages. I am not caching any procedure in my sharedpool so far. So far my shared pool cache rations are very good neat to 100%. However these rations may not be realistsic. So what (...)
17 June 2009
temp tablespace: Tom: if i select count(0) from v$transaction and got 0, does that mean all content in temp tablespace is empty? mine is not empty, why? can i drop temp tablespace at any time during db is up? Regards,
15 June 2009
Table reorg: Tom, What's your recommendation to reorg a large table in 10g. We are seeing a pretty 'high' high water mark as compared to the number rows in a table. These tables involve lots of delete/insert statements during batch cycle and we're looking for (...)
12 June 2009
Data Grouping: Hi Tom Greeting, I have a table item_analysis as below. item_id frequency 1 3 2 19 3 6 4 8 5 1 ..... I would like to get a result set which would group item_id into two groups so that (...)
12 June 2009
Tablespace: Hello Sir Thanks for this useful site, I learned lots of think. While reading Joanathan Lewis book "Pratical oracle 8i" he mentioned about "resuing the tablespace name, since pmon wake up and scan the tablespace for reclaiming the space" (...)
12 June 2009
Performance issue with CLOB: Hello I am working on Oracle V8.1.7.4.0 and I have one performance issue with this table: CREATE TABLE C2W_MESSAGE_SDW ( CMD VARCHAR2(12), OLD_ID NUMBER, OLD_Y_MESSAGE_TYPE VARCHAR2(50), (...)
12 June 2009
Os Authentication :Eat the cake and have it too: Hi Tim, Thanx for all your help to the Oracle Community. Is it possible to have a DB user identified externally and also have a DB password so he/she can log both ways Authenticated from os as well as using account name and password (...)
12 June 2009
Comparing Package Dependencies: Tom, I would like to provide a utility/script to my development team that would allow them to compare package dependencies by using all_dependencies and then searching for a similar result string that might come from another package i.e. Here is (...)
11 June 2009
VPD: Tom, We have a requirement where in another group wants access some of our tables to look into some tables data. Our manager do want them to look into all the data and they should be only looking into the particular that they have to see. (...)
10 June 2009
Design - Business Logic: Enviornment: Pro*C and Oracle 9i Below logic is built in Pro*C Ours is credit card system. Each customer can hold more than 1 card. Card may get reissued or renewed.Based on customer deliquency we will assign bucket count for each card. Bucket (...)
10 June 2009
Changing a column from VARCHAR2 to CLOB: Hi Tom, Apologies if this has been answered elsewhere. I did do a search but didn't find any relevant information. I have a table that has been defined with a column of type VARCHAR2(4000). We are now looking to change this to a CLOB (...)
10 June 2009
Data Model related Question -- history information.: Hi Tom, In my latest project I have come across the following data model. All the application tables are modelled on similar lines. Eg, if we have a customer table with the following information Column Data_type ...
10 June 2009
OCI Transparent Application Failover: Hello Tom, I am writing OCI component for a mid-tier app server. I want to use failover/load balancing features provided by OCI. After going through OCI reference document, I understood that OCI TAF is enabled/configured in initial connect string. (...)
10 June 2009
execute already written Mysql query on the oracl: Hi I have successfully converted my MySql database in oracle. Now the problem is how to execute already written hundreds of Mysql query on the oracle. There are many syntax variation in Mysql query format which is not acceptable for oracle. (...)
10 June 2009
Using group function in merge statement: Hi Tom, Just wanted to ask that i want to use group function in merge statement. Is it posible because when i group function in values(col1,col2,count(*)...) statement returns error group function not allowed here. Please explain with example if (...)
10 June 2009
Get pervious value: This type of question comes up quite often in oracle forum. Below is the test script create table test(col_1 number, col_2 number,col_3 number); insert into test values (1,1,4); insert into test values (1,2,null); insert into (...)
10 June 2009
What difference between SQLJ,JSP(Java Stored Procedure) & PL/SQL Java: Hi Tom; I really want to understand what difference between SQLJ,JSP and PL?SQL with Java. Thanks
10 June 2009
samele for Oracle Streams in 10g: Hi Tom, Can u please provide the sample programs of Stresm in oracle 10g (using two database)I need the coding for how to create databse also.(Fully solved sample program in stem by step procedure) Thanks and Regards P.rajasekar
10 June 2009
Patch on 10.2.0.3 to 10.2.0.4: Tom, Recently i applied a patch on 10.2.0.3 to 10.2.0.4.(windows 2003 32 bit) The procedure followed to dwonload patch was, 1) login to metalink 2) Patches and updates 3) Simple search 4) Product/family (database family) 5) release---10.2...
10 June 2009
creating text files from the clob column in table: HI TOM, Thanks for the advice with the same. Yesterday I had told about my requirement of creating text files from the data stored in a clob column in one of the table. From the reference code in one of the questions i tried the following (...)
8 June 2009
Hints When to use: Hello, Wish you a Merry christmas and Happy holidays. 1)When tuning an sql statement in plsql when should Hints be applied ? How do we know what Hint to apply and when see point 2 please .We use CBO.We are doing fresh development. (...)
8 June 2009
Storing Date and Time in Separate fields Vs Using the same field: Hi Tom, My application uses separate fields for storing time components separately. There are calculations to compute the next available open session based on the particular time of the day. When the sessions (slots) gets filled up, the (...)
8 June 2009
UNDO RETENTION: Tom, Hope all is well. Sorry I'm on IE (bleah), was having problems with Netscape, opened up IE, and here I was with the chance to ask a question! Mozilla doesn't like our authentication server, so I can't use that at work. Anyway, to (...)
8 June 2009
CURRENT MODE AND consistent mode: hi tom i your discussion regarding consistant gets and db dblock gets you mention about block read in CURRENT MODE AND consistent mode Could you please explain exact meaning of this words by giving some examples? Thanks
5 June 2009
Database transfer from SQL Server to Oracle: 1. Before I go for Oracle database, I would like to know whether migrating the database(all objects and data as well) from SQL Server 7.0 to Oracle 8i is possible or not? If so how? 2. I would like to know how do I declare binary datatype? (...)
4 June 2009
ORA-16000 database link read only query fails: Dear Tom, We started up a database in read only mode,all users are able to run read only queries . One user who has a database link to another database cannot run a read only query and do a minus operation. The target database is (...)
3 June 2009
DB_FILE_MULTI_BLOCK_READ_COUNT According with SAME Metodology: Hi, I'm configuring my Database/Storage (HP-XP12K) and I want to do this by follow the SAME Metodology (Doc. "Optimal Storage Configuration Made Easy" by Juan Loaiza) to do this, but there is a part in the document that stablish that you need (...)
3 June 2009
Password File: Hi Tom ! Happy XMas and New Year 2002 to you. 1. At the time of creating a password file you specify the maximum number of distinct DBA and OPERs. But say already this step had been taken care and now I want to see in the exsisting (...)
2 June 2009
Padding a Long Variable: Tom- I've coded a procedure to export information from the Oracle Database to a text file using PL/SQL. It is necessary to Pad all the variables (LPAD Numbers with zeros, RPAD varchars with spaces) for the Mainframe system to read them (...)
29 May 2009
Show_Space Procedure Usage Problem: Hi Tom, I'm trying to use your procedure SHOW_SPACE and I'm encountering oracle error message saying "ORA-10618: Operation not allowed on this segment". I have tried to research on the error and the error states that "This DBMS_SPACE (...)
29 May 2009
sqlplus hangs and takes for ever to do this: Tom These days Iam facing a huge problem which is wasting a lot of mytime. I have this table t , which has a million records. For my work, I need to keep checking the number of total records , many number of times in a day. when I (...)
29 May 2009
DBMS_SCHEDULER: Emailing after inserting into database tables: Hello Tom, You provided an example for "Emailing after inserting into database tables."; I?m working with 10g. 10g documentation recommends using DBMS_SCHEDULER. The code you provided using DBMS_JOB is listed below. Would be possible (...)
28 May 2009
dbms_job: I am trying to queue a database job using dbms_job as following: declare jn binary_integer; begin dbms_job.SUBMIT(jn,'MYJOBS.MYPROCNAME(1234,''5678'');' ,sysdate + 1/1440); one minute delay, execute once only,delete upon (...)
28 May 2009
handling different datatypes in sql loader: Hi, I have a flat file which contains data that needs to be loaded into a table. In that table we have column with numeric data type.Previously we are getting numeric values from the flat file corresponding to the column in the table and it is (...)
28 May 2009
Materialized View: Hi Tom, I'm a regular visitor of your site and learns a lot through the different types of real time issues. Well, my application has lot of materialized views and we usually refresh them through jobs running during night. Some times (...)
27 May 2009
Virtual columns in Oracle 11g: hi tom what is virtual column in 11g. Why oracle has introduce it. Can you give us its possible usages. regards Amir Riaz
27 May 2009
Circular Dependency Problem.: Hi, Tom, How can we find Circular Dependency Problems in a schema. For example: we have two tables A and B. A has a FK to B and B has a FK to A. Thanks.
27 May 2009
Flashback Query: Tom, Can you please tell me if there is any performance penalty on the database by enabling Flashback query feature. Is it adivsable to enable this feature in a database where more than 100 transaction tables will undergo lots of DML (...)
27 May 2009
how do we retrieve LOBS from the database using JAVA interface: In our application we are storing images as BLOB's. By using DBMS_LOB package we were able to insert the image . Now how do we retrieve it from the database using JAVA interface . Also where to store the image in JAVA once retrieved from (...)
26 May 2009
Underlying mechanism of Exchanging the partitions: Dear Tom, I have a partitioned table with 9 partitions. Each partition is about 10Gig in size. Now, I also have set of 9 conversion tables(non partitioned tables ) for each partitions in the partitioned tables. The non-partitioned tables (...)
26 May 2009
internal - password: How to change the password of 'internal' ?
26 May 2009
Prevention and Detection of database corruption: Tom, I run a 24x7 OLTP database that's just cannot afford ANY data corruption. I am trying to proactively detect any possible data integrity/corruption issues before it hits our clients. Besides DBVerify and ANALYZE...VALIDATE STRUCTURE for (...)
26 May 2009
UTL_SMTP - Package: Hi I am having Oracle 8.1.5.0.0 Version Installed in our Solaris machine, I want to use UTL_SMTP package. Where can I get the package is any download is available for this package. Please let me how to install the UTL_SMTP package into my (...)
25 May 2009
Type 2 Dimension: Hello Tom, We have data warehouse. On daily basis we are planning to receive one file 3Gb size and approximate 3 millions records. The problem the file from source contains same record daily, some of the accounts can be added and some of (...)
25 May 2009
password: Hi tom i am vote yes to your new police it is beter to give only one up to three questions . my question: is i create a user and password to connect to the database i well tell the user about the password belong i need to force him to (...)
16 May 2009
Export/import moving data between different RDBMS versions: Dear Tom, we have different Oracle RDBMS version - production, testing, developing. In this situation we use only Oracle 7.3.4 features, nothing extra. We would like to move data between Oracle 7.3.4, Oracle 8.0.5 and Oracel 8.1.6R2. How do (...)
16 May 2009
CTAS with Cluster: I have a cluster that I am trying to to a CTAS into. CREATE CLUSTER cl_dept_no ( dept_no number(2) ) tablespace users size 256 hashkeys 20 STORAGE (INITIAL 250K NEXT 50K MINEXTENTS 1 MAXEXTENTS 3 (...)
15 May 2009
Sqlloader direct or conventional: We use to get a text file , and update the table through SQLLOADER. That has approx 800,000 records. We use direct method which is faster. The table is having the following columns USER_ID VARCHAR2 MODEL VARCHAR2 (...)
15 May 2009
Needed locking when not using sequences: Hi Tom, I'm aware of the many advantages in using sequences and would like to improve my understanding (and maybe add some more arguments for future discussions) of the serialization issues when not using a sequence. As an example, I use a (...)
15 May 2009
Job Interval Limit: Hi Tom, I was trying to 'help out' on OTN, inspired by this example from you: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:388480262167#1545342000346301875 I came up with some 'solution' but it exceeds the limit of (...)
15 May 2009
Logging difference between Microsoft and Oracle: Hi Tom, working with Oracle I learned that DDL statements will not be logged and so you cannot rollback them. For example a truncate table statement cannot be rolled back. The pro, I thought, would be better performance. Now, working in a (...)
15 May 2009
Database Stability: Hi Tom, Good Morning. I saw a system stability excel sheet recently from QA perspective. QA team prepares a system stability document by considering several factors, like PRI1 defects, Priority 2 defects, system outage,etc. This document is (...)
15 May 2009
Relation between explain plan (row source), and longops: I was watching a long running query, and I realized that either I've forgotten, or didn't know, how to correlate an "execution plan" with "actual execution". For example, in longops, I saw info like this: Sort Output: : 19741 out of (...)
15 May 2009
How much: Hi Tom, I have 5 databases running on HPUX box having 16GB Ram How to find the total memory consumed by each of these Databases From the 1) OS ( may be some ps -ef dont know ) 2) Database ( I did show sga (...)
14 May 2009
shoud I use char or varchar2: I have a database column that shall store data always 10 characters long. Which datatype should I use for it CHAR or VARCHAR2 and What is the specific reason for using that datatype ?
13 May 2009
Self-Join: Hi Tom, Which is more efficient, query with self-join or the same query with subquery instead of a self join ? Thanks John.
13 May 2009
Lots of archivelog generation when shrinking and compacting segments: Hi Tom, 1. what is the reason of huge redo and archivelog generation when compacting and shriking huge segments in 10g? 2. How it can be avoided or minimized? Thanks JP
13 May 2009
One tablespace per schema?: Hi tom, We are migrating 60 clients from mysql database to orcale 10g. In mysql, a client is one schema which is saved on one physical datafile. When we migrate to oracle, is it advisable to also create the client schemas into separate (...)
13 May 2009
Compare columns in two tables and report which column is different: Compare columns in two tables and list out column names which are different. for ex:- create table t1(c1 number(2), c2 varchar2(10)); create table t2(c1 number(2), c2 varchar2(10)); insert into t1 values(1,'a'); insert into t2 values(1,'b'); result (...)
13 May 2009
Authentication on the Host Server: Hello Tom, I telnet to host Oracle server installed on HPux as oracle os user ( which is the Oracle software owner ). I do the following $sqlplus '/as sysdba' It gets connected Q1) What authentication method was used here. I have (...)
13 May 2009
How to automate the creation of the private synonyms based on the granted role: Hi Tom, To provide access on specific data only to a certain role, we think of creating views and using the private synonyms. As the private synonyms are for users not roles, We need to automate the creation of these private synonyms when a (...)
13 May 2009
PGA memory versus TEMP space versus LARGE_POOL: This is not a specific test-case question... but really more of a general database architecture question. I'm trying to get a handle on what kinds of operations the database does in TEMP space versus what is does in the PGA memory. A consistent (...)
13 May 2009
Pivot query with unknown number of columns: Create table emp(eno integer,location_id integer ); Create table location( location_id integer location_name varchar2(255)); insert into location(1,'New York'); insert into location(2,'Los Angeles'); insert into location(3,'Chicago'); (...)
13 May 2009
External table field delimiter: Hi Tom, Why do the records that do not have value for FLG get rejected? Cheers, Ganesh DROP TABLE LNK_NUMRECS CASCADE CONSTRAINTS; CREATE TABLE LNK_NUMRECS ( TNAME VARCHAR2(30 CHAR), NUMRECS NUMBER, FLG VARCHAR2(1 (...)
13 May 2009
pl/sql table based on a record type and a pl/sql table: TYPE t_hdr_rec_type IS RECORD ( party_intf_id xx_cust_intf_in_dat.party_intf_id%TYPE, customer_name xx_cust_intf_in_dat.customer_name%TYPE created_by xx_cust_intf_in_dat.created_by%TYPE, (...)
13 May 2009
ANDV calculation: Dear Tom, Thank you for reading this post (Thank God, I've finally able to post this question after longing for months) I've been having my eyes on the special ability Oracle has that's called Index Skip Scanning. I'm very interested in a (...)
13 May 2009
How to trace a Java Web application through a connection pool - DBMS_APPLICATION_INFO: Hello, I need improve the traceability in our Java Application that usually run on fixed users. The DBA should have a fast access for the information about the heavy users that are degrading the database. 5 years ago, I implemented a .NET ORM (...)
7 May 2009
"Dynamic" Sequence Generation: Hi Tom, how are you doing? Thanks a bunch for the great site and years of learning / entertainment it's provided :) First, some context for my question. I'm a consultant working at a client site (development) and as such i get to consult a (...)
7 May 2009
Index size increased after IMPORT: <code>Hi, I exported a big database and imported it into another. On a very big table (190 Million records), there are 3 indexes. Index size before export was 13 GB. But after import into new database, index size has increased to 20 GB. (...)
4 May 2009
Is password in 11G case sensitive ?: Have just downloaded and installed 11.1. imported by 10g2 database and started to run my forms application My sql reports started failing - invalid username/password running under Vista C:\Users\ian>sqlplus CHEST/norma@m SQL*Plus: (...)
2 May 2009
Any drawbacks to using RMAN?: <code>We will be migrating to 9.2 in the near future from 8.1.7 for a data warehouse. We are having a debate amongst ourselves about using RMAN when we go to the new platform. One camp is all for it, another is leery because someone had a (...)
2 May 2009
Insert space in a string after x characters: Hi Tom, In Oracle 9i, is there a way to insert spaces after x number of characters in a single select statement? For the example below, I need to insert a space after every 3rd character - Input String: testin123fortom Output String (...)
2 May 2009
Materialized View: <code>Sir, I have one doubt regarding Materialized view & Snapshot. Is there any difference between them?. Which is preferable & why. Thanks in advance. Regards Manoj </code>
1 May 2009
Oracle Text search on view (local & remote table) errors.: Hi Tom, I am trying to use Oracle Text search on a view which is built using Local and Remote table and consistently get this error. Does Oracle text doesn't work with views built using a local and remote table? The firstname column on both (...)
1 May 2009
Data Models: <code>Hi Tom, I love your site. We're developing a new application, and the data architects have created an ER diagram for it. After looking at it, it appears to be a very generic data model (similar to what you have in your book where (...)
1 May 2009
How to create outlines for SQL's that look identical but refer to different objects,...: Hi Tom, I'm working with a database that has two schemas with the same tables (same structure and name but with different rows of data and obviously different owners). The application is split into 2 sets of processes that each runs the same set (...)
1 May 2009
DBMS_JOB -- how to pass parameters to the job: <code>Hi Tom I have one procedure execute_job with two in parameters (batchnumber and loaddate). Procedure as follows :- create or replace procedure execute_job(batchnumber in number, Loaddate in varchar2) is (...)
1 May 2009
E-mail utl_smtp: Boa tarde Tom, sou do Brasil e trabalho como desenvolvedor pl/sql, vou direto ao assunto: tenho uma procedure que envia E-MAILS funcionando perfeitamente, so que quero que ela envie esse e-mail formatado como HTML (por exemplo negrito, italico, (...)
30 April 2009
ctx_doc.policy_filter package: I have a word document .rtf that i want to convert to HTML and plain text for my oracle text search. I tried this code that i found in your site but i'm getting error. <b>--I have created the preference</b>exec ctx_ddl.create_preference('test_i...
30 April 2009
partitioned tables and tablespaces on ASM: Hi Tom, I have a 2 node 11g RAC with ASM, running on an EMC Clarion SAN, Unbreakable Linux 5. One of our developers would like me to create a separate tablespace for each partition in a partitioned table. He thinks that the reading of file (...)
30 April 2009
Upgrade from 8i to 10g - Partitioning option licensing: Hi Tom, Few years ago we bought Oracle 8i EE DB licenses and we have been paying support for it. Meanwhile, we migrate our database to version 10g. Do we have to buy Oracle Partitioning for new DB now, or it is inherited from 8i licenses? With (...)
30 April 2009
Oracle Streams: Hi Tom, I need to upgrade the database version 9.2.0.1 to 9.2.0.5 I am going through the docs. The docs refers the use patchset 9.2.0.5 . will I have use opatch utility to use this patch. As database is already present in 9.2.0.1 oracle home so i (...)
30 April 2009
Partitioning in OLTP: Hi Tom, In our OLTP system the transactions are being logged into several hundred tables that have grown over the years to contain over 100 million rows. We want to implement partitioning. And the question is how do we partition. The tables (...)
30 April 2009
Get free disk space in PL/SQL: Hello Tom, I'm currently searching the best way to get used and free disk space of a file system from "inside Oracle" (i.e. in PL/SQL or Java stored procedure). I think Java may be the best way to do it however, I wonder if there is something (...)
30 April 2009
Like Operator with IN clause: I have to compare more than one Patter using LIKE operator Say I want to prepare query SELECT name FROM employee WHERE name <b>LIKE IN</b> ('sasho','shashi%','rags')
30 April 2009
Index range scan: Hi Tom A question regarding Index range scan, what happens when the index range scan happens? i know that we read the ROOT level then the branches then we reach the leaf (witch one ?, the lowest value ?, the point where the scan begins ?) then (...)
29 April 2009
db_file_multiblock_read_count: Hi Tom, I have received a recommendation from a fellow analyst that with 10g it is better not to set the parameter db_file_multiblock_read_count and let oracle determine the best value for the number of blocks read in multi-block I/O operations. (...)
29 April 2009
Making a genric search sql Query: Hi Tom, I have a web application connecting to a Oracle DB. I have a search screen where user can enter any search criterion and submit the information. The information is collected and passed to an Oracle Stored Proc. where a dynamic query is (...)
28 April 2009
Evaluation of expressions: <code>Does Oracle perform short-circuit boolean evaluation when evaluating expressions? For example, consider the following part of a query: select lcs_id, loan_id, client_id, security_id, masterfund_id, category, calc_lvr, (...)
28 April 2009
Uncommitted transactions in Data Files and Redo Log files: Hi Tom First of all I want to inform you that, I love you very much and I have a big respect to you I have a question related uncommitted transactions. I know that while we issue COMMIT, LGWR writes committed and uncommitted transactions from (...)
28 April 2009
Transfer Steps of ETL Processes, From an IBM Mainframe to an Oracle Server: Tom, I'm looking for a way to decrease the total run time for a set of ETL processes that, by one measure, operate on about 60 GB of information in each, notionally daily, processing cycle. The source system is a billing application on an IBM (...)
28 April 2009
Correlation between MAXLOGHISTORY & CONTROL_FILE_RECORD_KEEP_TIME: <code>Hi,Iam really confused after going through numureous documents on these parameters.I have some doubts to be clarified : 1)What is significance of MAXLOGHISTORY parameter in non-OPS environments. 2)If Control_file_record_keep_time=0,then (...)
28 April 2009
How Oracle Works: Tom, I have always been amazed/fascinated with Oracle, and how insanely awesome it was designed and works. I have often been curious, what language(s) are used to create Oracle at the lowest levels and up (and over time has the base (...)
28 April 2009
IMPLICIT CURSOR ATTRIBUTE SQL%NOTFOUND NOT WORKING: <code>Hi Tom, I am using implicit cursor attributes SQL%NOTFOUND for the below PL/SQL block. But it raises NO_DATA_FOUND exception. It works fine for update and delete statement as they are not going to raise the NO_DATA_FOUND (...)
27 April 2009
Pl/SQL ETL: <code>Tom, Currently we use the following structure for our ETL , Its taking too long Cursor for Delta table Loop Insert into dimension tables vaues ( cursor variables ) insert into xref tables (...)
24 April 2009
Surrogate versus Natural Keys: Hi Tom, The project I'm currently working on has the DB design rule that all tables must have a surrogate key. This is the case even if a perfectly good natural key exists. The primary motivation appears to be that of join efficiency by (...)
22 April 2009
SQL*Loader and tab delimited input data: <code>I have an input file that contains data that is tab delimited containing info like firstname, lastname, city, state, zip. The fields are not enclosed in quotes or anything else. The problem arises when data for a field (like lastname) is (...)
22 April 2009
IP address: <code>I have got an Oracle version 8.1.1 I'd like to know how you get the IP address from users when they have already logged on the Database. Would you, please, send me infomations about versions older than 8.i? Thanhs beforehand. (...)
22 April 2009
When no data definition change, is SYS/SYSTEM stats recollection needed?: <code>Tom, After all schema contents (table, index, proc, triggers..) are created in our 10.1.0.4 database, we collect statistics for SYS/SYSTEM and all other users schema. Then the database is rolled over to production. In production, there (...)
21 April 2009
pl/sql cursor cache changes in 9.2.0.5: <code>Hi Tom, from the 9.2.0.5 patchset release notes: "PL/SQL Cursor Caching Prior to release of the 9.2.0.5.0 patch set, the maximum number of cursors that could be cached for fast lookup by PL/SQL was bounded by the value of the init.ora (...)
21 April 2009
Merge statement: <code>Hi Tom, consider this: create table test ( id number(10), username varchar2(100), reason varchar2(100), timestamp_ number(10), cnt number(10), CONSTRAINT test_pk primary key (id) ); insert into test(id,username,reason,timesta...
21 April 2009
Execute to parse count in statspack: STATSPACK report for DB Name DB Id Instance Inst Num Release Cluster Host ----------- ------------ -------- ----------- ------- ------------ PSCP 564549865 pscp 1 9.2.0.8.0 NO (...)
20 April 2009
interpretation of tkprof: Hi Tom, Please help ! Neil I am having a problem interpreting a tkprof report. I have looked in detail at a lot of documentation on this area, particularly looking at "pitfalls" to be wary of and also a lengthy blog of yours. However I (...)
20 April 2009
Detecting dead sessions: <code>Hi Tom, Thanks very much for the examples on BIND variables.I have now understood the importance of these variables. Can you please send me some stuff or a link towards some good pages which can explain jow we can detect dead (...)
17 April 2009
PCI Compliance and DBMS_CRYPTO Key Management: Hi Tom, We are doing analysis for PCI DSS requirements and gaps. One requirement is that we have to store some of our data in 'unreadable' (encrypted) format. I am looking into DBMS_CRYPTO to accomplish this. One of the points in the PCI DSS (...)
17 April 2009
When is it safe to compress archive log files?: <code>Hi Tom, Periodically I need to compress my archived log files to save space. My script compresses all the files except the last one (the one with the highest sequence number). My assumption is that ARCH might still be working on that last (...)
17 April 2009
Truncate - Parent Child Partitioned Tables: Hi Tom, Hope you are doing great. My question is regarding truncating a Parent Child partitioned table. 1. I have a partition table say X which is a parent table. 2. I have a partition table say Y which is a child table whose parent is table (...)
17 April 2009
Oracle and .NET: Hello Tom, I've been working with Oracle for a while but only recently have I started developing applications on the .NET framework (VisualStudio 2005 and C#, by now). I'm writing this e-mail to ask for advice: I've already browsed the .NET (...)
17 April 2009
Tablespace , DF configuration in RAC: Hi Tom, I am look for some constructive advice for tablespace/datafile configurations in Oracle 10.2.0.3 RAC database. We have a fairly large database in 8.1.6.3 version, and as such it has various tablespaces along the line of small_size (...)
17 April 2009
SQL Query: Multiple references to a single table using alias: Tom, create table date_dim(date_id integer primary key,actual_dt date ,mth_mm integer,quarter integer,year_month integer); insert into date_dim values(2952,'30-jan-08',1,1,200801); insert into date_dim values(2981,'28-feb-08',2,1,200802); (...)
17 April 2009
Scalar Subquery as mentioned in "Effective Oracle By Design": Hello Tom, Yesterday, I was reading (again) your book "Effective Oracle By Design". While reading about Scalar Subqueries, I came across the following example that showed how an outer join can be rewritten as scalar subquery. <code> SELECT (...)
17 April 2009
ORA-22818: subquery expressions not allowed here.: <code>Hi Tom I have the follwoing query when i run this query i get this error Need help on the same ORA-22818: subquery expressions not allowed here. SELECT B.Vendorcat, A.vendorid, A.VendorFIRST_NM||' ' ||A.Vendor_MI_NM||' ' (...)
17 April 2009
running OS commands from DB - Some work others do not: <code>Hi. I followed the examples from the recent thread on the issue and have found limited success. I can get the java to run commands such as mkdir, rmdir and ls, but when trying to execute a shell script or create a file via "echo hello >> (...)
17 April 2009
how to implement a bitmap index design to oracle: how to implement a bitmap index design to oracle 9i ?, since i have design on paper but i dont know how to implement it in oracle?
17 April 2009
Mounting ASM Diskgroup to different host - no RMAN: Hi I have come across requirement of moving database to new machine in different datacenter. If ASM truely represent VM, it should allow to mount diskgroup with same LUNs as long as they are provision correctly to new host. I am trying to find (...)
17 April 2009
Different plan when using Bind vs. using literal: Hi Tom , 10g version. If Bind peeking is disabled is there an option that the optimizer will generate a different plan for the same statment when using binds vs. using literal ? If yes could you please explain why ? Thanks Yoav
17 April 2009
Table export: Hi, I want to export ra_customer_trx_lines_all table in its full into another table and take it to test server, since the DBF file associated to the table in the test server is corrupt and i am not able to shutdown the production due to work load (...)
17 April 2009
Can partitioning improve Index Range Scans: Hi, I have a transaction table that has 350 million rows in it. It is used by an OLTP app and has batch processing occuring against it. It is index ranged scanned by year and transaction_date by it's main queries. The main queries would be (...)
17 April 2009
Open a REF CURSOR for a clooection?: I have a function that returns a collection of rows. I need to return the results to a client application (PowerBuilder). To do this, I understand I must return a REF CURSOR. How can I convert the collection to a ref cursor? Thanks, (...)
16 April 2009
Combination of Locally Managed and Dictionary Managed TS: <code>In a Oracle 9i Database, we are planning to have a Database with a combination of Locally Managed and Dictionary managed tablespaces. Is this possible? The basic idea is that the Master and Transaction Tables will be dictionary managed. (...)
16 April 2009
Character Length Semantics: <code>Hi Tom, I have a new vended application that requires to set NLS_LENGTH_SEMANTICS=CHAR. I am planning on putting it on an existing instance which uses the default value of bytes. Can you please clarify the following: 1. Does changing (...)
16 April 2009
database vs instances: Tom, I am little confused about Oracle Database and instances. If I have created a database DB1, and started it,open for all users, does it mean it is one instance? How can I have multiple instances of a database and how do I find what Instance (...)
16 April 2009
Parallel Index: Hi Tom: Hi Tom: Thanks in advance for your assistnce! How would I turn "default" parallelism off on a partioned index: TABLE_NAME INDEX_NAME PARTITIONED TABLE_OWNE STATUS LAST_ANALYZED DEGREE (...)
16 April 2009
Database Size Reduction: We have a PeopleSoft HRMS system. We normally take a copy of the production every 2 weeks and create our Dev, QA and Performance environment. Our QA Environment does not need the entire data that is available in the Production. Our QA analyst (...)
15 April 2009
DIFFERENCE BETWEEN ANALYZE AND DBMS_STATS: DIFFERENCE BETWEEN ANALYZE AND DBMS_STATS
15 April 2009
Native Compilation without a Third-Party C Compiler: on http://download.oracle.com/docs/cd/B28359_01/server.111/b28279/chapter1.htm#FEATURENO07407 , an interesting new PL/SQL-feature is mentioned. a. So, on some platforms, there is an integrated C-Compiler for this platform in the database (...)
15 April 2009
KEEP Buffer Pool: Hi Tom My question is related to sizing the KEEP buffer pool. The reason for my question is that, in my production database there are lots of full table scans are happening and that too these table segments are of very small in nature usually (...)
14 April 2009
date calculation..: <code>hai, I have a basic question. I am having a table like... idno number(3) name varchar2(15) .. .. DOB date; In the above table data is already loaded. While on form(query mode) I have to calculate the age of customer to till (...)
14 April 2009
any performance hit using sequence with nocache in OLTP: <code>I would appreciate if you please let us know -- if there is any performace hit in OLTP using sequence with option "nocache"?? Thanks </code>
13 April 2009
Backup and Recovery -- why extra redo is generated.: Why is excessive redo generated during an Online/Hot Backup
13 April 2009
automate the creation of external tables: <code>In the past, I've used unix shell scripts to perform the following in sequence 1.browse through unix directory for listing of files matching a pattern 2. In a loop, supply each matching file name to a shell script which will call a (...)
13 April 2009
Your thoughts on oracle.: Tom, Could you please share your thoughts on recently announced Oracle VM? There has been a very little positive feedback (on blogs online, at least) on the announcement. As you have talked about and recommended VMWare products (especially (...)
13 April 2009
killing a session: <code>How can I disconnect a particular user ? I try oradim ?shutdown at command line. It shows me no error but doesnot disconnect user. If I have multiple databases running how can I disconnect or kill a particular session of particular database. (...)
13 April 2009
Closing Ref Cursors: <code>I know from experience that you should always close cursors. Does this apply to ref cursors? I find that if I close ref_cursors I loose my results set. It does not seem to matter where I put the close cursor statement. Is there a (...)
13 April 2009
HTTP request: <code>Can I post a XML document to a web server via the http request from Oracle 8i? </code>
7 April 2009
HAVING clause of GROUP BY.: <code>Hi Tom, Recently I came across a query with the following text: SELECT bbl1.*, COUNT(*) DupeCount FROM branch_book_list bbl1 WHERE bbl1.branch_name = 'Branch A' GROUP BY bbl1.branch_name, bbl1.book_ISBN, bbl1.book_name HAVING NOT (...)
4 April 2009
Cursor with parameter: <code>Running time of following cursor is 1 second: FOR REK IN (SELECT CA.DISTINCT CUSTOMER_ID, DN.DN_NUM FROM CONTRACT_ALL CA, CONTR_SERVICES_CAP CS, DIRECTORY_NUMBER DN WHERE (DN.DN_NUM LIKE '7032347%') AND (CA.CO_ID=CS.CO_ID) AND (...)
3 April 2009
Checkpoint&logswitch -- what is their relationship.: <code>Please Tom can you help me to understand what happens at the log switch time and what happens at checkpoint time and how one event can determine the other one. Also i am very confuse where SCN is written: in datafile header, in rolback (...)
3 April 2009
How to calculate two dimentional array in PL/SQL: Hi Mr Tom, I have a table, named tab1, having 3 columns: tab1(c1 varchar2(20), c2 number(1), c2 number(1)). For test, I inserted 2 rows as follows insert into tab1 values ('A1',3,9); insert into tab1 values ('A2',4,5); c1, c2, c3 (...)
3 April 2009
noresetlogs: <code>hello tom I don't understand why after recovering the database using a backup control file, the database must be open with resetlogs options? And in what other ocasion i must utilises this option? thanks a lot, razvan (...)
3 April 2009
Using XML to transfer data from SQL Server to Oracle: <code>Hi Tom, I have a table on SQL Server that I want to move to Oracle every five minutes or so. My thought was to use SQL Server to create a flat file and use sqlloader to load the data in the Oracle table. I was asked to look into using XML, (...)
2 April 2009
Raw Device vs File System: Hi Tom, I would just like to ask, how true that you can gain performance when you are using Raw Device instead of a File System. Regards, ANTON
2 April 2009
cpu count question: <code>I was reading </code> http://www.dba-oracle.com/oracle_tips_cpu_count_wrong.htm <code>and was wondering if this is really a bug? Is it really dangerous? </code>
2 April 2009
Parallel Query concerns: <code>Tom We have 10 cpu's on our production server, we have the following concerns, and missing pieces of knowledge before we start using parallel query on some select tables.(I have got the go ahead for using PQ ,but only after doing the (...)
2 April 2009
SCN: Hi Tom,can u plz help me on this what is SCN,why it is used? what is checkpoint?is checkpoint is related to SCN? when database has no transactions going on,when i do checkpoint using alter system checkpoint; why checkpoint number get (...)
2 April 2009
Read Write Access to External server from PL/SQL: Dear TOM I am in a great delima PLEASE HELP, I got a webservice which resides in a windows based server, this service I can invoke from the browser. There is a mandatory parameter where i need to pass on a path and file name for the webservice to (...)
1 April 2009
Dump Pivoted query data into a CSV: Hi Tom, I read a lot about pivoting queries and was able to use your example to create dynamic columns from rows. My question is how to use the refcursor (that created the dynamic columns) to be passed in DUMP_CSV (used the code from your (...)
1 April 2009
dbms_lock question: Hi Tom, In one of our third party application i found the following code: replace procedure wait_for_run_completion is v_cnt varchar2(100) :='7'; begin while v_cnt>0 loop select Count(flowchart) into v_cnt from (...)
1 April 2009
ORA-06502: PL/SQL: numeric or value error: character string buffer too: Hi Tom, I have a package which concatenates the data and inserts into a file. I am getting the error "ORA-06502: PL/SQL: numeric or value error: character string buffer too" while executing the package. Please help me.. Thanks in advance. I (...)
1 April 2009
DBMS_Job - Timed, but with Wait?: Hi, I have a job, which runs at an interval of 1 min. <code> EXEC DBMS_JOB.isubmit (job => 99,what => 'log_mig_proc;',next_date => SYSDATE,interval => 'SYSDATE + (1/24)/60 /* 1 Min */'); </code> There is this really good chance that the (...)
1 April 2009
power function: i try to query following. It is given error numeric overflow, is this a bug in oracle or else because when i try to this formula in excel its working. please tell the reason. why its given a following error. I also ask this to our support vendor but (...)
1 April 2009
how to show image from a long field in oracle with c#: how to show image from a long field in oracle with c#
1 April 2009
Jobs_queue_processes parameter and its impact: On one of our test databases(9i) when we set jobs_que_processes to 20, the CPU goes to 100% hanging all applications. Previous to this, the parameter was set to 0 by DBA when they found about 700 jobs running, they set this parameter to 0 after (...)
1 April 2009
Multi Master Replication and privilege: Hello! I`d like to create MMR situation : Production database - Testing databse (only select by programmers but on actualy data) I think best solution for my situation is to create MMR(Multi Master Replication) - I will create on those 2 (...)
1 April 2009
ANSI SQL - to use or not to use: Hi Tom, I have seen lots of information on the internet etc about the usage of ANSI SQL, however on this website I see you saying something like 'don't use the natural join feature'. Would you recommend not using the ANSI SQL at all, and (...)
1 April 2009
SQL tuning: Hi Tom, We are trying to execute simple function which has parametrized. We may pass parameter value as A or as B. If we pass the value as A, it replies in 3.97 seconds and for 6607 rows. The same function calls with the value B, it takes 6.69 (...)
1 April 2009
execute to parse ratio: Hi Tom, I have very big confusion regarding execute to parse ratio in statspack. i have observed this ratio in my statspack as 12.02. is it really bad? we are suggesting the parameter change (cusor_sharing=similar) to improve this ratio. is it (...)
1 April 2009
Drawbacks of queries having many "selects" within the main Select clause: Hello Tom, I have to tune some queries that are written as shown in the example below: select (select ....) as "col1", col_2, col_3, (select ....) as "col_4", (select ....) as "col_5", from (select (select...), (...)
31 March 2009
A running sequennce.: <code> I have a bit of a poser.. create table temp_mich ( mydate date, mytime varchar2(4), job_type varchar2(4), empid number ) insert into temp_mich values('24-mar-09','0830','CAP1',1234); insert into temp_mich values('24-mar-09','08...
31 March 2009
Finding latest DML time on a table: Hi TOM, Can you specify the correct way to find the latest time a DML was commited on a table? I have looked at DBA_TAB_modifications and orarow_scn per table as guide. These do not address accurately this quest. Please advice. Thanks (...)
31 March 2009
Is The Relational Database Doomed?: Hi Tom, I would like to read your opinion in regard to: http://hinchcliffe.org/archive/2009/03/17/16712.aspx Non-relational databases. Tony Bain over at Read/Write Web recently asked "Is The Relational Database Doomed?" While it's far too (...)
31 March 2009
table(cast(...)) performance tunning question: <code>I'm using table(cast(..)) feature to create a view by calling a PL/SQL package. But I had a performance problem which is caused by calling the PL/SQL package too many time. Here is my view's sample definition: CREATE OR REPLACE VIEW (...)
30 March 2009
Fetching last record from a table: <code>I was wondering if there is some possible way that I can fetch the last record from a table. In this particular case, obviously one is unaware of the number of records in a table. All I want is to write a query whcih will simply fetch all the (...)
30 March 2009
About latch wait time: Hi ,Tom! I read oracle doc ,and it said latch is a kind of thing which does wait for the lock. But when I read statspack ,I found the latch free wait time on top 5,what deos it mean? Thanks! Regards Alan
30 March 2009
sql trace in a session: <code>Hi Tom, When I set sql trace in a session, I get error: SQL> execute dbms_system.set_sql_trace_in_session('11','54',TRUE); BEGIN dbms_system.set_sql_trace_in_session('11','54',TRUE); END; * ERROR at line 1: ORA-06550: line 1, (...)
29 March 2009
Pipelined functions: <code>Hi Tom Can you please give an example of using pipelined functions in a *package*. I am not able to make it run within a package. I want to return an array of records from the pipelined function, say like name_1, address_1, city_1, hash (...)
29 March 2009
What is a SID, how to change it, how to find out what it is.: <code>Hi Tom, A trivia question for you, what is SID and what is it used for? Would changing it have any impact on the different instances of the database? And lastly, where can I find out the SID of my database? I don't seem to able to find (...)
29 March 2009
High-Water Marks: <code>What is High-Water Marks ? What is it used for ?? If I delete all 10000 records from a table, it will talk a long time if i submit a count(*) ?? Can you advise some web-site for DBA ? (other than Oracle.com) Thanks a lot (...)
29 March 2009
Create/Replace procedure command: <code> Hi Tom, Are there any issues with issuing a "create or replace procedure" command when the application that uses the database is up and running in a production environment ? What happens when you fire this script when this same (...)
26 March 2009
How to suppress ORA-00604: error occurred at recursive SQL level 1: <code>Hi Tom, Could you tell me how to suppress "ORA-00604: error occurred at recursive SQL level 1" when I raise application error using "RAISE_APPLICATION_ERROR". I am using it in after logon trigger and restricting few users. I am displaying (...)
24 March 2009
Append Hint: <code>Hi Tom I have read about the append hint that when its used with the Insert stmt it takes more space than a usual Insert would. In our case we have a batch process that first truncates tables and then does Inserts with the Append hint. In (...)
24 March 2009
ORA_ROWSCN strange behavior: Hello Tom, Lucky enough to get to submit a new question.. On ORA_ROWSCN, when I update a row, it is expected that ORA_ROWSCN number for that particular row is changed. But I noticed this strange behavior...Could you please explain this.. (...)
24 March 2009
Solid state disks: I recently tested an Oracle database on a Solid state disk. Here is the link http://www.superssd.com/products/ramsan-400/ The performance is really great if you compare it with regular disks. I couldn't find many documents or threads to (...)
24 March 2009
PGA/UGA Memory: Tom, I not using MTS but when I run the following query why do I get such a high value for UGA mem. max usage ? select name, sum(value/1024) "Value - KB" from v$statname n, v$session s, v$sesstat t where s.sid=t.sid and (...)
24 March 2009
Import and export between dbs with different character sets: <code>I have a db with us7ascii character set, in this db I can't store characters like ?. For example the word "ni?o" is stored like ni?o. I have another db with WE8ISO8859P1 character set (oracle 8.1.5 enterprise edition on NT). I want to Know if (...)
24 March 2009
How to handle ORA-01422 exact fetch returns more ....: Hi Tom I'm trying to execute SQL statment but the result is : ORA-01422 exact fetch returns more than requested number of rows Cause: More rows were returned from an exact fetch than specified. Action: (...)
24 March 2009
Number of joins in a query.: Hi Tom, This is a theoretical question really. There?s a bit of a discussion going around about how many tables should be included in a single sql statement. In the past I have read ?if it can be written in SQL, then write it in SQL?, and have (...)
23 March 2009
Passing Parameters from a WEB page to PL/SQL: <code>Hi Tom, Thanks for reading this e-mail. I'm new at programming in PL/SQL and have a couple of design questions for you. I have some forms that I'm working on for a future WEB site of my school. I'm hoping to use JavaScript with some of (...)
19 March 2009
SQL problem [ORA-01722: invalid number]: <code>Hi Tom, Could you please help me with the below SQL. I have 2 users "dev" and "admin", both are in same database. DEV>DESC TB_CMA086_US_CITY Name Null? Type (...)
19 March 2009
objects: <code>Tom If I want to know what are the object_name, and there synonym name , for all the objects to which I have access , how should I find out. How should I find out the list of objects, there owner for all the objects I have access to. (...)
19 March 2009
PLS-123 error: <code>Hi Tom, Could you tell me more about the program too large error (PLS-00123) ? Normally, we modularize the procedures in order to avoid this error, but I would like to know more about the internal organization/storage of a procedure. (...)
19 March 2009
Open ref cursor for sql stored in a clob variable: i have a sql query of length 375019. I used a clob variable to store the query as i cannot use the varchar2 type variable. When i try to execute the statement " open ref_cur for v_clob", it throws an error . Is this something doable? Kindly help! (...)
19 March 2009
can different way of writing sql result in different plan? ( more or less work for oracle to do): Hi Tom it is really nice that I can put a question here again. I heard people discuss that the ways you write sql could result in the different plan from oracle server,altough the result set is the same( such as select). for example, (...)
19 March 2009
Aging report: I am trying to run an aging report on the following table where the amounts are broken down by 0-30 past due, 31-60, 61-90, 90+ <code>CREATE TABLE transactions (hmy NUMBER NOT NULL, stotalamount (...)
19 March 2009
Does Oracle store transaction entries on a fixed size page?: "If I had 10 transactions all wanting to update a row on the page but there was only enough free space on the page to hold 7 ITL slots then effectively I have a page (block) level lock because 3 transactions are waiting even if they want to update (...)
18 March 2009
Can there be an infinite DUAL?: <code>Hi, Some time ago I came across a situation where I needed to duplicate a single row N times on an otherwise empty table, whilst incrementing a "manual" sequence number (The database is from a legacy system and does not employ Oracle (...)
18 March 2009
normalize and surrogate key in oltp: <code>Tom: we are OLTP. when table are fully normalized and some surrogate key is introduced, normally , it introduce a lot of join. is that all right? without introduce surrogate key, we can reduce lot of join. </code>
18 March 2009
Set autotrace on: <code>Hi Tom When I enter the statement set autotrace on i get the following error. SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled SP2-0611: Error enabling STATISTICS report How can solve it? (...)
18 March 2009
ORA-28031: Tom, Thanks for maintaining such a good website!!! Once in awhile we get the following Oracle message when attempting to login to Oracle. ORA-28031: maximum of 148 enabled roles exceeded (was surprised no one asked you about this yet) (...)
17 March 2009
DBMS_OUTPUT: <code>The following is a message I posted on Oracle Metalink's PL/SQL Forum for which they are seemingly unwilling to put effort into finding out a reason why this is occurring. If you have any insight into why this may be occurring it would very (...)
13 March 2009
indexing strategy: <code>Hi Tom, We have a table that has over 3 million rows with no indexes (not my fault I'm new). There is a candidate for a primary key, but, this key is just a generic id key and won't be queried. There is another column which is not unique (...)
13 March 2009
Standby Database/Alert Log: Standby Databases: Whenever I create a new tablespace in my default database, the tablespace is not getting recreated in my standby database, causing me to manually recover the standby database. Could you offer suggestions on what I may be (...)
12 March 2009
privot table: <code>Tom, I have table : Name Null? Type -------- ---- ROUTE NOT NULL CHAR(10) WO_NBR NOT NULL CHAR(10) ... DATE_REF ...
12 March 2009
Avoiding circular references: I've got at table, that looks something like this: create table T ( FromId number(9), FromType number(9), ToId number(9), ToType number(9)); It is used to "link" document of various types (FromType and ToType). Different (...)
12 March 2009
In list question with bind variables.: <code>Hi Tom, I am using Pro*C and I need to send an array of strings in a "where <value> in :<value>" is this possible? And if not what should I do? </code>
12 March 2009
what kind of analyze is best: Hi Tom: we are having some issues with performance on our 500GB database environment. we do analyze tables on a daily basis every 4 hours since data loading on different time, not sure when the data will finish loading. I have seen different (...)
12 March 2009
Pessimistic Locking via Web: <code>I am reading your book, and just read about pessimistic locking to make sure users do not get vanishing updates (by being the second session to update a non-locked row). I am wondering if/how you normally address this via mod/pls-sql (...)
12 March 2009
Locking scheme.: <code>Hi Tom, I've learned so much on this website, thanks for the work you put in. The situation: We are planning to implement a new application in our company, and one of the things which did not settle well with me was their mechanism for (...)
11 March 2009
Kill a session from database procedure: <code>How i can kill a session from a stored database procedure. There is some way to do this? </code>
11 March 2009
When are partition statistics beneficial?: <code>Tom, When are partition-level statistics used by the optimizer in 9iR2 and 10g? The only thing I've found in the docs so far (trying to avoid RTFM) is that they're used when a query fit the form SELECT ... FROM MYTABLE PARTITION (PTN_NAME). (...)
10 March 2009
DBMS_SQL : using bind variables in insert stmt: <code> Hi Thomas, I understand that using bind variables in an insert statement can speed up the performance. Can it be done the same way using dbms_sql ? If yes, could you please provide me with an example of inserting a bunch of rows (...)
9 March 2009
Multiple Listeners: <code>Hi, My question is regarding the automating startup of multiple listeners. I'm running an Oracle 8.1.6.0.0 on Sun Solaris with two listeners (LISTENER and EXTPROC_LISTENER).After a server reboot I noticed that only one listener was started (...)
9 March 2009
Table Design for DataMart/Datawarehouse: <code>Hi Tom, I have a question for you. I am in a process to design data mart, source data is in DB2 and in 3NF. For my staging area, I designed the model exactly same as source data model except that I resolved one-to-one relationships and sub (...)
7 March 2009
Index Organized Tables.: <code>My question is - how to reuse space in Index Organized Table (IOT) after deleting substantial amount of rows. When I deal with regular table, after deleting a lot of rows I rebuild the indexes in order not to leave gaps in the index blocks, (...)
7 March 2009
Is Oracle Text option right for searching within VARCHAR2 column?: Tom, We have a table in which we store the names of various businesses. These businesses provide various services to us. The datatype of column with business names is VARCHAR2(100). The table has millions of rows and growing. Our users have a (...)
6 March 2009
SELECT with WITH Clause(subfactoring) and ORA-4031 errors.: Tom, I want to validate following reasons to keep away from using "WITH CLAUSE" in the select statement: a) Each time the WITH query clause is run, a global temporary table is created and used in the query. Even if the SQL is using bind (...)
6 March 2009
DBMS_OBFUSCATION_TOOLKIT.DES3GetKey , MD5 Functions: <code>Hi Tom First of all , thanks very for proving us with this Great Oracle support platform.. Today my query is about the DBMS_OBFUSCATION_TOOLKIT. My requirement is to generate fixed length string (7) to be used as a password , and in (...)
6 March 2009
HTML DB Performance: <code>I have begun writing some HTML DB applications, and I'm finding it to be really cool stuff. However, I often find when I try something new that tutorials and documentation (and even the OU class I took) concentrate on showing how to create (...)
6 March 2009
Restartable Logic: Hi Tom, Another question since I have the chance: How would you implement a 'Restartable Batch Procedure'? e.g. every night a batch needs to process gazillions of records and in case anything terrible happens, it should be possible to (...)
6 March 2009
web services in plsql/htmldb: <code>Hi Tom, For some time I have been using UTL_HTTP to implement web services in a strict PL/SQL and HTML DB (no Java) environment. I do know that JDeveloper has a nice build-in mechanism for creating stubs for a given WSDL definition but I?m (...)
6 March 2009
outer join: <code>Tom SQL> desc emp Name Null? Type ----------------------------------------- -------- ------------------------- EMPNO NOT NULL NUMBER(4) ENAME (...)
5 March 2009
TNS_ADMIN: <code>I find that SQL-Plus ignores TNS_ADMIN environment variable, while Net8 Assistant uses it. Also SQL-Plus understands an "ifile=" statement in tnsnames.ora, while Net8 Assistant doesn't. The question is: Since I want one centralized (...)
4 March 2009
How to do direct path insert to tables with unique indexes: <code>Tom, Thank you for taking this question. I hope you can show me a way around a thorny issue I am facing now. I am loading a huge amount of data into our data warehouse using direct path insert (insert /*+ APPEND */). Here is the general (...)
4 March 2009
sql to get data from flat file-- this is mind blowing: <code>"In 9i, with the addition of external tables ( the ability to query a FLAT FILE with SQL) -- SQLLDR might be "moot". Using the external table and the insert /*+ append */ I can basically do a direct path load from file to database without (...)
3 March 2009
Date Validation with PL/SQL: <code>I asked a question about date validation a few days ago...but the response did not work for some reason. Here goes again (I know it's simple, but it's killing me right now) I am accepting 3 values from a form on a web page - day(1), (...)
3 March 2009
"Significance level" adaptive threshold type: In version 11g of Oracle database, there is a new feature whereby current performance data (obtained from AWR snapshots) can be compared against an AWR baseline and an alarm triggered if a given metric exceeds a certain threshold. From what I (...)
26 February 2009
How to find out from which procedure the error is emanating....: <code>Tom I have a procedure , which call another procedure , which in turn calls another procedure, and this finally call another procedure. In between these procedures also call functions, which in turn call other functions. Now when I run (...)
25 February 2009
Performance difference between executing db View and the actual tables: Hi, I have a view which consists of the following view definition <code>CREATE OR REPLACE VIEW My_view( id,col1,col2,colx ) as select t1.id, t1.col1, t1.col2, t2.colx from table1 t1, table2 t2 where t1.id = (...)
24 February 2009
Invokers Rights: <code>When I use invokers rights in my PL/SQL block, I use the caller's privileges and objects in the caller's schema, instead of the definer's. However Oracle says that "external references to other statements are still resolved in the schema (...)
23 February 2009
"not exists" does not exist (for me): I <b>never</b> use "not exists" The reason is: everytime I wanted to use it, I noticed, logically the same query can be achieved by using an outer join and then adding a condition "and <not null column (of the outer joined table/view/subquery)> is (...)
23 February 2009
analyzing only partition of table: <code>Dear Tom, Is it possible to analyze <compute option> only a partition of a table. <using DBMS_STATS package> i want to analyze a table <table having 83 million rows> parallely. when using ANALYZE table command for estimating statistics it (...)
23 February 2009
Dataguard - Client failover on standby database configuration: <code>How can the clients automatically connect to the new Primary database (previously Standby database) in case of failover/switchover using Tnsnames.ora without changing tnsnames.ora file on each client machine. Do I have to make two listeners on (...)
23 February 2009
redundancy select result: hi, I have 3 tables for example: table01, table02, table03. They have a same column named by 'name'. Such as SQL>select * from table01; NAME a b c SQL>select * from table02; NAME c b a SQL>select * from table03...
23 February 2009
using PL/SQL Varaibles: create or replace procedure Getrate1(v_rate_id varchar2) IS Col1_Tod varchar2(10); Gtod varchar2(10); Begin select substr(<Column1>,4,2) into Col1_Tod from <Table1> where rate_id=v_rate_id; dbms_output.put_line(Col1_Tod); select Column2 (...)
23 February 2009
How does invoker´s rights solve the PL/SQL privs problem: <code>Hi Tom ! In a previous question, I asked: ........................................................ I have both MDA and CORPORATE schemas. MDA has been granted "ALL" table privileges to access CORPORATE through a role. When I (...)
23 February 2009
develop on 10G and promote on 9i: Dear Tom, We are using oracle release 10G in our test environment (a large use of plsql object type and collections). But we are going to promote these objects in acceptance and in production environments that are build on oracle release 9i. (...)
23 February 2009
Average Row Length is not correct in the DBA_TABLES: Hi Tom, I'm turning to You, because I do not found any answer for my question: Question: Why I do not see the right AVG_ROW_LENGTH in the DBA_TABLES Dictionary Table? Why is it not correct? How will it be calculated in the background? (...)
23 February 2009
performance: Please see the below code do i need to put commit after all DML...OR one Commit will work .. if err_sacdm_cus.count>0 then begin forall i in err_sacdm_cus.first .. err_sacdm_cus.last save EXCEPTIONS (...)
23 February 2009
How to make a table drive a query: Hi Tom, In some of your posts, you mentioned "what if we drive with table1", or "what if we lead with table2". How do you make the optimiser 'drive with table1' or 'lead with table2'? I pretty much understand how to interpret the (...)
23 February 2009
CPU Usage: Hi Tom, First of all i would like to give many thanks for this website. My question, We have our own ETL Component (which is designed based on Oracle Database and Multithreaded Java concepts) - which is running in Production server now, it is (...)
23 February 2009
NVL in update columns: Hi Tom, Just a thought, I have two columns in a table(T) say columns A and B. I want to update a value to any one of these columns which is not null. definitely one of these columns is NULL and the other is NOT NULL So I have to (...)
23 February 2009
size of index become larger after rebuild: There is a source database A. Size of index is 30G. Use expdp command export a dump file. Then use command impdp import the dump to another database B in another server. But size of index in B is 9G. Then found some index is smaller than (...)
23 February 2009
Development Environment/Methodolgy: <code>Tom: Hope this gives you a bit of a break from the technical :-). Can you give me a general picture of what a development environment should look like, with regard to permissions in the database? Consider an organization with 2 data (...)
22 February 2009
How to find out which session/process caused ORA-01555: <code>We have a database running on 9.2.0.5 and we do have intermittent ORA-01555 errors in our alert log. There is no trace file to get the process information. We have a piece of the SQL statement that caused the ORA-01555 in the alert log (...)
22 February 2009
Retrieving DBMS_OUTPUT.put_line from JDBC?: <code>I'm using a Java application to process an incoming feed. The app inserts data into some Oracle tables, then runs an Oracle stored procedure. The Java application is called from a UNIX script, connects to Oracle using Oracle's thin client (...)
22 February 2009
SYS grants and export/import: Hello Tom I user exp/imp utilities to reproduce the production system for test and development environments. I user the exp system/**** full=y to export the production database and imp into a new database. Of course i lose the sys grants and i (...)
20 February 2009
Daylight Savings Time: <code>We have customers with offices in different time zones around the country. So, we've decided that when a new user is created in our system (through our application's UI), we'll ask the user for their time zone and we'll store that with the (...)
19 February 2009
Bind variable peeking & JDBC: Tom, Thank you for a very helpful site. I saw you at the Hotsos symposium this year and I am almost finished Effective Oracle by Design, which will be the first technical book (of many purchased) that I actually read cover to cover. I am (...)
19 February 2009
difference between count(1) and count(*): <code>What is the difference between count(1) and count(*) in a sql query eg. select count(1) from emp; and select count(*) from emp; </code>
19 February 2009
SGA size: <code>Hi Tom, Can you give us an idea as to how to estimate size of SGA? An experienced dba told me that the best way to estimate size for SGA is to set aside 40% of memory for sga i.e for 2gb memory 800mb is sufficient. Is this correct? If yes, (...)
19 February 2009
SQL*Net message from client: Tom, I have a query that is taking about 20 seconds to execute. But the trace shows me that is executing in about 8 seconds. The problem is in the SQL*Net event? Any suggestion? Thanks Tom! <code> call count cpu elapsed (...)
19 February 2009
mview with datetime stamp: dear tom, i have a table hrdemp ( ename varchar2(100), addr1 varchar2(100)); i want to create a materialized view based on this table which will have these two columns along with two more columns say creation_datetime, and (...)
19 February 2009
SQL Query to find gaps in date ranges: Hi Tom, I have a problem that I would like to solve using pure SQL and not PL/SQL (if possible). I suspect that there may be an Analytic solution to the problem, but have not been able to work it out for myself thus far. I have a table with (...)
19 February 2009
How to do bulk collect in Pro*C variables: <code>Hi Tom, I am writing one Pro*C code. In that code I want to get values from a table and want to write them into one file. How can I do bulk collect using Pro*c variables so that in one single statement I will get all the values (...)
18 February 2009
Oracle Security Information.: <code>Hi Tom, I am looking for a script , Procedure or a query to extract the information regarding the security implemented in the database for the all the users. I want to get all the users along with assigned roles, privileges assigned to (...)
18 February 2009
alter table monitoring clause: <code>Hi Tom, I want to know if I have altered the table with 'Alter Table Monitoring ' will it create load on Database if lot of inserts are occuring on that table duringg the day. Thanks Vrishali </code>
18 February 2009
Aggregate query on EAV table: EAV (Entity-Attribute-Value) stores all the data in a "generic" format like this: create table EAV ( row_id integer, col_name varchar2(100), value varchar2(1000) ); Suppose we have the following data (...)
18 February 2009
histogram: <code>Tom Can you give a simple example of how to create a histogram, to see the performance of a query? </code>
18 February 2009
Transport tablespace: Hi Tom, I am trying to alter the tablespace to the transportation tablspace. First I do: alter tablespace users read only; Then do : exp sys/change_on_install file=02072001.dmp transport_tablespace=y tablespaces=users triggers=n (...)
18 February 2009
Is there an easy way to make a ctas: Hi Tom, If you want to add several columns to a table you must do a create as select, but this is'nt so easy, you have several steps to do: 1) Recreate foreing constraints referencing constraints in the current table 2) recreate current table (...)
17 February 2009
Group By, Group By Rollup, and Grouping Sets: It always bothers me when I have to kluge a query to get it to work. I have a table of questionnaires and the person who completed each one. <code> create table ques_set (ques_set_id int, pers_id int); insert into ques_set values (1,48); (...)
17 February 2009
SQL queries/cursors in a loop statement: Hi Tom, I would like you opinion on the following two statements if you will please : 1. Avoid using SQL queries inside a loop 2. PL-SQL: Avoid Cursors inside a loop I appreciate that they are very generic statements, but that is my (...)
17 February 2009
I have a problem with dbms_scheduler: Hello Tom I've created a job (please see log #1, aparently successfully , it run with user X300905). But I can't find it with select * from DBA_SCHEDULER_JOBS. And a run_job fails (see log #2). I don't know what is the problem. Thanks you. Log (...)
17 February 2009
SID YES SERVICE_NAME NO in tnsnames.ora: Hello Tom, We use tnsnames.ora . 1) What is the difference between SID entry and SERVICE_NAME in the tnsnames.ora 2) When I put SID the connection is successful, when I put SERVICE_NAME it says tns could not resolve service name So This (...)
17 February 2009
Oracle Text (Token parser): <code>have you written a parser function that will translate query strings for Oracle text into search expressions. i.e. user input string == translation "word1 word2" == word1 word2 wo...
17 February 2009
optimizer_index_cost_adj: <code>Tom, On searching for 'optimizer_index_cost_adj' on your site there are a couple of places where you recommend this parameter to be changed if required. But the metaLink document 'Init.ora Parameter "OPTIMIZER_INDEX_COST_ADJ" Reference (...)
17 February 2009
Multiple DBWn: <code>I am trying to determine wheather to increase DBWn processes. We have just implemented parameter changes for parallelism. DB_BLOCK LRU_LATCHES=6 CPU's=6. I have been reading oracle metalinks, oracle online doc's and your Archives to find (...)
17 February 2009
Performance with outer joins: Tom, My requirements are as follows: Get all records from table A, that do not exist in table B. Limit this to records that are between 2 dates. Sample table structures: create table A ( A_ID NUMBER not null, GUID (...)
17 February 2009
copying a table: Hi Tom, I like to make a exact copy of a existing table but with another name. Like a backup copy of this table with the same indexes, constraints, data etc.. so I can restore this table later. Thank you for your precious help.
17 February 2009
MERGE/UPDATE LOG ERRORS and Unique Constraints: Hi Tom, could you explain, why a MERGE/UPDATE with the LOG ERRORS option raises an error when a unique constraint was violated? What's the difference to, let's say, a NOT NULL or a referential integrity violation? Regards Wolfgang
17 February 2009
How to Audit Invalid Objects and Other Things: Hi Tom, Because of a recent hicup on our production database where we accidentally recompiled a valid package (and it naturally caused some statements executed by pre-existing connections to fail with ORA-04068, etc.). And because of many busy (...)
17 February 2009
segment space mangement: Hi tom , In a test environment i performed following steps, CREATE TABLESPACE TEST DATAFILE 'D:\ORACLE\ORADATA\DEMO\TEST01.DBF' SIZE 10M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT MANUAL ; CREATE TABLE QQ TABLESPACE TEST (...)
17 February 2009
Compressing rows: Tom, I have tried reading the fine manual and looking at many examples on AskTom, but I haven't found anything to help. I have a table that has id's and data. There are some id's that have multiple rows and I want to 'compress' or merge the (...)
17 February 2009
logging errors to os file in pl/sql without utl_file: 1.dba has told to discontinue utl_file usage in pl/sql due to fail over issues below : want to failover only database to the second node but keep file system in the first node (or unplanned DB failover to second node), UTL_FIL programs will fail. (...)
17 February 2009
Performance: <code>How would be the performance in case if I cache all the tables into SGA. Take for instance... Production OLAP database size of 10 Gb.. System has 10Gb of main memory. Allocate entire RAM to SGA(its production only, has no other (...)
16 February 2009
SQL Standards: <code>Hi Tom, I can not find this information anywhere. insert into ISSYEAR 2 select SUM(if(IF BSIS.BSIS = B, PROJMARCH+PROJJAN+PROJFEB)) AS Q1, 3 PROJYEAR 4 FROM BSIS, FLATFILETEST 5 where BSIS.VARIALBE = (...)
16 February 2009
Truly complex pivot: <code>I'm trying to do a complicated 2-way pivot in SQL, and can't work out where to start. Assume a table as follows Event Group Month ----- ---- X A Jan Y A Jan Z B Jan A C Jan (...)
16 February 2009
ARCHIVELOG or NOT ARCHIVELOG?: <code>Hi Tom, thanks for your help before and I've some smalls questions for you: I would like to know what is the different between create DB in archivelog mode or not? When I create a DB with Oracle Database Assistant, it create me in which (...)
13 February 2009
The issue of bind variables only arises( in p/sql) when we are doing dynamic sql and not with static sql: <code> Tom 1. According to you , the issue of binding variables arises only when we are creating queries on the fly i.e. using dynamic sql. Please give an example( and discuss) and show how we accidentally hard code variables when using (...)
13 February 2009
flashback using timestamp: <code>Hi I am using flashback feature but I cant get it to work properly, it sometimes works sometimes dont. In session A I do this SQL> select sysdate from dual; SYSDATE 2002-OCT-14 16:33:47 1 row selected. SQL> (...)
11 February 2009
difference between sql with clause and inline: Hi Tom, This morning I saw an example on otn describing the sql with clause in 9i. I also saw an example, unfortunately that page seems to have been removed so cannot give you a reference. Here is the question though. How does a select (...)
10 February 2009
exp imp issue: Hi Sir, I recently did an exp of one of our production schema version 817. I want to imp this on my local laptop where Database server is installed version 9.2. I created the same user with LMT and auto extend of size 500 MB.The (...)
10 February 2009
Does select generate undo ?: <code>Hi Tom, I have recently faced a question from respectable person from Oracle. He asked me which kind of statements generate undo ? I said insert/update/delete/merge .. etc. He asked me where select statement generates undo ? I said (...)
10 February 2009
PCT FREE for Indexes: <code>Hi Tom, Lot has been said on this site and books about pctfree and pctused for creating tables. What about pctfree for indexes? I'm working on Data Warehouse application (no updates), it is easy to decide about pctfree and pctused for data (...)
10 February 2009
Tune this... (Without shooting the developper): Hello M. KYTE, For our helldesk ticketing and change management system, we use a piece of software, made by some company, that is supposed to run on an Oracle database. I say supposed to run, because it actually crawls... It's some RAD (...)
7 February 2009
Transparent Data encryption in 10g release 2: <code>Tom, In your presentation at OGUC, you highlighted Transparent Data Security as one of the cool features available in 10g R2. Could you please shed some light on the following questions ? * Who makes the encryption transparent in (...)
7 February 2009
Strange Chr(0): <code>Hello Sir, Can you please explain 1) What is CHR(0) 2) Why , anything that it touches dis-appears from the display though the value is still present.Anything "only" to the right of chr(0) becomes invisible.See STRG_1. 3) How (...)
7 February 2009
Database Creation Script: <code>How can i generate a sql script from an existing database in order to create the database on another server? Do i need a utility to perform this task? Thanks for your help! matthew </code>
7 February 2009
Number of sort segments: <code>Hi Tom, How many sort segments are usually created in the temporary tablespace ? Only one by the first sort operation and reused by all other operation ? Or several ? Is there any rule ? Can V$TEMPSEG_USAGE be used to know exactly the (...)
7 February 2009
generic trigger for auditing column level changes: I'm trying to create a generic before update trigger which will compare all :old.column_values to all :new.column_values. If the column_values are different, then I would like to log the change to a separate table. When I try to (...)
6 February 2009
Report of activity over a week: I have a table <code>create table t (id number, name varchar2(10), start_dt date, end_dt date);</code> That holds some info about activities I need a report that gives me how many records (...)
6 February 2009
SESSIONS CREATED DURING PARALLEL QUERY PROCESSING: Hello Tom, Are there any differences between the session created during database logon and the sessions that are created during parallel query processing. If so why is it listed under v$sessions? Please clarify. Thank you
6 February 2009
End of Support and End of Life Support for Oracle 9i Database: Hi, would appreciate if you can kindly help to advise me when is the End of Support and End of Life Support for Oracle 9i Database Thanks & Regards, Valerie
6 February 2009
can we append some data at the end of the first line in the same file with utl_file package: PL/SQL - can we append some data at the end of the first line in the same file with utl_file package
5 February 2009
Suggested OS: Hi Tom I want to know 1. Which OS do you suggest us to use as an Oracle Database Server? 2. Which OS do you use as an Oracle Database Server? (of course, from your answers it seems that you don't use Windows :)) And can you provide OS (...)
5 February 2009
truncate vs delete with constraints on tables: <code>Tom, I haven't used constraints as much as this new project that I am on. Usually I truncate tables to clear them out as it frees up the space etc. Yet when I tried to truncate the tables in RF order, I received constraint errors and it (...)
5 February 2009
10053: <code>Dear tom, A. your new book is illuminating. B. could you please, post an 10053 trace file and explain in your Word what is going on there? C. try to hack it my self - didn't worked for me, too much numbers. Regards, Amiel (...)
5 February 2009
Procedure Code.: <code>Hi, I have a simple question to ask: Which dictionary view contains the procedure code. DESC PROC give me 'PROCEDURE PROC'. I want to give access to the code of my procdures to another user in addition to 'grant execute to PROC; ' (...)
5 February 2009
excluding a tablespace from backup: Dear tom Sir, Ihave two questions #1 I gave the following command in 9i. Rman> configure exclude for tablespace users; **no message for setting the configuration came.** then I took a whole database backup. but the tablespace users has not (...)
5 February 2009
Japanese text in oracle database table: Hi Tom, Kindly explain how to create a table to store and retrieve Japanese char set (UTF) values. Is there any way the same table can contain English and Japanese (UTF) char set values ? for example first record of a table contain English (...)
5 February 2009
get the base table of views: Thanks a lot for your hard work. My question is writing a query to get the base table name of view. Are there any dictionary views including these information? Thanks.
4 February 2009
Regular Expression in SQL Loader: SELECT EMPNO,MGR, REGEXP_REPLACE(EMPNO,'^(.*)(2)','\1-\2') AS SPLIT_TOKEN FROM EMP EMPNO MGR SPLIT_HYP ---------- ---------- 7839 78-39 7698 7839 76-98 7782 7839 77-82 7566 7839 75-66 7654 7698...
4 February 2009
Trigger - Not able to find the program unit being called: Hi ALL, I have a custom trigger((After Insert/Update) on Oracle Apps standard table). This trigger inturn calls a custom package. The problem is, some times this trigger completes with the following error message. This is not happening (...)
4 February 2009
General Questions: <code>Dear Tom, My Questions are 1)How materialized views can score over relational views? For example we can create a view as sql> create view v as select deptno,count(*) from emp group by deptno; The same (...)
4 February 2009
Split Partition Question: Hello Sir, Can you please tell me which would be the best option for converting big table with around 80 million rows with blob and clob data into partition table. I have few in mind but have some doubts 1. CTAS - (create table ... (...)
3 February 2009
db_block_size and setting tablespace block size: Hello Sir, Is there any difference in setting up db_block_size and tablespace block size. As, if i wish not to create my database with 16k block size(db_block_size) and then say we have a need to have 16k block size so we go and create 16k (...)
3 February 2009
Database Consolidation: Hi Tom, Do you believe the consolidation of the databases would be a cost saving that often quoted as an effort to reduce the hardware resources and number of DBA-support hours What I mean by the consolidation is first identify the databases that (...)
3 February 2009
Is there a performance impact for dbms_output.put_line statements left in packages?: Hi Tom, I'm wondering if there is a performance impact/hit for leaving dbms_ouptut.put_line statements in packages that will be called from Pro*C or Oracle Forms or run in batch such that there is no place for the output to be displayed? These (...)
3 February 2009
11g Active Dataguard and 10g Dataguard implemented with Logical Standby: What is the difference between Active Dataguard, and the <i><b>Logical Standby</b></i> implementation of 10g dataguard? It appears similar if not the same to me, any capabilities in <i><b>Active Dataguard</b></i> that warrant that extra licensing (...)
3 February 2009
Usage of Analytic Functions within a query having grouping: <code>Tom, Table tab1 has 3 columns col1,col2 and col3 I have a query grouped on col1. Columns col2 and col3 have non unique values for a particular value of col1. I want the value of col2 for the row having maximum value of col3 pertaining to (...)
3 February 2009
What After 10g?: <code>Hi Tom, Last month my team including myself, underwent training at Oracle University - Mumbai, India. During the discussions that we were having with the trainer it was commented by the trainer that the next version coming would be (...)
3 February 2009
Invalid view when uderlying table changes - shoudl recompile fix it: We (all of the developers here) were under the impression that if we created a view with the "select * from tablea" syntax that a recompile would fix the view even if new columns were added or column names were changed in thh underlying table. Take (...)
3 February 2009
Empty a XMLtype column: Hi, In order to reduce storage, my client wants to delete the XMLtype column data older than 3 months. The XMLtype is stored as a CLOB. He wants to keep the metadata in the other columns. In 10.2 there's a function to empty the column: set (...)
3 February 2009
How To FULL DB EXPORT/IMPORT: Hello Tom, How to do a Full DB exp and Import. I do say exp system/manager@xyz FULL=Y FILE=FULL.DMP Then If I want to do a full Import to a new freshly created DB which only has the default schemas sys , system , etc. 1) (...)
3 February 2009
Alter tables - multiple schemas: I want to "execute immediate 'Alter table '||v_column_name||' add ('||v_column_name ||' '||v_data_type||')';" but across multiple schemas in a single database instance. I have a table (HOSM) that I can loop through and pull the unique identifier (...)
3 February 2009
Select materialized view directly: Hi Tom, I want to know your opinion: Is there any pitfalls to query a mat. view directly instead of write query to the "base" table that could be rewritten?
3 February 2009
grant alter any procedure: Hi Tom, We have 2 schemas, UserA and UserB. I would like to grant ALTER ANY PROCEDURE on only UserA's schema. Is it possible? I know that when we grant alter any procedure system privilige, that user has this prvilige on every user's schema. (...)
3 February 2009
How to return result of a query in chunks of 100 rows at a time.: Hi Tom, I have a requirement where in users want that on the screen where we display result set of a query only 100 rows should be displayed at a time and again when user clicks button ( on the screen named Next) next 100 rows should be displayed (...)
3 February 2009
CPU usage >99%: Dear Tom, We have a great issue here in bussiness, We have a mulit-tier(3 tier) application based on oracle 10g back-end and OAS for middle tear (All Unix based).. Sometime the CPU utilization approches 100% this make the system works slowely .. (...)
3 February 2009
HTML DB: <code>Tom - I read a little bit about the new features in 10g for HTML DB. I'm pretty excited about it if it effectively delivers on these new features (e.g., the whole meta-data driven framework, the Application Builder, Session Management, (...)
31 January 2009
Poor performance using public synonyms: <code>Hello Tom, A person has been giving seminars all over Australia and one of his suggestions to his audience has been 'don't use public synonyms if you have a large user base'. His argument is summarised on his webpage: </code> (...)
31 January 2009
Questions on Pro*C multi-threaded programming: <code>Hi Tom, I am working on a project using Pro*C. I do find out lots useful info on this website. Here are my questions: 1) Where is the header file in which the sql_context is defined? 2) I want to created a number of threads. Can I (...)
30 January 2009
High Availability questions: <code>For 24 x 7 solution, we have designed MultiMaster replication with Transperent Application Failover (TAF ). We have achieved TAF thru adding entries in TNSNAMES.ORA file. Is this sufficient ? Have I to do anything with OCI8 ? In my (...)
30 January 2009
Cursor_Sharing: <code>Tom, We have an interesting problem, When we set cursor_sharing to 'force', doing following SQL having space trailing the return value 'Y'. SQL> select 'Y' from dual; SQL> 'Y' SQL> -------------------------------- SQL> Y (...)
30 January 2009
Generate PDF documents from PL/SQL: Hi Tom, How would you recommend generating PDF documents from within "plain" PL/SQL? Thank you, Dieter
30 January 2009
Truncate table by non table non-owner: <code>Hi, I would like to fing whats the best way to allow user to truncate table that is own by some one else thru forms on-line basis. I know that one way this can be achived is by granting drop any table system previledge. However this (...)
29 January 2009
SDU,TDU etc: <code>Tom: how do i know my current setting for SDU and TDU if it is not present at listener.ora and tnsnames.ora? also how to know what's setting for MTU? </code>
28 January 2009
ALTER SESSION SET CURRENT_SCHEMA: I have an application which requires us to change schema's within the application to update a table for various users. We have a variable in cobol called DB-SCHEMA which gets set. I am able to issue the with a cobol module. EXEC SQL (...)
26 January 2009
FORALL Update - Updating multiple columns: <code>Sorry for the confusion. profile_cur has 19 columns and has 1-2 million rows. An UPDATE in the cursor loop will update 1 row and 19 columns at once. The Update is dependent on results of 19 procedures which are quite complex. I can not (...)
25 January 2009
10046 and tkprof: <code>Hello Tom, Thanks for the great job that you are doing maintaining this site. My question is : Can I use tkprof to format trace file generated by setting event 10046 ? I checked couple of sites ( www.hotsos.com and www.oraperf.com ) and (...)
25 January 2009
no of return by a query,Paging: <code>Hi tom, I have two question to ask. Many thanks in advance. great site and yor book ultimate i have ever come across. Q.1 I have a cursor for example cursor c1 is select * from emp; I want to know that how many row will this cursor (...)
24 January 2009
Feedback on how to un-pivot a table: <code>To be brief I frequently am asked to produce un-pivots of data *and* calculate values. These calculations may also vary by department. Hard coding these views for a rather fluid calculation system and then to keep versions per (...)
24 January 2009
dynamic sql in pl*sql in V8: <code>Hi Tom, is there an easier way to handle a 'select field1, ... from table' by dynamic sql instead of using dbms_sql.bind_variable and these stuff? The problem is that the number of fields can be different. thanks for help Ines (...)
24 January 2009
CTAS vs Delete - runtime: <code>I have an unpartitioned table with the following: No of Rows: 9 million Size about 80 GB Contains a BLOB column I need to delete about 1 million rows. DB is 24x7 and about 1000 rows are inserted into the table every hour. This table (...)
23 January 2009
Rollback segment questions: <code>Hi Tom, I have some questions about rollback segments: 1) Which position of the rollback segment is going to be used by the new transaction if i) no active transaction ii) has active transaction in this rollback segment? 2) Do you (...)
23 January 2009
Advanced queuing: <code>Hi I am using Oracle 8.0.5.0.0 on NT When i run the following query, feature not enabled error is returned. execute dbms_aqadm.create_queue_table(queue_table=>'RawMsgs_qtab',queue_payload_type=>'RAW'); ERROR ERROR at (...)
23 January 2009
Index and LIKE Operator: Hi, Here is a small test case. <code>SQL> create table t 2 as 3 select * 4 from all_objects 5 / Table created. SQL> create index t_idx on t(object_type) 2 / Index created. SQL> exec dbms_stats.gather_table_stats(...
23 January 2009
TIME in EXPLAIN Plan Vs Elapsed time in TKROF: <code> Tom, CREATE TABLE T (x NUMBER,y VARCHAR2(4000)) INSERT INTO T(x,y) SELECT LEVEL, RPAD('*',LEVEL,'*') FROM dual CONNECT BY LEVEL <= 3999 scott@ORCL> EXPLAIN PLAN FOR SELECT * FROM T WHERE X = 859; Explained. scott@ORCL> (...)
23 January 2009
End to End Monitoring Tool/Process: Hi Tom, I would like to know if there is any "End to End" monitoring tool for performance of any request coming to the database and back to the end user. We have several applications accessing oracle databases. Some times if there is any (...)
23 January 2009
physical or logical standby: Hi Tom, What is your opinion when choosing DR site whether use physical or logical standby?
23 January 2009
How to calculate the actual size of a Index.: <code> Tom, I have learnt from your site about "How to calculate the actual size of a table" from http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:266215435203 But i couldnot find " blocks, empty_blocks,avg_space, (...)
22 January 2009
Special charater ü appears as ¿ after loading data using SQL loader.: Hi all, I am trying to load data having character u but it appears as ? in the database tables,why it is so? I am trying to load data from a flat file into oracle table using sql loader. Data gets loaded successfully but the special character such (...)
22 January 2009
Monitoring CDC: Hi Tom, I've setup CDC and everything is working great. The data is captured to my cdc tables as expected. However I now have to come up with a way of montioring the CDC activity and nofity the DBAs when it stops working. I've checked the CDC (...)
22 January 2009
Select clause for columns not in primary key: Hello, I have a table t1 with columns c1, c2, c3 and c4. Out of these column c1 and c2 are PK in that order. Now am joining this table with some another and making sure that the PK is in the join. However am noticing that the query goes (...)
22 January 2009
SQL Tuning: <code>Hi Tom, Many many thanks for sharing your ideas on this site. This site has been my regular learning source for almost all my questions. I would like to know the basic rules that one should follow while writing effective sql statements. (...)
22 January 2009
Inserting new record into a table: Hello Tom, I have a question for a requirement for a project I'm working on. I have a table: CREATE TABLE ps_bo (bo_id NUMBER(31,0) NOT NULL, bo_type_id NUMBER NOT NULL, bo_name (...)
22 January 2009
Bad performace ORDER BY and GROUP BY on single table: Tom, I'm new here and I have a performance question (an I just read lot in your "Effective Oracle by design book" ... great stuff ... but no answer so far). We designed a "master" grants matrix table to hold all oracle grants, application (...)
22 January 2009
how to retain oracle database userid during database migration: Hi Tom, We are planning to do a database migration+upgrade from 9.2.0.4.0 on windows 2000 to 10.2.0.4 on linux platform. We are thinking of installing 10.2.0.4 on linux and do an exp and imp. But the problem we are having is that some of our (...)
21 January 2009
Advanced Queuing: Hi Tom, I am glad I could ask a question now. My question is regarding Advanced Queuing in Oracle. My requirement is to capture changes in one database and send those changes to another database instantly. Eg: Database1 got new records added in a (...)
21 January 2009
Refresh Schema from Prod to Dev: Hi Tom, What is best way to 'refresh' a schema from one database to another? Here is what I am doing and I feel there ought to be a better/easier way... 1. Export (expdp) USER1 schema from prod 2. Determine USER1 privileges, etc. on DEV (...)
21 January 2009
tablespace read only: tablespace read only issue Hi tom, I logged in as sysdba and tried to make a tablespace read only when some other user session logged in the database, my session(sysdba) simply hangs (its not their default tablepace or they don't have (...)
21 January 2009
DBMS_SCHEDULER with Stored Procedure and output parameters: I am trying to define a job using DBMS_SCHEDULER to execute a stored procedure. This procedure returns an output paramter. I found examples of defining input parameters to be sent but haven't found an example that uses an output parameter with the (...)
21 January 2009
Inconsistent printing of XMLType: Hi Tom, I have been trying to get "nice" looking XMLType output in 10g, and have been running into strange inconsistencies with how Oracle seems to treat the datatype. I was wondering if you could shed some light on what's going on behind the (...)
21 January 2009
first_row Deprecated in 9i: The book of Jonathan Lewis(Cost-base Oracle page 2)wrote that the hint first_rows is deprecated in 9i. ( We are actualy working to pass for cost base :) ) We query a view with a outer join that build a bad execution plan. When the view is hint (...)
21 January 2009
performance issues using FOR UPDATE and WHERE CURRENT OF: Hi Tom, I have read your blog "http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:546822742166" which talks about commit exact fetch scenarios. I have a requirement where in I need to update the same table on which my cursor (...)
21 January 2009
Sequences or GUIDs: <code>Tom, Our application inserts a lot of records (it's a web-selling app) to a table. We were planning to use Sequences as a generator of unique values for the TRANS_ID column. Later we were told to consider GUIDs as unique numbers. The (...)
21 January 2009
What´s your oppinion about the DBA job in the future: <code>Hi Tom, First, i would like to thank you for your site in web. I learn a lot of things with the doubts of my coleages registered in this site. I learn too how to explain to the developers using examples (like you). Well, i would like to (...)
21 January 2009
Importing csv (or xls) AND creating the destination table in PL/SQL: Hi Tom. I need to write a PL/SQL web application that: 1) let the user upload an Excel file (csv or xls) to the server 2) import its content in a new table I have been experimenting with external tables, but it seems to me this is not the (...)
21 January 2009
How to populate BLOB column of a table from the client using PLSQL: Hi Tom! Is it possible to load files(gif,pdf,doc ...)into a an oracle table when the files are available in the client filesystem? Please note that the file sizes could vary from 1K to 200K, so do I need to compress them before loading? Does (...)
21 January 2009
Using Interval Datatype: <code>I have the following query where TS_END and TS_START are DATE columns. I used to run this query to give me the average response time in seconds: SELECT COUNT(*) system_process_count, TRUNC(AVG(lgrt.ts_end - lgrt.ts_start)*24*60*60, 1) (...)
21 January 2009
Locking Mechanism in Oracle: <code>Hi Tom! I have been following your site and its really invaluable to say how much your solutions to various problems helped me. Thank U Tom for all U do. Pl. Keep up the excellent work. Now to start with, I am facing a problem releated (...)
21 January 2009
pl/sql for native execution: <code>Good morning Tom, Could you please give us a hint on the performance that can be reached compiling a procedure into native code, c for example. Thanks. </code>
20 January 2009
Export Backup Taking too long(Delay in backup by ten hours): Dear Sir, I have a problem regarding the oracle nightly export backup. earlier it used to take around 9 hours for 400GB database export backup a particular schema. now it takes around 19 to 20 hours. Could you please suggest technique to tune (...)
20 January 2009
EXP terminated without enough space: CREATE TABLE TEST ( "TRANS_ID" VARCHAR2(150 BYTE) NOT NULL ENABLE, "RQST_SYS_ID" NUMBER(*,0), "RQST_XML" CLOB, "RQST_DATE" TIMESTAMP (6), "RQST_API_NAME" VARCHAR2(100 BYTE), "HOST_NAME" VARCHAR2(100 BYTE), (...)
20 January 2009
A Few Questions: <code>Hi Tom. I was looking into COALESCE. I checked the PCT_INCREASE parameter for the tablespaces, as I understand that if its 0 (Zero) then SMON automatically coalesce's it. SQL> select tablespace_name, pct_increase from dba_tablespaces (...)
20 January 2009
Complex Query: Hi Tom, I have a complex query condition - I am dealing with a table that contains Death information, partitioned by death year. The cause of death are grouped into certain categories Say C1 thro C50. For any death year, I need a report on the (...)
20 January 2009
Compressed Indexes & Tables: <code>Hello Tom ! what are the pros and corns of compressed indexes/tables ? Is there any performance overhead in this regard ? I heard that compressed indexes save space but cause increased CPU time...is that true ? Regards Riaz Shahid (...)
20 January 2009
COLUMN LEVEL CONSTRAINT: Hi Tom, I have created a table emp with column level constraints as shown below: CREATE TABLE EMP (IDX NUMBER PRIMARY KEY, ENAME VARCHAR2(250), SAL NUMBER CHECK (SAL >=0)) now I want to create another table on the top of emp (...)
19 January 2009
Application Servers and Row/Column Level Security: In oracle database there are three methods for implementing row/column level security : views and access control over these views. Virtual Private Database. Oracle Label Security. But all of them use the user account utilized to logging (...)
19 January 2009
Override locking of FOR UPDATE: Hi, I am using the following function to generate a primary key FUNCTION getID ( ) RETURN NUMBER IS PRAGMA AUTONOMOUS_TRANSACTION; v_ID NUMBER DEFAULT 0; BEGIN SELECT ID.cnt INTO v_ID FROM ID FOR UPDATE; UPDATE ID SET ID.cnt = v_ID (...)
19 January 2009
about the size of ora_spid.aud--it can be large?: Hello Tom recently I encouter a problem , which is related to the size of ora_spid.aud. the size of the file is 110k. and eventually the /dba is full. I understand that when sysdba login, oracle will make a file in the (...)
19 January 2009
Time that logs need to be kept.: Hello, We currently have a dataguard setup using a filesize of 43MB each. I have recently noticed that when it was originally setup there wasn't a time set to age out the old log files so we have been keeping them in the arch directory for over (...)
19 January 2009
Spell Numbers in arabic: Hi Tom, i saw your solution to spell numbers which is very good. im looking for a function which converts a number to arabic words. what i did was converted the number to english and then replaced it with it arabic equivalent. but the converted (...)
19 January 2009
Removing Duplicate Rows While Loading from External Table: *** TABLE DDL create table internal_table ( id number, timeseries_name varchar2(50), timeseries_description varchar2(250) ); insert into internal_table values( 1, 'macro1', 'macro_timeseries1'); insert into internal_table values( 2, 'macro2', (...)
19 January 2009
BULK COLLECT - SAVE EXCEPTIONS - how to know the exact row.: Hi Tom, I am working on a datawarehoue project and using BULK COLLECT with SAVE EXCEPTIONS to log errors while loading data from source to destination tables. I loop through the BULK_EXCEPTIONS and insert the error_index and error_code with (...)
19 January 2009
Temporary Table Questions: <code>Tom, I have a few questions abt temporary table: 1. Which type of temporary table is better to use in a multi-user applications, a global temporary table with on commit preserve rows or a global temporary table without on commit (...)
17 January 2009
Transactions and Isolation Level Confusion: <code>Hi, Tom. I have some questions about transactions and isolation levels that come from my reading of your books and answers in this forum, as well as the Oracle Database Concepts. 1. You've said that using serialazable isolation level (...)
17 January 2009
Replication: <code>Tom I recently read the first chapter from your Oracle Expert One to One book and it so amazed me and supported things I have been saying for years I purchased it immediately. I have started to read it and its brilliant. Anyway on to (...)
17 January 2009
How to know SGA and PGA size is insufficient?: Hi Tom, This is continuation to the followup at http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7739845026791#485777700346728968. As I found it more appropriate to be a new thread I am asking this question. We are using (...)
17 January 2009
Oracle Table-Partition: <code>Hi, I've a table : " TABLE2000 " with Partitions : TAB_1999_H1( Tablespace TAB_DATA_99) TAB_1999_H2( Tablespace TAB_DATA_99) TAB_2000_H1( Tablespace TAB_DATA_00) (...)
17 January 2009
DB BUFFER SIZE/DB_KEEP_BUFFER_SIZE/`DB_RECYCLE_BUFFER: <code>HELLO, I WANT TO KNOW THAT IS DB_KEEP_BUFFER_SIZE AND DB_RECYCLE_BUFFER CACHE ARE PART OF BUFFER CACHE,e.g if db_cache_size is 10mb then the sizes of `DB_KEEP_BUFFER_SIZE and DB_RECYCLE_BUFFER must not be greater then db_cache_size or they (...)
17 January 2009
Database redesigning: I am asked to do the re-design of a database which is having very poor performance. Majority of tables have the primary key as composite keys consisting of 2 to 5 columns. In the application sql where clause, I found that not all columns of (...)
17 January 2009
Is a composite primary key a good idea?: <code>Tom, I have a table that has 3 fields that, when considered in combination, could uniquely identify each record. These fields are... Object_ID CHAR(4) Ticket_Number NUMBER Start_DateTime DATE It will never be the case that (...)
17 January 2009
Trigger on a table Fire at commit.: Hi, I have few transactional tables. Tab_M is the Master transactional table. All other concern tables are also transactional and child(or child-to-Child) of the table Tab_M. I want to put a Trigger to hold some bussiness logic. (...)
13 January 2009
export using QUERY: <code>Dear Tom, I find that Oracle8i can export table data selectively by using QUERY. Below is the exp that I wrote for it. This is the first time I used it and was not successful. The error message is: table_export[2]: CUA_TRANS_DTS: not (...)
13 January 2009
Enterprise manager: <code>Tom, I know I could use </code> http://localhost:5500/em" <code>to get the enterprise manager. But if I have several instances in the same server, it only lets me connect one database this way. How could I connect to every instance using (...)
9 January 2009
Performance issue for XML loading: Hello Tom, We have an XML in a remote table which is stored as a CLOB column Type , this is stored in a local table local_table. The XML is Stored in the following format: <Transaction> 1 <Legs> n <payments> n i.e (...)
9 January 2009
Dual Table: Hello Sir, <code> create or replace function test1(in_string in varchar2) return varchar2 as out_string varchar2(2000) := NULL; begin select DECODE(SUBSTR(IN_STRING,1,1),'1','1','Y','Y','N','N','U','U','W','W',' ',' ',' ')|| (...)
9 January 2009
Cumulative Sum by Date: Hi Tom, I have a table: create table t (PO varchar2(30), LineNum int, as_of_date Date, Total int) with the following data: insert into t values ( 1, 1, to_date('04-JAN-2007'),7000) insert into t values ( 1, 1, to_date('06-JUN-...
9 January 2009
Returning a CLOB from a table: I have a requirement to return a CLOB, to a client app, in the following format AAAAAA~AAAA 6250~AAAAAA HOSPITAL AUGMENTATION~AR~2009 BBBBBB~BBBB 7220~BBBBBB HOSPITAL AUGMENTATION~AR~2009 CCCCCC~CCCC 6252~CCCCCC HOSPITAL AUGMENTATION~AR~2009 (...)
9 January 2009
Moving from solaris to linux: Hi Tom, I have been tasked with migrating a number of databases (10.2.0.3) from Solaris to Linux. None of the databases are over 10gb but they do have a large number schema's. What is the best (recommended) way to migrate them?
9 January 2009
Am I using bind variables?: Hello Tom, I have a question on whether I am using bind variables. I query v$sql view for a particular sql_id as follows: SYS@prod1:> L 1 SELECT sql_id, fetches, executions, invalidations, parse_calls, 2 rows_processed, (...)
9 January 2009
Can a Single SQL do this?: Happy New Year to you, Tom! I need to parse a transaction file. If it's a single transaction it will go like this: T0 <tab> "transaction A" T1 <tab> "transaction A" 100 T3 <tab> "transaction A" T4 <tab> "transaction A" 102 It (...)
9 January 2009
Converted to UTF8 and the display of columns using SQLPlus is longer.: We have converted to UTF8 and now use Putty to access our data through SQLPlus. When you select fields from tables now, you now longer get the display to come out showing exactly how many characters are in that field. It now shows it in byte form. (...)
8 January 2009
Partition split: Hello Tom, When splitting a partition like so: ALTER TABLE my_schema.my_table SPLIT PARTITION WEEK_20090112 AT (TIMESTAMP'2008-12-08 00:00:00') INTO (PARTITION WEEK_20081208 TABLESPACE my_tablespace, PARTITION WEEK_20090112) PARALLEL 4; (...)
8 January 2009
utl_smtp send_mail: How to set importance high and get it as exclamation mark in Outlook: Hello, I am using utl_smtp to send e-mails from an oracle proc, and it works fine. I have been asked a 'nice to have': to set the Importance flag. I have used the Importance parameter in the send, but just got Importance: High as first (...)
8 January 2009
Oracle Resource Manager: We are currently trying to implement Resource Allocation plans on our data warehouse. My question is this: Do resource plans actually limit cpu resources as per defined plans when the cpu utilisation is less than 100%? I mean does it give priority (...)
8 January 2009
Splitting Partition & index creation: I have a table where the last parition was created by june and every data after june'08 was entering into the maxpartition column. Now i need to split the paritions and there are local indexes created on this existing partitions. The (...)
8 January 2009
ora-01652 & ora-01630: <code>Hi Tom, I had encountered few "non Oracle Exceptions" when creating snapshots. MYBOWNER@PROJA SQL> get dly CREATE SNAPSHOT DAILY_SL_SM STORAGE ( INITIA...
8 January 2009
TNS listener, ORA-12500, ORA-00955: <code> Can either or both of these error conditions be generated by sending the TNS listener too many queries, too fast? If so, how fast is too fast? ORA-12500 TNS:listener failed to start a dedicated server process ORA 00955 name is (...)
8 January 2009
It doesnt Work. VARCHAR2(4000 CHAR): Hi, I have five columns as below in my DB col1 VARCHAR2(4000 CHAR) col2 VARCHAR2(4000 CHAR) col3 VARCHAR2(4000 CHAR) col4 VARCHAR2(4000 CHAR) col5 VARCHAR2(4000 CHAR) When i try to save the data with max length, i get the following error. (...)
7 January 2009
Using Oracle collections and result sets: Tom, we are implementing an application using EJB's. We need to be able to insert , update , delete and view result sets from the Oracle database. One way suggested is to use stored procedures and functions and use Java or Session/entity (...)
7 January 2009
questions on redo and undo: Hallo Tom, during christmas break I found time to read Expert Oracle Database Architecture in total. That was great reading, I learnt a lot, thank you ! I have 2 questions on Chapter 9., "Redo and Undo". The first one is on redo for undo (...)
7 January 2009
how to transfer client files to DB server using PL/SQL: Hi Tom, Is there a way to transfer a file from client machine to DB server using PL/SQL? If this is possible, can you show me an example? Thank you very much. Amy
7 January 2009
Monitor health or bottleneck queries hidden in stored procs: Hi Tom, 1. how to profile stored procs? Using dbms_profiler? Is there an GUI verion of dbms_profiler available? 2. how to find execution time or performance of complex stored procs over a period of time 3. Is Apex useful tool for this case. (...)
7 January 2009
Pros and Cons of dbms_redefintion package: Hi Tom, 1. I read that DBMS_REDEFINTION package can be used to redefine tables,indexes,constraints online.What are limitations of DBMS_REDEFINTION package. Like,I read we cannot redefine tables that have mview logs,.. Can i redefine (...)
7 January 2009
Reduce parsing using dbms_sql: Tom, In your book "Effective Oracle by Design", you showed a technique on page 292 to use dbms_sql instead of execute immediate to reduce parsing. As the readers and you may not have the book handy, I am typing the code from your book and then (...)
7 January 2009
Table Export to Foxpro: Hello, I am using Oracle 9i and 10g on different servers. So many times i have to send /export the oracle table data to Foxpro table. For this, i am using the solution provided by you which generates the CSV file and then i imports the data in (...)
7 January 2009
Size of extent bitmap or space header segment: For an 8K Block size database, do we need to allocate a larger extent bitmap or space header segment when adding big datafiles (e.g. 20 GB, 32GB, etc) to a locally management tablespace of uniform size or is the 64K per datafile enough for extent (...)
7 January 2009
IOT pctthreshold calculation: <code>Hi Tom, My block size 8K. My tablespace DATAAPR setting is as INITIAL_EXTENT = 40960 NEXT_EXTENT = 40960 MIN_EXTENT = 1 PCT_INCREASE = 50 I created an IOT tablespace without specifying Overflow tablespace as follows (...)
7 January 2009
Oracle Sequences: <code>Hi, It has always been a great learining experiences reading the text on this site. Now i have my own problem and posting my question for the first time. Hope i make some sense. Can i have same sequence name for 10 different sequences. (...)
7 January 2009
Would you put denormalized columns in a foreign key?: Tom, Without getting into the reasons and justifications (unless you need them for some reason), I am considering denormalizing some columns so that every record in a detail table has values for these columns copied from the related record in the (...)
6 January 2009
Does database trigger hard parse SQL statement?: Hello, I was told recently that Oracle does not do hard parse of SQL in database trigger since some 9i release or patch. I used to think that SQL code should be always wrapped into PL/SQL procedure or package and called from row level trigger to (...)
6 January 2009
Fast Refresh of Materialized View is slower than a Drop and Re-create of Materialized View.: Hi Tom, I had a quick question about why the Fast Refresh of a simple Materialized View subject_mview which is defined on one table, takes much longer than the drop and recreate of the same subject_mview Materialized view, as defined below: I (...)
6 January 2009
Stats update when doing move tablespace or rebuild index: Hi Tom, I had a question regarding alter table move tablespace <tb_name> or alter index <index_name> rebuild. Does the statistics gets updated when we do move tablespace or rebuild indexes. I don't want to loose the stats of the table or (...)
6 January 2009
Using RMAN and Dataguard for Backup and DRA: Hi Tom, I'm supporting a data warehouse that is approximately 750GB in size and grows everyday. I currently use RMAN for backups, but the backup is strictly to tape. Once a week full backup and an incremental each of the other days. The full (...)
6 January 2009
Remove rows from v$session_longops: Hi Tom, I've been using dbms_application_info.set_session_longops for a while and do know, that you are also advertising it's use quite a bit. My biggest problem ist the fact, that a new row in v$session_longops does not seem to get deleted (...)
6 January 2009
comparing data from two months: Hi Tom, I have a table CUST(month date, custno number, cust_name varchar2(50)) and a table TRANSACT(month date, transactno number, custno number, amount number). The two tables are partitioned by range (month). In the table CUST there are the (...)
6 January 2009
How does insert and delete use space regarding to indexes?: Hello Tom I have such as test: SQL> create table t as select * from dba_objects; Table created. SQL> desc t Name OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP (...)
6 January 2009
Error Trapping - Reg: <code>In our database we are having around 200/300 procedures/packages. whenenver there is an error in the error trapping table we want to insert a row with the following contents. 1. Procedure Name 2. Line No of the procedure at which (...)
6 January 2009
INITRANS Cause of deadlock: <code>Hi Tom. I am getting the following errors in my alert log: ORA-000060: Deadlock detected. More info in file d:\oracle\admin\vndb\udump\vndb_ora_1620.trc. The first few lines of the above trace file are as follows: Dump file (...)
6 January 2009
Continue String in new line in SQLs: Hi Tom, I am generating a lot of Insert statements from a source database, which will be executed in Destination database. I am using PL/SQL code to print Insert statements using DBMS_OUTPUT.PUT_LINE from few tables. So because of Max limit, (...)
6 January 2009
RMAN - Full vs. Incremental - performance problem: <code>Hi. I am testing RMAN. I have run an incremental 0 and an incremental 1 cumulative test without having any database activity in-between these test. I am using OmniBack media manager with RMAN. I have three databases. Here are the timing (...)
6 January 2009
Basic Information: Hi Sir, This seems to be a very basic question but can you please explain below terminology with examples and detailed explanation. And also please explain where we can implement this to improve performance with some examples. 1. (...)
6 January 2009
order in a rollup group: Hi Tom, I have a query like this: <b>SELECT col1 , col2 , col3 , SUM(col4) , GROUPING(col1) , GROUPING(col2) , GROUPING(col3) FROM table WHERE ---- GROUP BY ROLLUP (col1, col2, col3) HAVING (...)
6 January 2009
Locking parent job submitting child dbms_jobs: I could spawn a child process from a standalone pl/sql procedure using dbms_job.submit package. Session of calling procedure is ending as soon as it generates the child process. I want the calling procedure session to be alive and running until all (...)
6 January 2009
Rebuilding indexing--space consumption: <code>We have a situation where when we go to rebuild certain indexes within the database, it fails with a "Unable to extend temp segment...". What I don't understand is, before this program runs at night, we double check to make sure there is (...)
5 January 2009
Dyamic Quarter Dates: <code>I have been tasked with designing a dynamic select to return 3 rows based on a date which are the 3 end of month dates corresponding to the quarter in which the first date resides. For example is the first date is 12/18/2000 then the select (...)
5 January 2009
Double Audit: Hi, Tom! I congratulate you on Happy New Year! Can i do audin in table and file?
3 January 2009
Converting row to columns: Hi Tom, Requirement is I have to convert all my rows into columns. <b>Here is an example:</b> CREATE TABLE SERVICE (SERVICE_CODE VARCHAR(5), CONTAINER_SZ NUMBER, CONTAINER_TYPE VARCHAR(2), AMOUNT NUMBER); The above (...)
3 January 2009
SQL Tuning: Hi Tom, I having the following question need to seek for your advice. Recently, I am reviewing the PLSQL statement coded by other developer, I found that, most of the SQL statement they coded are as follow: They uses "EXISTS" instead (...)
3 January 2009
Multiple record set from webpage to oracle database: Sir, <b>Question - 1</b> I am facing a great problem and you please help me. In our web application (Accounts module) the web page (JSP Page) has one header block ? ONE record and Multiple detail records in detail block. One record can easily (...)
3 January 2009
Innovations / Features making life easier: <code>Hi Tom /* 01. This question is hardly technical. 02. So, it could not be version specific. 03. We really missed your Bangalore, India visit (18th Dec, 2008 - AskTom Live) 04. You are answering/commenting questions from the 'very (...)
3 January 2009
Design Question: Self Join: We have a single table that contains all the list of values (not master tables) for each language. This leads to a lot of self joins. Is there a better way to do handle this? Example: <code> CREATE TABLE SYSTEM_CONSTANTS ( SYS_CODE (...)
3 January 2009
oracle sql: i have a string like below param="string1,string2,string3" now i need to display the above strings in a separated rows like string1 string2 string3 like
3 January 2009
Performance issues: Dear Tom Kindly iam working as Oracle DBA for 3 years ,i have no questions but all what i need to guide me for a good Doc for performance tuning as each part in Oracle database iam perfect RAC,RMAN etc all this self study , but in performance (...)
3 January 2009
how to populate date which is in group by clause: Is there any way to tune this query. because converting date to char and again to populate another date variable which leads a performance issue. SELECT DEALER_NUMBER DEALER_NUMBER, TO_DATE('01 '||TO_CHAR(trust_created_dt,'MON YYYY'),'DD MON (...)
3 January 2009
CREATE MULTIPLE TABLES IN A LOOP: I WOULD LIKE TO CREATE MULTIPLE TABLES IN A SCHEMA CONNECTION BASED UPON THE TABLES PRESENT IN ANOTHER SCHEMA..FOR EXAMPLE, I HAVE A SCHEMA SCHEMA1 WHICH I NEED TO ARCHIVE AND I NEED TO CREATE TABLES DYNAMICALLY IN SCHEMA2. FOR THIS I WRITE A (...)
3 January 2009
Partition pruning/elimination -reg..: <code>Dear Tom, Sorry for the delay. 1 CREATE TABLE emp (no number, name VARCHAR2(10) , 2 PRIMARY KEY (no, name)) 3 partition by hash(no) 4 ( 5 PARTITION PART1 TABLESPACE "TS1" , 6 PARTITION PART2 TABLESPACE "TS2" (...)
31 December 2008
Uniform Size on LMT: Tom, I have a LMT tablespace defined as follows: CREATE TABLESPACE ABC_IDX DATAFILE '/ora101/oradata/ABC/ap_idx01.dbf' SIZE 10240064K, '/ora101/oradata/ABC/ap_idx02.dbf' SIZE 4096064K, '/ora310/oradata/ABC/a...
30 December 2008
How to pass a parameter in dmbs_job: Hi Tom, Can you help solved my problem on how to pass a parameter in the dbms_job. What's wrong with my script below: DECLARE jobno number; err_b varchar2(1250); ret_c number(35); BEGIN dbms_job.submit(jobno,'SFC3OWN.P...
30 December 2008
Maintaining tablespaces on external hard disk: Hi Tom, I have a 500 GB external hard disk (more than 400 GB of free space) and a laptop with 80 GB hard disk with little space left.I have installed oracle 10g express edition in the laptop.Can I create a tablespace for my database with the (...)
30 December 2008
How to change repeat_interval for GATHER_STATS_JOB in 10GR2: Hi Tom, How to change the auto stats collection job (GATHER_STATS_JOB) in 10GR2? It is created and scheduled by default to run at 10PM, we like to schedule it to run at different time, say 8PM on Friday and Saturday. I tried this BEGIN (...)
30 December 2008
error on parallel rowid: Hi Tom I have the next from your book expert oracle database architecture section parallel "DIY": SQL*Plus: Release 10.2.0.4.0 - Production on Lun Dec 29 12:03:43 2008 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Enter (...)
30 December 2008
Shrink and coalesce: Tom, What is the difference between shrink and coalesce and under what conditions should we use each of these options? Do these commands lock the objects? Thanks...
30 December 2008
What is async IO ?: <code>Dear Tom, Sorry for asking a unix question in this forum. But the answer to this question will be useful to many readers like me. What exactly is the sync IO or async IO in solaris. From where to where the DBWR copies when the async (...)
13 December 2008
V$SQL_PLAN_STATISTICS: Dear Tom, Does V$SQL_PLAN_STATISTICS shows only the statics about the current (connected) session? SQL> select count(*) from v$sql_plan ; I can get the plan details here COUNT(*) 18540 SQL> select count(*) fro...
13 December 2008
Cursors Vs PL/SQL Collections.: <code> scott@AFRSQA> Declare 2 v_name Varchar2(4000); 3 begin 4 for x in (select object_name from all_objects) 5 Loop 6 V_name := x.object_name; 7 End Loop; 8 End; 9 / PL/SQL procedure successfully (...)
13 December 2008
Redolog information by CREATE INDEX: Hello Tom, i tried to figure out which information is logged (and how it is logged) in the redolog files, if i create an index. I have created a little test table with one field (number) and round about 128 datasets. After that i created a non (...)
13 December 2008
usage of parameters: Tom, <code>create table t1 (key number, col1 varchar2(1)); insert into t1 (key, col1) values (1,'Y'); insert into t1 (key, col1) values (2,'N'); insert into t1 (key, col1) values (3, null);</code> A question about parameters for reusing (...)
13 December 2008
Oracle Text and OLTP: Tom, Is Oracle Text the recommended search mechanism for an OLTP environment? Can reindexing happen on the fly using Oracle Text? For example, suppose I have an index on a blob column which stores all kinds of documents. When I add a new document (...)
13 December 2008
Variable scope for background procedures: Hi Tom, Long time listener, first time caller. I'm involved in writing a couple of PL/SQL packages on an OLTP system that process messages from an Oracle Advanced Queue. Now, these procedures query the data dictionary for table structures each time (...)
13 December 2008
conditional asking: well, maybe there is anwser somewhere on asktom site but i was not able to find any --even have what to search. do you have any idea how to ask in sql for something like: if there is any record of type a or b then take the last one of them, if (...)
13 December 2008
Dropping tablespace error: Hi, I was having a problem of dropping a tablespace.When i was about to drop a tablespace it prompted an error stating "An attempt was made to drop a tablespace which contains the segment(s) for the LOB columns of a table but does not contain (...)
13 December 2008
In table (A,B) update value of B from sequence so that A->B is one to one: I have the following table TEMP; and a sequence TEMP_S CREATE SEQUENCE TEMP_S; CREATE TABLE temp ( VCOL VARCHAR2(1), NCOL NUMBER); INSERT INTO temp VALUES( 'A', null); INSERT INTO temp VALUES( 'A', null); INSERT INTO temp VALUES( 'A', (...)
12 December 2008
How can I track failing sql statements: Hello Tom; I am running a third-party application which returns the error ORA-00942 table or view does not exist. Application returns immediately upon returning the error. What I want to do is to find out the sql statement that caused the (...)
12 December 2008
Backup/Recovery: <code>I am recommeding a Backup/Recovery Strategy to be used by multiple clients. The question was asked why not use O/S backups instead of RMAN? I explained that this is not recommended by Oracle, but could not tell them exactly why. Could you (...)
12 December 2008
Strings of Zero Length Not Equivalent To NULL: <code>Tom, As we are planning to upgrade to 9i version, I am reviewing the Oracle 9i migration documents and in one of the topic, I read "Strings of Zero Length Not Equivalent To NULL". "A string of zero length ('') is not equivalent to a (...)
12 December 2008
Advanced Queuing & PL/SQL Notification: <code>hello tom, I hope you don't get angry now, if I "redirect" you to </code> http://asktom.oracle.com/pls/ask/f?p=4950:61:166184132340407832::::P61_ID:442119935770 <code> there is MY MOST important question. TODAY I've got your book - but (...)
11 December 2008
Whats the difference between the v$sql* views: What is the diff between: v$sql v$sqlarea v$sqltext v$sqltext_with_newlines when I query v$sql joining with v$session and filter by a SID I get more than one SQL (expected) but if I join v$sqltext with v$session with same (...)
11 December 2008
varying elements in IN list: <code>I am using Oracle Report Builder 3.0.5.8.0 . While using bind refernce i have created a dynamic list of values to give input for that particular bind reference , actually i want multi selection from LOV for that bind refernce is it (...)
11 December 2008
Performance issue: Question: I have a folowing 3 queries . Issue with 1st query its take long time to ran. Becically its call to others Function/Query see query 2,3 both queries in called by(query 1 "select aal_lom_get.get_external_code (aal_lom_get.get_parent_id(cm.l...
10 December 2008
Database alert log switches: Tom, I am trying to learn a bit more about databases and the redo log area. Oracle suggests that database servers are tuned to switch every 15 ? 20 minutes, 20 minutes being the optimal switch time (as per metalink notes) I have two questions, (...)
10 December 2008
NO_DATA_FOUND in Functions: <code>Tom, We've just migrated from Oracle 9.0.1 to 9.2 and, coincidence or not, I'm facing a problem I had never faced before. The NO_DATA_FOUND exception is not being raised from my PL/SQL functions anymore!!! If I put a exception block to (...)
10 December 2008
ERROR MESSAGE ORA-04030: Pl suggest how to solve the problem related to ORA-04030. Thanks Ajay Goel
10 December 2008
Problem with TIMESTAMP WITH LOCAL TIME ZONE data taype: Hi! I have a problem using TIMESTAMP WITH LOCAL TIME ZONE data type. E.g: CREATE TABLE messages ( message_id NUMBER(19,0) NOT NULL, message_time TIMESTAMP WITH LOCAL TIME ZONE NOT NULL, CONSTRAINT (...)
10 December 2008
Extract a repeated string from a field: Tom, First off thanks for all the times in the past when you have helped me by already answering my question that somebody else had asked first! I am trying to extract a list of email addresses that have been entered in a comment field in a (...)
10 December 2008
Error in External table population: Tom . I am running into a problem with External Tables. Please advise. I have created the following statement for the external table . <code> DROP TABLE tab_ext ; CREATE TABLE tab_ext ( col1 INTEGER, col2 (...)
10 December 2008
i need to find the table name and column name using column values.: PL/SQL I have a specified column value like " moe_2002_acso_link" . form this i need to find the table name and column name where the data presents ?? im not sure is it possible or not !! but still ny one can help me out this! like i need (...)
10 December 2008
Object Type Performance in Bulk DML: I started a new job with a company that does not use methods in an object type. When I queried about the reason for this I got the following response: We do not allow methods other than constructors to be defined for object types. There are two (...)
10 December 2008
Resizing undo tablespace: Hello Tom: After some operations in my database my undo tablespace has grown to a very big size. I would like to resize it to give more space to the O.S. Which is the quickest way to do it. If i try a resize of the datafile i get the ORA-2397 (...)
10 December 2008
Moving table partitions: <code>Tom, Is it possible to move a table partition from one tablespace to another in the same way it is now possible to move a table from one tablespace to another ? </code>
10 December 2008
dba responsibilities: <code>What are the responsibilities does a dba have to shoulder in an organisation? Does he/she require to know development side also in the context of the present day job market for DBAs? </code>
10 December 2008
Performance Tuning: <code>Hi Tom, Few weeks back, I saw your your remarks about the new book - "Optimizing Oracle Performance" by Cary on this site's home page. I immediately ordered that book. Now I am reading this. Every line I read, I get really surprised about (...)
10 December 2008
Key-preserved table concept in join view: <code>Could you explain me about Key-preserved table concept in join views.i know that a table is ket preserved if every key of the table can also be a key of result of the join. but i not understand meaning of this. Thanks (...)
10 December 2008
Updating a view: <code>Hi, I am trying to update a view, actually just one really big table(fact) that is part of the view. The view consist of a one really big table (fact) and another inline view joined together. The inline view does full outer join of two (...)
9 December 2008
PL/SQL Procedure returning a Ref-Cursor with "embedded Cursors": <code>Hi Tom, Luckily I'm in the position to ask a question right now ... I've been searching the archives, but didn't find a solution ... My problem is the following: I've a query like ... SELECT U.UserId, U.LoginName, U.PassWord, (...)
9 December 2008
Making a URL call in PL/SQL: <code>Tom, Is it possible to construct a PL/SQL stored procedure that makes a URL call. Then have the resulting HTML displayed. Something like... procedure helloworld is begin htp.htmlOpen; htp.BodyOpen; htp.p('This would be cool, if (...)
9 December 2008
reclaim space: <code>Dear Tom, I have a table which has more than 90% of the rows deleted and never will be used. I would like to reclaim the space. Currently it has 133 extents (LMT with uniform size of 512K). It has data less than 512K. What is the (...)
2 December 2008
Automatic Storage Managemen (ASM) and RAID, etc.: I'm trying to understand best practices for speccing a system using the new ASM technology in 10g. Many of the examples use SANs or various kinds of volume managers which provide storage and incur substantial overhead to do striping, ensure (...)
2 December 2008
Intermedia text indexing: <code>I have a table with a varchar2 column (Title) and a clob column (Body). Can i perform a select with the contains command for searching on each columns ? For example i want to search the word 'Oracle' on Title and Body. Thanks. (...)
2 December 2008
Is it safe to use ROWID to locate a row?: <code>Hi Tom, I'm looking at a client application which retrieves several columns including ROWID, and later uses ROWID to identify rows it needs to update: update some_table t set col1=value1 where t.rowid = :selected_rowid Is it safe to (...)
2 December 2008
doubt about ROWDEPENDENCIES: <code>Hi Tom, With this feature we can store scn at row level. suppose if we have one table with rowdependencies and it's having 2 rows. create table t rowdependencies as select rownum r from dict where rownum < 3; -- these 2 rows (...)
1 December 2008
Oracle wait events: <code>One of our systems has serious problems on certain days. I am looking at some oracle views (v$system_event, v$session_event & v$session_wait) to make sense of the info gathered. Top waits now are: db_file_sequential_read (...)
1 December 2008
RMAN: Hi Tom, if i have configured recovery catalog for one of my target database suppose PROD db i want to know oracle will write information in the source control file. As recovery catalog itself contains the information about the target database. if (...)
1 December 2008
pinning packages: <code>Question on pinning packages using DBMS_SHARED_POOL.KEEP. A) Package A is pinned in memory at database startup (using a startup trigger) B) Package A is executed C) Package A is modified and recompiled Does Package A (...)
28 November 2008
data files: <code>Hi Tom, We're product based company and we've mid range ERP systems. The system has financial accouting, sales, production ,hrms, and inventory modules. I've created separate tablespace for each modules and given "autoextend on" for (...)
28 November 2008
ENABLE ROW MOVEMENT: <code>Hi Tom I have seen your posting on ENABLE ROW MOVEMENT which is available in 10g. It looks a very nice option since we can relocate and reorganize the heap tables without any outage since it does not invalidate indexes. But is there any (...)
28 November 2008
sql_trace vs. event 10046, level 4: <code>alter session set sql_trace=true;</code> <code>alter session set events '10046 trace name context forever, level 4';</code> Are these equivalent? I pored over TKPROFs generated from traces from each, and they seem the same, but maybe I'm (...)
28 November 2008
how to dynamically generate cursors with table names that come from parameters.: I am a java developer but I've given a request to generate several 1800 bytes fixed length files, which have the same format but are only different on the select query. As the format keeps changing I though that the best way to generate them is (...)
25 November 2008
how to use dbms_stats.gather_databse_stats: <code>Tom: i try to use dbms_stats to give me a report of what statistics is missing, what i do is: declare a dbms_stats.objecttab; begin dbms_stats.gather_database_stats(OPTIONS=>'LIST EMPTY',OBJLIST=>a); end; after that how can i know (...)
25 November 2008
Replace several comma by one comma: Hi , I have string like the following. 1.,Accenture, , ,EDS,Sun,Training Partner 2.United States,,,, 3.Canada, I want to have the string like the following as a result of the query. 1. Accenture,EDS,Sun,Training Partner 2. United (...)
25 November 2008
v$timer doesn't seem to match the tim field in trace files: Hi, Skip the paragraph below if you don't want a detailed explaination of what I'm trying to do. The paragraph after it explains the problem. I am trying to trace an application that uses a mid tier as well as multiple direct connections. I (...)
25 November 2008
removing control characters from text: Is there a routine in Oracle that can test for and remove unwanted characters in a text string, ie control characters?
25 November 2008
Question regarding OLS in 10G: <code>Tom, The organization I work for has not upgraded to 10G yet. I was just reading a blog entry here </code> http://technology.amis.nl/blog/index.php?p=774 <code> regarding column-sensitive security in VPD in 10G. Is this information (...)
25 November 2008
Reuse of cursors in JDBC: <code>Tom, First of all thanks for giving so much time to this forum. We appreciate all your excellent work. In your books I read a lot about bind variables, reusing or cursors etc. According to you the correct way to use cursors is to parse (...)
25 November 2008
Username's and Passwords: <code>Seems similar to a couple of other questions that you have been asked, but not identical enough! I have to implement "C2" security features within our application. Unfortunately the 3rd party suppliers are not yet ready for Oracle 8 let (...)
25 November 2008
SYSDATE behavior in SQL and PL/SQL: <code>Hello, My quess: there are two different SYSDATE functions ? one defined in STANDARD package and another one somewhere ?inside? Oracle. Example: SQL> select * from dual; D X SQL> select sysdate from user_objects where (...)
24 November 2008
Q: nologging option: <code>tom, from oracle documentation nologging can be used with following operation: alter table...move partition alter table...split partition alter index...split partition alter index...rebuild alter index...rebuild (...)
24 November 2008
V$session_wait: <code>Hi tom, Here is the query SQL> select segment_name,segment_type,owner,tablespace_name from 2 dba_extents,v$session_wait 3 where file_id=p1 4 and p2 between block_id and block_id + blocks -1; SEGMENT_NAME (...)
22 November 2008
Can I Pass a nested table to Java from a pl/sql procedure.: <code>Hi Tom, Can I pass a nested table from pl/sql procedure to java , I am using JDBC.If yes can you please provide me a simple example or point me to a documentation on this.My developers don't know how to do this. (I have seen an example on (...)
19 November 2008
Limit CPU Usage: <code>Tom, DSS Environment. Users log in with their own user account to the application. When they submit a particular job in multiple sessions, the CPU Usage shots up on 8 CPU Server(NT). Infact, it goes like this. One user submits say 7 (...)
19 November 2008
dbms_output.enable does not work: <code>Hi Tom, I have dbms_output.enable(1000000) in my procedure but to see all the messages thrown by dbms_output.put_line I still have to issue the command "set serveroutput on" at the sql plus. Isnt it true that dbms_output.enable is the (...)
19 November 2008
rebuild index for locally managed tablespace with AUTOALLOCATE option: <code>We have a locally managed tablespace with AUTOALLOCATE option. I know that you do not recommend this option, but it is not my decision. We were loaded a large amount data from a legacy system into an Oracle database. The Oracle DBA (...)
15 November 2008
Data Guard: Hello, Tom Can you explain guaranteed protection mode of the Data Guard? What happens with primary database instance in this mode if standby database is unavailiable? Documentation says in this case all changes to primary is prohibited. (...)
15 November 2008
How to allow special characters to store in DB: <code>Hi Tom, Many thanks for your help. I need to store special characters like single quote ('), double quote (''). / etc. In the input, there will be only one quote. But to store single quote, we need to give two quotes. Is there (...)
15 November 2008
Manipulating Sort Area Size: <code>Hi Tom, The value specified by SORT_AREA_SIZE is assigned on a per user basis. Therefore, if it is set to 1M, and 25 users connect to the database, each user's Server Process will consume 1M (25M in total). The questions are: 1) The (...)
14 November 2008
About Temporary tables: <code>Hi tom... Actually, ive never created any temporary table till now, i just like to ask if when creating the table, on what tablespace will the temporary table be stored? i need to consider the space to make sure that the creation is (...)
12 November 2008
ORA-4045: When I am tring to do logminer configeration The Ora 4045 error occurs. SQL> execute dbms_logmnr_d.set_tablespace('logmnrts'); BEGIN dbms_logmnr_d.set_tablespace('logmnrts'); END; * ERROR at line 1: ORA-04045: errors during (...)
12 November 2008
multi-pass sort: Dear tom, I have a question, how does multi-pass sort work, with reference to temporary tablespace. the following sizes are included to make the example clear. Suppose the sort_area_size is 1 mb and i have a table t of 1gb. I give the query as (...)
12 November 2008
oracle parameter distributed_locked_tiemeout?: hi, I am getting an application error: ORA-02049: timeout: distributed transaction waiting for lock error? How do I keep the ORA-02049 from happening?how to modify this parameter?
12 November 2008
Is there a guideline to shoeveloper privileges and roles effectively?w how to manage d: Is there a guideline to show how to manage developer privileges and roles effectively?
12 November 2008
Question on CONCAT: Hi Tom, Good Morning... In my Procedure. <code>CONCAT(<SQL_QUERY>,<CASE_STATEMENT>)</code> is not working but <code><SQL_QUER>||<CASE_STATEMENT></code> is working Why??
12 November 2008
Index ignored when selecting multiple columns: Hopefully this is an easy one. I would like to know why an index is ignored when the SELECT clause contains multiple columns. It was my understanding that the index points to the position of the row so that whether you select one column or ten (...)
12 November 2008
security of full export or user export: Tom - a full export is being provided to a new vendor so they may "do something" with it. A concern has been raised that the passwords for all the important schemas will be available in the export - and therefore compromise security of the original (...)
12 November 2008
Problem to perform TSPITR: <code>I was performing TSPITR with RMAN but got the following error during compiling command "restore". RMAN-06026: some targets not found - aborting restore RMAN-06023: no backup or copy of datafile 3 found to restore RMAN-06023: no backup or (...)
11 November 2008
SQL*Loader problem with a quoted, comma separated data file: <code>Tom, Hopefully I have provided all the statements you'll need to duplicate this problem below. I am trying to use SQL*Loader to put data into my table. The data is comma separated and each data field has double quotes around it. The (...)
11 November 2008
scheduling backups: <code>1.How do I schedule Rman Online backup scripts. 2.How do I write a script for automating online backup (using ocopy on NT).My script should run from OS prompt.I want to schedule this scripts at OS (NT) level . (...)
11 November 2008
Procedures, roles and grants: <code>Hi Tom, We are using 2 schemas, user1, user2. User1 has user1_table, and created role role1. The following was issued: grant select, insert, update, delete on user1_table to role1; grant role1 to user2 User2 created private synonym (...)
11 November 2008
Error ORA -00904 in MERGE command: <code>Hi Tom, Thanks for the excellent work you are doing. Here is my question: SQL*Plus: Release 10.1.0.2.0 - Production on Fri May 7 10:29:21 2004 Copyright (c) 1982, 2004, Oracle. All rights reserved. Connected to: Oracle9i (...)
11 November 2008
Case and Decode and others: <code>Tom 1.I usually run into a situation where I run it queries against huge tables, and they run for an hour. some times when I want to discontinue the query , the only way is to kill the sqlplus window, i.e. kill the session. Is there (...)
11 November 2008
Locatin of listener.ora and tnsnames.ora files: <code>Hi Tom, As far as my knowledge goes I understand in Solaris filesystem 'listener.ora' and 'tnsnames.ora' files are in /var/opt/oracle/ area and for HP and Digital machines these files are found in /etc/ area by default .Is there anyway in (...)
11 November 2008
Cost /Rule based Optimizer behaviour: <code> I am on NT 8.1.7.0.0. with server of 8 CPUs. Yesterday I was trying with a query using dba_data_files and v$datafiles and after running for say about 7 min query used to quit on ORA-4030. Process memory exhausted. Going through your book (...)
11 November 2008
uninitialized collections: <code>Hi Tom, I wonder if you could help me with the following? I am trying to test a small package that will be called by java to insert into a database. The connectivity will be using JDBC thin client. I need to test the package to be sure (...)
11 November 2008
oracle is doing full table scans on a table with raw column and the column is primary key: Hi Tom, Thanks for your continuous support. We have a table with blob data type as one column. We are using secure file option as we are using 11g. We kept the blob column in a seperate tablespace. In the main table we have a column (...)
11 November 2008
DISTINCT vs, GROUP BY: <code>Tom, Just want to know the difference between DISTINCT and GROUP BY in queries where I'm not using any aggregate functions. Like for example. Select emp_no, name from Emp Group by emo_no, name And Select distinct emp_no, name (...)
3 November 2008
Remove RTF Tags from a Text Field Containing RTF: <code>Hi Tom, In our database we have some varchar2(4000) fields (could later be a clob). Where we store RTF data. I would like to know if there is anyway I can remove the RTF tags returning only the text again. For Example a Function RTFtoTEXT (...)
3 November 2008
What is the reason that implicit cursors are faster than explicit cursors: <code>Hi I tested and satisfied myself that implicit cursors are better than explicit cursors. I did tkprof report , and what you said is right. Now can you tell me the reason as to why the implicit cursors are faster, what are the internal (...)
3 November 2008
rman question: thomas, happy holiday. I have question(s) related to rman 1. what is the difference between - to back up the current control file and to backup up control file copy(backup controlfile copy) 2. when using either of above command (...)
30 October 2008
Deleting duplicate records without using rowid and rownum: <code>Hi Tom, If there is any duplications of records in a table, we know how to eliminate the duplicate rows using rowid. But is there any possibility to delete the duplicate records in a table without using rowid and rownum. my friend who is (...)
30 October 2008
Restoring Stats and Execution Plans: Hey Tom, We recently had a query (using bind variables) whose plan flipped after our nightly stats gathering process due to bind variable peeking. We were hoping that by restoring stats to the previous day (on all the tables included in the (...)
30 October 2008
ORA-00001: unique constraint (GIGAGAME.PLAYERINFO_IDX2) violated: Hi Tom I have problem need to help My Company RD TESTING FIND THE PROBLEM ORA-00001: unique constraint (GIGAGAME.PLAYERINFO_IDX2) violated HOW CAN I RESOLVE PROBLEM Tks schema ??? DROP TABLE PlayerInfo; CREATE TABLE PlayerInfo ( (...)
30 October 2008
Package variables values within trigger when adding column with default value.: Dear Tom! My question is about operator: alter table foralt (add y number default 0); Problem: trigger on foralt depends on package variables and it works differently in 2 cases: 1. update foralt set y = 10; 2. alter table foralt (add y (...)
30 October 2008
db cache advice: <code>How are the ON, READY and OFF values supposed to be used ? At first I had the impression I had to do: OFF -> READY -> ON, so READY would allocate memory to be used. Should I do: OFF -> ON -> READY (to maintain memory allocated) -> ON ? (...)
30 October 2008
RMAN Catalog vs. Control File and Misc RMAN: Tom, What are pros/cons of using an RMAN Catalog vs. Controlfile Repository (for 10-30+ databases)? Can you run reports on RMAN Catalog to show success/failure of all backups? If you have RMAN backup sets for a given database, but you lose (...)
30 October 2008
Spfile Question: I m a beginner in field of oracle . Consider a spfile , now if i make changes to the parameters in this during the running instance (using alter system set parameter....), will this change be applied in current running instance or will it be (...)
29 October 2008
Move tables with all of the objects and data.: I created tablespaces with SEGMENT SPACE MANAGEMENT to "manual" instead of "auto". I need to delete some of the data and then shrink some of the tables. What would be the safest way of moving all of thhe tables, data and objects out of the incorect (...)
29 October 2008
How to tune access of "fat" tables: Hello Tom, I'm working at a client's site who has a data warehouse with partitioned fact tables with about 400 columns. Performance is not too good and I am trying to optimize the system a bit. The client told me that most of the columns are (...)
29 October 2008
PLS-00436: implementation restriction error when using forall in update: <code>I have a object as follows: create or replace type claims_only as object (fordnr varchar2(15),glaeubigernr number,fordergnr number); and I have a a collection as: create or replace type claims_only_collection as table of claims_only; I (...)
29 October 2008
Trying to select rows which got certain attributes: Hi I have the following situation. My table got the following data SR_WID activity_type activity_subtype 1 Maintenance Break fix 1 Install startup 2 Install startup 3 Install startup (...)
29 October 2008
Date Calculation: <code>Hello Tom, I was just wondering, as to how do you calculate the time which is displayed in "AGE" column of the first page. because, I see, it in many formats as 3hrs, 9 months old, 2.3 years old, 19 hrs old etc. I'm a new commer to oracle (...)
29 October 2008
Bind Peeking Mechanism in 11g: Hi Tom, I have a question concerning bind peeking in 11g. I read that 11g uses bind-aware cursor matching, but all examples I found are based on a tables with a skewed columnn and the query selects a single value (eg in Performance Tuning Guide, (...)
29 October 2008
SQL*Plus IN/LIKE Capability: <code> Tom, In sql*plus we can do an IN... select * from x where animals IN ('DOG', 'CAT', 'RAT') / Or we can do a LIKE... select * from x where animals LIKE ('%OG') But if we want BOTH we have to do this... select * from x (...)
29 October 2008
Monitoring v$archived_log history: Tom, Because we have just implemented Data Guard, my attention has been more focused on redo generated, archived logs, archived log switches, etc. Now I know I can get REDO history from STATSPACK correct (e.g. track over years and/or during (...)
29 October 2008
Database Backup Strategy: Tom, Because of a situation with one of our production databases we are in the midst of rethinking our entire database backup strategy. It seems that regular, routine test recovery of a backup from tape for all production databases is the only (...)
29 October 2008
Logic behind the DENSE_RANK().: <code>Hi Tom, EVEN THOUGH BOTH THE QUERIES LOOK'S IDENTICAL WHY THE 2nd QUERY FUNCTION's DIFFERENT SELECT DEPTNO,EMPNO, DENSE_RANK () OVER(PARTITION BY DEPTNO ORDER BY EMPNO ) RNK FROM EMP DEPTNO EMPNO RNK 10 7782 1 10 7839 2 10 (...)
28 October 2008
Tuning Methodology: Dear Tom, How to set performance metrics for an application ? Let's say we have to design a new application and benchmark it. How do we go ahead with it ? Is it really a vague question? Thanks & Best Regards
28 October 2008
Normalization of the database: <code>Dear Mr. Tom, I am in big dilemma now a days. We say that we should normalize data to the most possible level. But when web enabled applications come into picture, i have seen a general tendency to denormalize the data just to increase (...)
28 October 2008
Space difference between dba_segments, dba_tables, show_space: Question for Tom - Tom - I don't understand this huge space report dicrepancy. I have a tablespace that is a size of 5.5 GB SQL> select sum(bytes)/1024/1024 from dba_data_files where tablespace_name 2 = 'PDATA'; (...)
28 October 2008
Group by behavior in 10GR2: <code>In 10.2 when a query is run with GROUP BY it does not order the output by the grouped columns. In earlier versions, the output was always sorted by the grouped columns even though there was no ORDER BY clause present. The GROUP BY clause (...)
28 October 2008
Design: <code>Hi Tom We are trying to design our database to accomodate dynamic alteration of our schema by providing user defined columns. The client should be able to add columns to tables without altering the base schema. Example If we have a (...)
28 October 2008
Primary and Foriegn Key: Hi, I am new to Oracle. How do I find out a primary might be a foriegn key for another table? Is there table diagram in Oracle? For example: I saw "Customer ID" in a table, I want to know "this "Customer ID" is linked to what other tables, how (...)
27 October 2008
A tough status problem needs help: Tom, I believe I did NOT make this thru last time(my computer crashed last time right after submit this question), and I still did NOT receive your response, so I give it another try, please ignore this if you already received this very same (...)
27 October 2008
"unhandled exceptions" a problem?: Hello I have a DBA that is claiming that by allowing a PL/SQL procedure to allow an unhandled exception (i.e. return the exception to the host program) somehow causes a problem. He is claiming that doing this "pushes the error to the database (...)
27 October 2008
Performance tunining in 10g using STATSPACK utility: Hi tom, i'm tried to install statspack utility in 10gdatabase by running the following scripts. @ORACLE_HOME/rdbms/admin/spcreate.sql installed STATSPACK in sysaux tablespace. and dynamic performance views are created in (...)
27 October 2008
Saving searching and loading pdf documents: Hi Tom, We are getting from one of our custometrs PDF documents that should be stored in database 10204 on hp-ux. 1. I would like to know how to load and save those files into the database ? 2. Is there a possebility to index those PDF (...)
27 October 2008
system privilege cascade?: Sometimes I grant a user 'create any' system privilege with admin option. As far as I know, if I revoke the system privilege from the user, it does not cascade to any privileges granted by that user before I revoke their privileges. I was told (...)
27 October 2008
Retreiving only paired rows in a table: I created a memory table (called BudExp) using WITH statement and got something like this in my data set: Dept_ID Dept_Name TYPE ---------- ---- 101 Marketing Budget 101 (...)
27 October 2008
Cloud Computing: What's your view on cloud computing and how will it affect the future of relational database, especially Oracle? Will cloud render most of the DBA jobs obsolete?
27 October 2008
Unused Index(s) for a specific Time?: <code>Hi Tom, I want to drop those indexes which are not used in the last 2 months so to reclaim space. For example There are two indexes on scott.emp on the empno and the other is on deptno. While the application is only using empno index but (...)
27 October 2008
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [___intel_new_memcpy+40] [PC:0x268F3C8] [ADDR:0x14] [UNABLE_TO_WRITE] []: Hi, I am getting the follwoing error while just selecting records from a view "ORA-03113: end-of-file on communication channel" And when i check the udump location, it has created a trace file for every execution of a query. (...)
27 October 2008
Client connection: <code>Hi Tom, Thanks for your previous help. I have oracle instances in two different NT boxes both are behind firewall. When use any client tool like SQLPlus or Designer..to connect to those instances i am getting disconnected every 5 minutes (...)
27 October 2008
What does plsql_optimize_level parameter entail?: Tom, Do you have any details about what the optimization techniques are for the plsql_optimize_level parameter? I just read an article that says "this optimisation turns cursor-for-loops into implicit bulk fetches". Very nice to know that. I'd (...)
27 October 2008
How to refresh Materialized View when the changes are made in the B tables !: <code>Dear Tom, Hi ! How are you doing ! Thanks very much for your Kind help again and again. Sometimes I wonder if you were not there what would have happened to my role. My sincere thanks and Regards to you. Ques) This Quest is (...)
26 October 2008
Database Statistics: <code>Oracle 10g has introduced new data dictionary views for table and index statistics (DBA_TAB_STATISTICS and DBA_IND_STATISTICS). The documentation ("Database Reference" manual) appears to only mention their existence and structure. Most of the (...)
24 October 2008
DBMS_RLS package: <code>Hi Tom, We had a specific requirement where in we needed to screen some of our table rows based on the the user who has logged in. Of course the first glow in any one's brain would be DBMS_RLS. But our DBA gave the following reasons (...)
24 October 2008
copy command: <code>hello Mr Tom, i have 5 tables and a stored procedure which collects certain data from all the 5 tables and dumps into a sixth table called export table. i want to copy this export table data into a dbf file .. so thru sqlplus i can use (...)
24 October 2008
problem in execute immediate using CLOB datatype: <code>Hi Tom 1) I've problem executing a native dynamic sql using clob data type.Then i referred the docs its say native dynamic won't support CLOB datatypes. Below i've give the table , pl/sql block and error. ashiq@RMSREAL8> desc a (...)
24 October 2008
New Features in 11g: Tom, Oracle introduces Beta version of Oracle 11g. What are the new features of Oracle 11g? Please compare 11g with the previous versions. thanks Regards, Dawar Naqvi
24 October 2008
OT: Any chance to see you in Italy?: <code>I noticed in the last months you travelled across Europe... any chance to see you here in Italy? I could start programming the budget... Thanks, Andrea </code>
23 October 2008
remote batch job get ora-02049 when update our table through db link: <code>we have a table in database A there is a batch job in database B update the table through a db link. the batch job's process: select to look for the record exist or not if exist update otherwise insert.. then they get ora-02049 (...)
22 October 2008
Oracle standby database hardware/software requirements: <code>I know primary and the standby database need to be the same base release level. But, do they have to be the same edition of the database - (primary enterprise edition and standby standard edition), and the same O/S - (primary on SUN Solaris (...)
22 October 2008
date query: <code>Tom: 1. I had a problem with a date query using the operator 'between'. WHen I do Select * from table where date is between '01-JAN-02' and '17-JAN-02' it does not give me the records marked with a date '17-JAN-02' I have to (...)
22 October 2008
Parse and Execute: SQL Tuning: Tom, Thank you for taking the time to answer my question.The following query seems to be using bind variables yet -according to TKPROF- it is being parsed 18722 (the same number of times is being executed). Since bind variables are being (...)
22 October 2008
Spawn Jobs from a Procedure that run in parallel: <code>I have a table that contains 2 million transaction records. A procedure reads these transaction records and creates an output record in the RESULT table. Usually for each record in the TRANSACTION table there is a record in the RESULT table. (...)
22 October 2008
Parallel Processing: Without using dbms_job,dbms_Scheduler can invoke two functions f1,f2 at the sametime from one session. select f1(x),f2(x) from dual; In this scenario, f2 gets invoked after f1 gets completed. I tried all possible combination but I was (...)
22 October 2008
Business Logic - PL/SQL Vs Java - Reg: <code>Hi Tom With oracle 9i where do u suggest to write the business logic PL/SQL or Java. Which is faster/flexible/portable. Is oracle going to support PL/SQL in future. Regards </code>
22 October 2008
Full sql text from v$sqltext: <code>Hi, When querying v$sqltext the sql statement is broken up into "pieces". I want to write a query that will show all of the text as one statement and not broken up by pieces. So, insead of showing: piece sql_text -------- 0 (...)
22 October 2008
Real Application Clusters: <code>Hi Tom You are doing a great job by providing solutions and help to Oracle users. I want to ask you about 9i RAC, could you please explain how they work in delivering 24/7 database availability. Thanks Basharat Mehmood MashreqBank (...)
21 October 2008
How to preventing front-end users accessing the db via sqlplus , etc: <code>I have read the your comments on this issue. What do you think of this idea? When the user logs on to the front-end the code assigns a password-protected role to the user allowing access to the application tables. If the user logs on via odbc (...)
19 October 2008
Resize Undo Tablespace / Shared-Dedicated Server: <code>Hi Tom, 1. In my database UNDO_MANAGEMENT = AUTO, but the size of UNDO_TABLESPACE has grown to 6GB. How do i reduce/shrink it ? Do i need to manage the size of UNDO segements or anything ? 2. Whts the difference between Shared and (...)
19 October 2008
Index Organised Tables Vs Ordinary Tables: <code>Hi Tom I am having two tables 1. CREATE TABLE NEWT ( X NUMBER (10) NOT NULL, Y VARCHAR2 (40), PRIMARY KEY ( X ) USING INDEX TABLESPACE SYSTEM PCTFREE 10 STORAGE ( INITIAL 65536 NEXT 8724480 PCTINCREASE (...)
19 October 2008
SQL Injection: <code>Tom, Not so much a question as a cool SQL injection article I wanted to make you aware of. </code> http://www.unixwiz.net/techtips/sql-injection.html <code> Although the article details a white-hat-hacker SQL-injection attack on a MS (...)
19 October 2008
Returning a Java Array into PL/SQL via a Java Stored Procedure and vice versa: <code>Do you have any sample code to show how a Java Array can be passed through PL/SQL and how a PL/SQL Array or record could be passed to a Java Array via a Java Stored Procedure? Thanks, John Perkins </code>
19 October 2008
grand total: <code>Dear Tom, I have a query like this, SELECT vplcode, SUM(rated_flat_amount)/COUNT(*) amt FROM tap_rtx_009 WHERE vplcode = 223 GROUP BY vplcode, start_d_t, actual_volume, rated_flat_amount HAVING COUNT(*) > 1 (...)
18 October 2008
Provide dynamic order by for "hard" cursor: <code>I need guidance as to the order by clause for a hard refcursor Unless I hardcode the order by clause the order by gets ignored when fetching from the cursor. Can I achieve dynamic ordering on ver 8.1.7 or any higher version ? I can use (...)
18 October 2008
Library Cache: <code>select sum(pins), sum(reloads) from V$LIBRARYCACHE SUM(PINS) SUM(RELOADS) 287980714 640661 This means that only a small amount of sql is not being held in the library cache.(which is a good) As we are not fully using bind variables (...)
15 October 2008
Database trigger: <code>Hi Tom, hope you can help me out. The problem I have is this: I need to turn the SQL trace on for a specific user. In order to do so I've created a database trigger, which turns tracing on if the user is SADMIN. But, when SADMIN tries to (...)
15 October 2008
SQL query: <code>I was asked to write a following query. select all the employee salary whose salary equals oldest employee in the organization. I tried using sub query, multi part query but interviewer was not happy with the answer. caould you please (...)
15 October 2008
What is the meaning of schema?: <code>Hello Mr. Tom i read so many book for oracle and java and every book speak about schema but did not explain the actual meaning of it.once give the meanning of "user and all objects it own".other give the meaning of " Structure of table".So (...)
15 October 2008
Use of package OWA: <code>Can I use the procedure init_cgi_env in the package OWA? </code>
15 October 2008
NOT IN in SQL: <code>Tom, How bad will be the performance of query if NOT IN is used.Is there any other way of writing a query not using NOT IN. Thanks, Dan </code>
13 October 2008
Data Archival and Retrieval: <code>Dear Tom, I have a Data Archival and Retrieval requirement. The requirement is: after 2 years from the transaction-closed date, the data has to be archived. It's not the entire data. Only some 15 columns from a combination of 8 to 10 (...)
13 October 2008
using dimensions for query rewrite: <code>Hi Tom, I have one question about dimensions. Is a dimension some metadata about dimension tables in my star/snowflake schema, or can dimensions be used for query rewrite. Can you briefly explain (or provide some example)? Thanks a (...)
13 October 2008
Is there a way to convert the 0.12 to 0.15 , 0.16 to 0.20 etc decimals: <code>Hi I have the following data 1.11 0.11 0.99 0.14 I need to round this value to the following values 1.15 0.15 1.00 0.15 can you help me out in getting a solution for the same. Thanks in advance. </code>
10 October 2008
can we recover data block which is corrupted: <code>can we recover data block which is corrupted by dbms_verify utility. when we are no not using rman. because in documentation they say fix_corrupt_blocks does not repair but mark them corrupt. thanks </code>
10 October 2008
Generate XML in desired format: <code>Hi Tom, How do we generate XML in desired format using PL/SQL? For example, This is my master table... SQL> create table inv_master(inv_no number(10), 2 inv_date date) 3 / Table created. This is my detail (...)
9 October 2008
Full Database export and import : v8.1.6: <code> Hi Tom, I have a question about full export/import of database (I have only used export/import of specific schemas before). I have a script to do a full export of the database. It does the following : $SQLPLUS /nolog <<EOF >> (...)
9 October 2008
Logical Standby database: <code> Hi Tom, 1) What is advantage and disadvantage of Logical Standby database? 2) undo_management = Auto or undo_management = manual Which is best method? 3) What is the use of following users in 9.2 database ODM (...)
9 October 2008
Can I do mutlithreaded programming in PLSQL: <code>Can I do multithread programming in Oracle proceudres? By the way, I think dbms_pipe has similar functions as dbms_alert's signal functions, what's the difference? Which one is much better if I want to use them to write a program which (...)
9 October 2008
Can partition key updateable ?: <code>hi, Is it possible to update a partition key in the above mention version or higher. Regds. Madhusudhana Rao.Puvulla </code>
9 October 2008
AUTO COMMIT: <code> Hi Sir, We have an application that executes stored procedure via VBA . Apparently ,it auto commits that statement. Is there a way to configure the oracle database to set the auto commit to off so that (...)
9 October 2008
surrogate Key: <code>Tom, we have application requirement like this.the master table is Busstop having columns stopid,onstreet,crossstreet,direction.Stopid is primary key.Stopid Automatically genarating in application (from GUI)based on select of the (...)
9 October 2008
11g/10g?: We are currently using Oracle 9.5.0.2 and planning to upgrade to newer version. What do you recommend us when you compare with 10g/11g? I was under impression that we should wait until Release 2 (Don't worry about actual name, I am talking (...)
9 October 2008
Multiple concatenated column indexes or single column indexes?: When I design tables, I've always added indexes to the columns that were used in WHERE clauses in queries. If the columns were used in multiple WHERE-clause combinations, I index each one of the columns that are being used. I found an application (...)
9 October 2008
OLTP to Datawarehose: <code>Tom, Given a production OLTP database with several huge partitioned tables, each of them with potentially 1 milion transactions per day, we need to copy/transfer nightly to a separate Data Warehouse those transactions (occured in OLTP from (...)
7 October 2008
consistent gets -- Very puzzling: <code>Tom: create table test( a int); begin for i in 1..10000 loop insert into test values (i); end loop; end; set autotrace on select count(0) from test; Execution Plan 0...
6 October 2008
How to compare two tables of data????: <code>Hi Tom, I have two tables of values(dept1 and dept2). How do I compare all the data in these two tables??? It will return true if both tables contain the same data & false for otherwise... Another thing is that I do not know how to use (...)
3 October 2008
Steps to follow when migrating from a lower to higher version: Tom, Could you just point out the steps or thing to be considered when migrating from a lower version to higher version? Just an example, when migrating from 9i to 11g. I have seen many articles, but none of them say very clearly what to do step (...)
3 October 2008
Inserts with APPEND Hint.: <code>insert /*+ append */ into t select rownum,mod(rownum,5) from all_objects where rownum <=1000 call count cpu elapsed disk query current rows ------ -------- ---------- ---------- ---------- (...)
3 October 2008
User-Defined Aggregate with More than One Value Parameter: <b>I would like to create user-defined aggregate/window functions that allow multiple parameters</b> such as <u>weighted-averages</u>, <u>weighted-standard-deviations</u>, and various other statistical calculations for statistical process control (...)
3 October 2008
Investigate Wait events & Poor response times using Statspack report.: <code>Hi Tom, Here is Statspack report that was obtained when the end-users complained about the poor reponse times in the system. Please advice on the following: (1) The Top wait event i.e. Latch Free. (2) Latches Activity. During this (...)
3 October 2008
questions on LONGS: <code>Can you perform string functions on the datatype LONG? </code>
3 October 2008
Determining which query caused deadlock.: <code>Our application is causing deadlocks. After doing research I'm almost certain it is due to not having an index on a couple of foreign key columns. *** 2002-11-04 09:17:47.998 *** SESSION ID:(12.346) 2002-11-04 09:17:47.960 DEADLOCK (...)
3 October 2008
RBS vs Undo Tablespaces: <code>Which is the best to use, are there advanteges from one over the other like is performance better or easier administration etc Thanks in advance Craig </code>
3 October 2008
Database Locks: <code>Dear Tom Thank you for answers for my earlier questions. I would like to bring your notices one more question. 1. I am trying to monitor the database locks in my database. Query is Reddy815>CONNECT INTERNAL/ORACLE@PSI1 AS SYSDBA; (...)
2 October 2008
what is lobsegment,lobindex: <code>I query table 'user_segments' sql>select segment_name, segment_type, tablespace_name, bytes, max_extents from user_segments where segment_type like 'LOB%' result is SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME (...)
2 October 2008
reading data from .dbf file into oracle 8i -- HOW TO LOAD DBASE data: <code>I am unable to read the file which in .dbf format . if I open the file in excel and save it as .csv file , I can read the file using utl_file package of oracle 8i. I cannot do this activity manually as I would be ftp ..ing the files after (...)
2 October 2008
IN (vs) EXISTS and NOT IN (vs) NOT EXISTS: <code>Hi Tom, Can you pls explain the diff between IN and EXISTS and NOT IN and NOT EXISTS. Because I have read that EXISTS will work better than IN and NOT EXISTS will work better than NOT IN (read this is Oracle server tunning). (...)
2 October 2008
Placement of DBAs within an organization.: At the company I have just joined the database group happens to fall under the applications department. This is in stark contrast to every other place I have been where the DBAs were a part of the infrastructure group. I feel that an important (...)
2 October 2008
Design issue - Multiple schemas / additional fields which is better?: <code>Dear Tom, Your advice would be much appreciated for the following: There are about 100 tables in a schema presently. Some of the tables are partitioned with date range. Some tables are huge tables with about 6 million rows inserted per (...)
2 October 2008
Hierarchal Queries with Aggregates.: Hi Tom, To count the Number of Delimiters in a Given string i use the below query. SELECT COUNT(DECODE(LEVEL,0,NULL,LEVEL)) COUNT_DELIMIT FROM DUAL CONNECT BY INSTR('1,2,3,4,5,6,,',',',1,LEVEL) >0 but i used the same query in (...)
1 October 2008
How to monitor RMAN Backup Jobs: Hi Tom, Good Morning. We have a RMAN full backup of 230 GB job running every night. This job runs for around 1 hour 30 minutes one day and runs for 6 hours on other day. I used this query below to monitor the progress of the RMAN job and (...)
1 October 2008
COMPATIBLE PARAMETER after upgrade from 9208 to 11.1.0: If I upgrade from 9208 to 11.1.6 Compatible parameter changes from its initial value of 9.2.0.0.0 to 10.1.0. 1) Under what circumstances is it advisable to leave this parameter value to 10.1.0. or to upgrade it to 11.1.0. I do want to use all (...)
1 October 2008
Cursor FOR loops optimization in 10g: Is it true that Oracle Database 10g will automatically optimize the cursor FOR loops to perform at speeds comparable to BULK COLLECT? I just got curious after reading in one question on PL/SQL best practices on Oracle Magazine March/April 2008. (...)
1 October 2008
will an oracle upgrade (say 10.2.0.4) requires more disk space: Hi Tom, Good Morning. When we upgrade oracle from Oracle 9i to Oracle 10G, we know that we need to do some pre-checks and make changes for init.ora parameters and also create SYSAUX tablespace. Other than the SYSAUX tablespace, will an oracle (...)
1 October 2008
Writing a function which accepts unlimited parameters: Dear Tom, How can I create a function in Oracle which can accept unlimited number of parameters like decode function? Thanks in advance Milind
1 October 2008
Star Schema: <code>Hi Tom, Can you explian about STAR schema and data warehouse,dimension concept in few words? There are books available on OTN but before i start reading them explain some terminology i mention above. </code>
30 September 2008
Using CATSEARCH or CONTAINS with a serachstring longer then 4000 Bytes: Hi Tom, there was a question in a germany Oracle Forum about the problem getting an ORA-01460: unimplemented or unreasonable conversion requested when using more then 4000 Bytes in the CONTAINS Operator. As I only know the MATCHES Operator which (...)
30 September 2008
Notify changes: Hello, I need an advice about which is the best way/architecture to accomplish the following: when user A changes a record of a particular table and commits it, user B needs to be notified of this change in some way. We can choose later if (...)
30 September 2008
better approach to aggregate query?: Tom, Thanks again for this wonderful site. I have a (hopefully) pretty simple and straightforward question. I have a table like this: <code>create table data$test (n1_id number, n2_id number, n3_id number, m1 number, m2 number); insert (...)
30 September 2008
Backup using and old controlfile and online backup: <code>Hello, Q.1. The"recover database using backup controlfile" command is used to recover your database when the current controlfile is not aware of the tablespace that is lost. This is what I did and it worked. At 8:00am I took a full (...)
29 September 2008
Multiplexed REDO and a few misc questions....: Tom, I have a few 'simple' misc. questions please... 1) What is your philosophy of multiplexing ONLINE redo logs? 2) What is your philosophy of multiplixing ARCHIVED redo logs? On the above two questions, I am in process of trying to (...)
29 September 2008
Performance - inline views vs indexing: Hi Tom, thanks for your time. My question is in regards toward creating a temporary table and indexing it, vs using an inline view. Consider: Process 1: <code>SELECT <stuff> FROM a, b, (SELECT <more stuff>) c WHERE <join> (...)
29 September 2008
Email encryption/decryption: Since the dbms_obfuscation_toolkit was only introduced with 10g, how do we get the functionality to apply either the Triple DES or (preferably) AES-256 algorithm? We are sending e-mails to the general public using UTL_SMTP. Also, how does the (...)
29 September 2008
initial assesment of benefits of native pl/sql compilation: hello, i am working on the oltp system (namely on the part which processes the tranactions). as an approach of writing more performant code (and also with the perspective of moving to 11g), i was thinking of moving certain types of calculation (...)
29 September 2008
PLS-00172: string literal too long: Hi Tom, When i try to insert huge data into a CLOB colum, i get "PLS-00172: string literal too long". Tried searching in web/metalink, but of no use. Please let me know if a workaround is available for this insert. drop table t9; create (...)
29 September 2008
ORA-00932: inconsistent datatypes: expected DATE got NUMBER: <code>Hi Thomas, Thanks a lot for wonderfull technical support found on this site. I always learn a lot from it. For most of the applications, I am getting ORA-00932 errors in tkprofed files and even on OEM console while application runs. (...)
29 September 2008
Bulk Binding - why!?: Hello Tom I use your site less than I did a few years ago which must mean I am getting a little better. One thing I've never done <u>anything</u> with though is bulk binding. I am a big advocate of going for the most simple approach which just so (...)
29 September 2008
DYNAMIC_SAMPLING: Hi Tom, My questions are related to using DYNAMIC_SAMPLING. a. Does dynamic sampling work the same way in 10g and 9i? If not, what does it do better in 10g? b. What is the difference between using DYNAMIC_SAMPLING(F 0) and DYNAMIC_SAMPLING(...
29 September 2008
UPDATE command on nested tables(PL/SQL objects): Hello Tom, How to update nested table(PL/SQL object) using UPDATE command? CREATE OR REPLACE TYPE test_obj AS OBJECT(e_id NUMBER ,e_name VARCHAR2(10),e_sal NUMBER); / CREATE OR REPLACE TYPE test_obj_tab AS TABLE OF test_obj; / DECLARE (...)
29 September 2008
Dates Question: Hi Tom, Thanks for answering my question.. I'm working on custom time entry application using APEX.. I have a requirement to show a combo list with weekly dates Monday - Saturday based on the system date with 10 weeks in the future and 10 weeks (...)
29 September 2008
Question on explain plan for hash join: Lately I was working on a query which was fetching like 2million rows and was running for a few hours .I was able to tune this query to give out results in less than 15 mins but i still have questions regarding the explain plan of the old un-tuned (...)
29 September 2008
Backing Up Table with TDE: Hi Tom, I have recently been getting involved with an internal department project that will be using TDE (Transparent Data Encryption) for one of it's table columns. As you are aware when you run a full export on a database that has a table with (...)
28 September 2008
efficient upsert: <code>Hi Tom, In order to implement upsert logic (update if exists/insert if does not) in batch processing I use one of following 1) try to insert, if fail on PK constraint, handle exception, update. 2) try to update, count sql%rowcount, (...)
28 September 2008
DBMS_PROFILER: <code>Hi Tom, I have two questions: Ques1. I am executing a package which calls another package(thru DB link) which is in different instance. It was working fine but suddenly now I started getting this error below- ORA-02064: distributed (...)
28 September 2008
Create context for Multiple users: Hi Tom, I have two users "A" and "B" both have same procedures "MY_PROCEDURE" and "P" and "create and drop any context grants" On user "A" I created context MY_CTX and Procedures. create or replace context MY_CTX using MY_PROCEDURE; (...)
27 September 2008
XML Generation/Manipulation using SQL: Hi Tom, After almost 3 months, i got the chance to submit a question. This is first time i submitting. I have few queries related to XML. I would be grateful to you if you answer with small example. a) How to generate XML from a standard (...)
27 September 2008
Logical Reads vs Physical Reads: <code>Tom, Have heard DBA's mention everytime to keep the logical reads low. But, when asked why, they can't come with an answer. When a block is requested by a query, Oracle looks for it in the Buffer Cache and if found, it results in a (...)
27 September 2008
new rman undo bypass "feature"?: I came across metalink note 466931.1 "Oracle Database 11g Top New Features : Summary" today searching for something else and noticed mention of an 11g feature I had not seen in any previous materials, or in the 11g documentation online. quote: (...)
27 September 2008
Load XML from local Drive with out creating a Directory: I need a sample script (PL SQL) to load a XML file from a local or mapped drive into a oracle database. The plan is load the xml file into a stage table. CREATE TABLE Stage_BD ( id NUMBER(15) , xml_file XMLTYPE , (...)
27 September 2008
DBMS_UTILITY.LNAME_ARRAY: Hi Tom, Can we create and use DBMS_UTILITY.LNAME_ARRAY / DBMS_UTILITY.maxname_array TYPE in Oracle 8.1.7 by calling Oracle 9i ORACLE_HOME/rdbms/admin/dbmsutil.sql ? Thanks Moni
27 September 2008
Keeping QA Tables in Sync with PROD: Hi Tom, Our Developers want an automatic way to (re)synchronize certain tables in QA environment with production data. I haven't gotten all requirements yet, but it looks like they are wanting to get the data in sync on QA (after changing (...)
27 September 2008
using hints in queries: Hello, I just became aware of hints last month and haven't found much documentation on them. Most seem to advise against using them even when doing a search here and finding this topic. http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_Q...
27 September 2008
extract execution timestamps from raw tracefile: Tom, Greetings. when you came to the PHLOUG's meeting few months ago, i asked you this question and got some information from you, but need little more further clarification. I am trying to extract the actual sql statement execution (...)
27 September 2008
adding a millisecond to a date: <code>how do i add a millisecond to a date. i only have calculate(24/(60 * 60 *1000) and add) or is there any other way? thanks nagaraju </code>
26 September 2008
pessimistic locking vs optimistic locking: Hi Tom, I would like to have your point of view on that, pessimistic vs optimistic locking. I read this article http://www.orafaq.com/papers/locking.pdf on optimistic locking. What the guy is saying is better to use op...
26 September 2008
mean, median, mode - analytic: <code>Tom, I am trying to calculate mean, median, and mode of salary within each department, and display it on all rows (corresponding to the department). It's fine to ignore rows with a null salary. I believe I've got mean and median, but I (...)
26 September 2008
Rebuilding: <code>Sir, I know we can easily rebuild nonunique indexes but i want to know can we rebuild primary key indexes or we have to drop and recreate those indexes? Thanks. atul </code>
25 September 2008
Why there is so much logging for "insert /+* append */ into tab select"?: <code>Hi Tom, Q1 ~~~~ I was executing the following sql statement with the table in nologging mode. But it seems the changes are logged (not just the dictionary changes). Please have a look at the following. Case1: ======== SQL> (...)
25 September 2008
primary key constraint vs non-unique index+PK: <code>Hi I have some questions regarding definition of primary keys at database level. I know that in 8i we can enforce uniqueness using non-unique index and primary keys, this makes thing flexible in datawarehouse, when we have duplicate rows (...)
25 September 2008
STRAGG: Hi Tom, Need your help here. Stage table: Customer Product Order Date Operator ------------------ ---------- C1 P1 D1 OP1 C1 P2 D2 OP2 C1 P3 D3 ...
25 September 2008
Column order in Index: <code>Hi Tom. If I have a table called Test contains 5 columns a, b, c, d and e. 1. What is the deference between index_1, and index_2 if they are defined as:- Index_1. Create index_1 on test (a, b); Index_2. Create index_2 on test (...)
25 September 2008
Transactions per second: <code>Hi Tom, Is there a quick way to calculate the number of tranactions per second that my database has processed since it was started? Thanks, Gabe Zauss </code>
25 September 2008
Yet another ASCII dump issue: Tom, I've written Pro/C to generically dump any table to delimited ASCII. It dynamically builds a select statement with a single column containing all the table's columns concatenated together with a delimiter, then fetches this into a (...)
25 September 2008
Data Warehousing database & a regular Database: <code>Tom How is a data warehousing database different from a regular database. I usually find that data warehousing is seen as a separate skill. Why ? What are the areas (in a nutshell ) wherein a data warehouse database differes from a (...)
25 September 2008
comparing differenece between two tables: <code>i want's to see the diffrence between two tables columnnames,datatype,datalength. I have following sql statement, which doesn't give me appropriate result. select table_name,column_name,data_type,data_length from user_tab_columns a (...)
25 September 2008
Creating dynamic stored procedure problem: <code>Here is my first dynamic stored procedure and I have some errors when trying to compile it. Please help. I am new in ORACLE. Thanks a lot Line 12 PLS-00103: Encountered the symbol "=" when expecting one of the following: constant (...)
25 September 2008
current_user or session user problem with package: <code>Hello Tom, (Oracle 9.2.0.4 on Solaris 8) this as dogged me for two days but maybe you can see it immediately . The question is what's happening in this correlated sub query using user_role_privs for a package called NOT by the owner (no (...)
25 September 2008
Checksum within UTL_FILE: Hi Tom I have used your example http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:68212348056 to create flat files from the database using UTL_FILE, however I have modified this slightly as below to amend the output file (...)
25 September 2008
How to limit the numer of rows to a given number: <code>Hi Tom, Could you propose a solution for the following constraint: in a given table T with columns A,B,C are up to 5 entries allowed for a given combination of (A,B). The only solution I can think of is having a "counter" columnt in a (...)
23 September 2008
Converting CLOBS TO VARCHAR: <code>Can you give me a solution for converting CLOBS datatype to VARCHAR datatypes, all the documents I refer to talk about converting BLOBS to VARCHAR and when I try and apply the examples to CLOBS, get errors </code>
23 September 2008
WITH Clause vs global temporary tables: Hi Tom, Can you tell me why is the WITH clause faster than using GTT tables in this situation? WITH clause with with_sere as (select /*+ parallel (dein,8) full(dein) */ dein.inte_no (...)
23 September 2008
user authentication: <code>hi Tom, I am stuck with the following question and would appriciate your help, What 2 methods of user authentication are available in Oracle??? What are some advantages and disadvantages of each??? Thank u Tan </code>
21 September 2008
Inserting clob performance issue: enable vs disable strorage in row: <code>Storage in row DISABLE vs ENABLE: why such a performance difference ? With ENABLE, inserts is about 30 times faster than with DISABLE ! I thought using DISABLE would be a better option because the clob values (text to be filled) would be (...)
20 September 2008
Replication For Physically Disconnected Servers: What do you recommend for replication when the only means of communication is an "air gap" folder? These systems will never be able to talk to each other due to security constraints (government networks). The data must be in text format so (...)
19 September 2008
Constraints on partitions.: Hi Tom, Can constraints be enabled or applied on partitions of a table instead of the entire table? I tried searching the docs but couldn't find anything. Tried my own tests as well but they didn't work either, though that doesn't mean that (...)
19 September 2008
Overuse of cursors?: Tom, This really isn't a standard question, but given your experience I was hoping you could answer it for me. In my current job, standard practice is to do <b>everything</b> in the form: <code>Declare Cursor <what we want to (...)
19 September 2008
Historisation Concept: Hi Tom, I am working on a requirement where I have to keep historize data in all database table for maximum 10 Years. I have read severeal post on asktom, search on google and comeup with conclusion on using "Workspace Manager". I need (...)
19 September 2008
Drpping primary key constraint: <code>Is there a way to drop primary key constraint without dropping the associated Unique Index? We are facing this situation in a Data Warehouse installation. We had used disable/ enable constraints as a way to trap invalid records. We had done (...)
19 September 2008
start/stop db from command line in NT: <code>I'm looking for some script *.bat that I can schedul to startup and shutdown my Oracle database in NT and 2000 Server. </code>
19 September 2008
pragma autonomous_transaction within procedure before creating synonyms: Hi Tom, I have created a stored procedure with in oracle package which creates list of synonyms depending upon the change of dblink server name. I need to execute this procedure to create/replace synonym pointing to another dblink server. My (...)
19 September 2008
Unique Constraint violation when using merge: Hi Tom, we are using external tables to load an update our tables. Because of an error in one of our mainframe programs we got dublicate rows in the file we wanted to merge. The result was a Unique Constraint violation on the PK constraint. (...)
19 September 2008
SQLLDR from PL/SQL procedure?: Tom, Is it possible to run sqlldr from a stored procedure in Oracle 8.1.7.4 Standard Edition on Windoze 2000 Server? Thanks, Sinan
19 September 2008
Sourcecode repository: Tom- We are trying to create an extensive sourcecode repository of ALL oracle objects (created by users). Over time we have used a few scripts (on dba_source) to spool files and have used dbms_metadata to some extent to get scripts out. Is (...)
17 September 2008
Space thresholds for tablespace and OS: Hi Tom I am currently struggling with a situation where we are under new management which has no experience with open systems and Oracle and databases in general. We are being questioned as to "why" for various things and the standard comment is (...)
17 September 2008
Database Workload and Health Report: Hi Tom, I am trying to figure out some (summary) statistics to use to report on the growth, workload, health, etc of my database over time. I have a hunch you are going to tell me this is a fool's errand... but I guess I'm hard-headed, and I (...)
17 September 2008
DATA TYPE CHANGING FROM NUMBER(8,5) TO NUMBER IN VIEW: Hi All, I have a column DISC_PCT which has number(8,5) as data type in the table. when I create a view on this column as create or replace view hdr_view as select round(disc_pct/100,5) from table; end; I now see this column as (...)
17 September 2008
Temporary tablespaces: What am I missing if I do not use the 'TEMPORARY' keyword when creating a temporary tablespace? I have a situation where a tablespace has been created without using the keyword. Users have been created with this tablespace as their (...)
17 September 2008
hierarchical query with aggraget function: Hi Tom, consider the following: <code> articles create table article_tab ( articleid number not null, description varchar2(100) not null, primary key (articleid) ) / categories create table category_tab ( categoryid (...)
17 September 2008
Columns in the database that are 'used': <code>Can you suggest a way to efficiently check every column in a database specifying whether the given column is 'used' (has values that are not null and not zero for NUMBER and not null and not spaces for non-numeric) and if it's used, some (...)
17 September 2008
Flush shared pool: Hi, 1. I use Auto Trace/ Explain Plan to tune SQLs. During this process, Do I have to flush out the shared pool evertime before executing my query to find the correct response time? ( Alter system flush shared_pool )? what will be the (...)
17 September 2008
blob storage question: <code>I have a table with a blob column. It's possible to specify an extra storage clause for this column ? </code>
17 September 2008
tune these max statements with subquery: <code>How can I convince my developers there is a better way to do this? SELECT MAX (weight) weight FROM client.pet_weight WHERE pet_id = :pet_id AND setup_date = (SELECT MAX (...)
17 September 2008
Timestamp vs. Date - is Date still appropriate for new systems?: <code>The new Timestamp datatypes are described as extensions of the Date datatype in Oracle's documentation. They are clearly more powerful and include capabilities such as tracking times down to milliseconds, incorporating time zones, etc. (...)
17 September 2008
Dynamic Dynamic SQL: Tom, sorry about that I figured it out finally. Thanks. -quinn I am somewhat familiar with dynamic sql and can substitute values in the sql e.g. st := 'select acno from prod.acc where acp = ''PI'' and acno = :1'; execute immedia...
17 September 2008
Top 10 Performance Indicators: <code>Hi Tom, I would like to set up an automated performance monitoring system for my 20 or so Oracle 817 databases. I suspect that if I could monitor about 10 or so statistics and keep them in line (ie. DB Buffer Hit rate of +90%) then I will (...)
17 September 2008
Fine Grained Auditing: <code>Hi Tom I am trying to audit some queries, I read about Fine Grained Auditing in Oracle University notes. It says that if I set an audit condition such as sal > 3000 then the triggering event is not that the user accessed emp records (...)
17 September 2008
Temporary tables (comparision with SQLServer): <code>We are migrating some Stored Procedures from Sybase 11 to Oracle 8i. Most of them use temporal tables like: SELECT x, y, z INTO #Temp FROM some_table ... OR- CREATE TABLE #Temp ( .... ) Where #Temp is a Temporal Table in a temporal (...)
17 September 2008
Deleting duplicate records: <code>Hi Tom, I have a table T1 with columns C1, C2 and C3. Currently, there are duplicate records in T1 with the C1, C2 combination. T1: C1 C2 C3 -- -- -- 1 50 a 1 50 b (...)
17 September 2008
insert into Temporary tables: <code>Hi Tom, In a stored procedure I am trying to create a temporary table, select values from an existing table and insert those values into the temporary table i just created. My stored procedure is: create or replace procedure (...)
17 September 2008
Using the DBMS_STATS-package: <code>We are doing performance-testing of SQL-statements in our project. When running explain-plans on the statements, the results are often not realistic due to the fact that we have not loaded the tables with data, and therefore the indexes, in (...)
17 September 2008
Deleting rows in 10g: Tom, At the Atlanta OUG, you mentioned that in 10g, Oracle will re-allocate space in certain situations such as deleting every other row in a table. This has gotten me curious, and I was wondering if you would mind elaborating on this? (...)
17 September 2008
Loading XML Into Tables Using Pl/SQL: <code>Dear Tom, I am new to XML and don't know Java and, unfortunately, do not have time to learn it right now as I need to have a little prototype done of this in a couple of days. I did a lot of reading on your site and reviewed many of the (...)
16 September 2008
OEM of 10G: <code>Hi Tom, Recently, I attended launch of 10g. I found that OEM with 10g is really cool. Will the statspack still come as separate component or it will be part of OEM in 10g? Also, could you please point me to a link for sql model clause on (...)
16 September 2008
Question regarding Parallelism.: Hi Tom, I was trying out some stuff on my local database.My questions are I have created 2 sample tables which would go for a full scan and iam looking at something like contents of tab1-contents of tab2,I tried out with and without (...)
16 September 2008
rename a table: Hello tom, this is sreedhar got a silly dout.. where can i get the info regarding a table name has been changed with RENAME TO well i want to know more about that.,, recently i faced the problem one of our developer has (...)
16 September 2008
Oracle Patch question: <code>Hi Tom, I've specified oracle 9.2.0.6 plus a number of additional patches for a new system. When the patches are applied (mostly new binaries) does the database record the fact? I've searched the dictionary and asked colleagues but (...)
16 September 2008
Is it possible to add a Partion on existing table: <code>Hi Tom, I have a table called SO, It is not partioned, I need to partion based on the column trans_date. If trans_date's month Jan means partition m1, feb means m2 .. upto 12 partition. I don't want to recreate the table. How can I alter the (...)
11 September 2008
Linux-Getting Started: <code>Tom, I want to learn to use Linux and do not have ANY experience with it yet so I am not sure of the best way to go. I have used AIX for basic functions but not sure if it will be similar. What version do you run personally ....and what (...)
11 September 2008
check PGA usage at OS level (UNIX/LINUX): <code>Hi Tom I am currently trying to figure our PGA usage from some users, I can obtain from v$sesstat views but it changes all the time. In Solaris I can use pmap to track real memory usage, do you know any other tool for linux and hp-ux? (...)
11 September 2008
Materialized view refresh - Data compression: Hi Tom, We have an MV that is built with NOLOGGING and compress. But after refresh, there is no reduction in the total block numbers as compared to the table. When I see the recursive SQL, I see only insert /*+ bypass_recursive_check */ and there (...)
11 September 2008
How can I DO PATTERN MATCHING from within PL-SQL using REGULAR Expression: <code>Hi Tom, How r u doing.. I must be quick on this because today is the first time i am able to put my Qnand hope that u will see it.. My problem is : 1. I need to search for matches in a table which has a bunch of Mobile phone (...)
10 September 2008
Can Oracle allocate more PGA than PGA_AGGREGATE_TARGET parameter ?: Hi Tom, Can Oracle allocate more PGA memory than PGA_AGGREGATE_TARGET parameter ? If yes, is there a rule for that ? Thanks, Pierre
9 September 2008
want to find a way to shift column values up or down: <code>Tom, to illustrate what I want to accomplish, as an example, table test has two columns, which looks like 1000 12300 1002 5690 1003 10000 1010 8765 I want to shift the vaules of the second column up so that (...)
9 September 2008
PL/SQL Server Pages (PSP): <code>Where is the utility named "loadpsp" found so that I can compile PSP files? </code>
8 September 2008
performance while using union all: <code>Hi tom, I have a question regarding the internals (and costs) of a UNION ALL statement. Up to now we are running some of our selects on a huge table (table1) which consists of more than 1 billion rows. The data of this table will be (...)
8 September 2008
database sessions -- seeing their sql.: <code>How can I see (with SQL*plus) that is the last SQL statement that a specific user has given.... </code>
8 September 2008
How to track logon & logout ...: <code>Hi Tom, I'm impressed by the quality of the answers you provide in this site. It is really a boon to us Oracle users... I wanted your help in the following : I would like to track the Logon & logout timings of each user on to a flat (...)
5 September 2008
Analytic Functions Drawbacks: Hi Tom, Analytic functions are really cool, and I can see great benefits from using it. Despite its benefits, does Analytics Functions has any drawbacks, like performance issues, or any details one should be aware of when choosing to use (...)
5 September 2008
Temporary tables vs nested table in vpd policy: Hello Tom, In my implementation of vpd policy function I need to return "select * from temp_table" where temp_table is either a temporary table or a nested table (which will be a package variable). The reason is that the data I need is not in (...)
5 September 2008
Automatic Memory Management: Hi Tom, I am trying to understand For Automatic Memory Management in 11g.We have MEMORY_TARGET parameter. 1)Can you please explain how MEMORY_TARGET considers SGA_MAZ_SIZE if auto shared memory management is enabled. If i keep (...)
5 September 2008
Pagination query and optimization: Hi Tom, I've been reading through a few posts regarding pagination. I'm using the following query template in my application, to implement a "previous", "next" feature to page through a search resultset: (J2EE front-end calls a stored proc (...)
5 September 2008
Loading Lobs Using SQL Loader: <code>Hi, I need some help here. I am trying to load a text document in to a table . Structure of my table is as follows. LOB_ID VARCHAR2(1) MY_LOB CLOB This is my control file. load data infile (...)
5 September 2008
Long Raw to BLOB: <code>Hi Tom, We are using an Oracle 8.1.7 database. Is there a way in PL/SQL or Java Stored Procedure to convert a Long Raw into a BLOB? Thanks, Firas Khasawneh </code>
5 September 2008
New database and password file: <code>Hi Tom, I am kind of confused about password file parameter in init.ora. What is the functionality of password file parameter exactly, becuase whenever I am trying to create a new database (On Unix) while password file parameter is set to (...)
5 September 2008
Does merge with append hint causes fragmentation?: Hi Tom, Thanks for taking this question. Quite a straight forward question, by the way: Does merge with append hint causes table fragmentation? I have table and its data is inserted only with the merge operation, using append hint (and (...)
5 September 2008
question on rownum: when I have a query of the form <code>select rownum rn, x.* from (select ... from ... order by ... ) x </code> is it guaranteed that the output is ordered by rn (without explicitly telling Oracle to order it by rn) (...)
5 September 2008
Multi table insert has the "double db block gets" than a single insert - why ?: Hi Tom, thanks for the chance to answer a question - you're doing a great job with this site! I'm investigating the multitable insert feature. As far as I've understood is one of its' purposes to read the source table once and than to (...)
5 September 2008
Always compress first column of an index?: I have searched far & high, but not yet found an answer to this question. Is it <b><u>always</u></b> better to compress the first column of a concatenated index (or the only column of a non-unique index), rather than leave the index totally (...)
5 September 2008
Cursors : When exactly cursor run the Query?: Hi, I need to know as to when exactly cursor runs it's query? As per my understanding Cursor runs query once that too when it is called but if I check v$sqlArea query is getting executed again and again? I recently optimize a process and (...)
5 September 2008
New/Old features: Hi Tom: What are your favorite features in 11.1? Over the years you have mentioned many tools, like JDeveloper that you thought were great, but today, what do you think the essential tools to analyze problems are? It seems to me that RTFM (...)
5 September 2008
execute shell script from stored procedure: <code>Is it possible to execute shell script from store procedure in async. mode? If yes then how and if not is there any work around to doit? </code>
5 September 2008
Exception in Declare Section: I just want to know why exception in the declare section of a procedure is not handled at the same level of the exception handling but only at one level up. Below is a small code sample. <code> SQL> create or replace procedure proc(pIn (...)
3 September 2008
rman: Whether oracle could recover backup from other platform? How to?
3 September 2008
Automatic Statistics Collection: Tom - In Oracle 11g, there has been a restructuring of the job scheduling framework. In particular, the automatic gathering of optimizer statistics. In Oracle 10g, the following query reveals the association of the program GATHER_STATS_PROG with (...)
3 September 2008
AUDITING OF USERS ACTIVITIES IN DATABASE: hi tom i want to audit / track of user's activities in database (like updation,insertion,deletion with date and time ) in oracle. frontend applicatoin is in form 6i. thanks in advance waiting for your prompt response san khan
3 September 2008
Higher Chain Count: Hi Tom, Thanks for helping the Oracle Community across the world. My question is. If 75% of the rows are chained rows in my table 'T' with pctfree 10 (default). Then how 'alter table T move..' will help me to reorganize the table? (...)
3 September 2008
Asynchronous Process: Hi Tom I Know that a Job can be used like an asynchronous Process, but i would like to Know if there is another way to implement an asynchronous process. All that I want is Just a PL/SQL Block that call another one and immediately finish (...)
3 September 2008
how to stop killing the pga and oracle: We have a report that is running and causing the system to die. The DBA noticed the pga was filling up and asked me to look at the proc that was running. I noticed it has serveral inputs and several outputs. Most importantly two of the five (...)
3 September 2008
Converting CHAR to Numeric via user written function: Hi Tom, We use the following function to convert numbers in CHAR format to numeric. The data is very dirty so if it can't convert or you get "infinity" we just put in a zero. We run in parallel over billions of rows. It used to be quite slow (...)
3 September 2008
Writing a stored proc in oracle: I have written 100's of comples stored proc in sql server but I am having trouble writing my 1st in oracle. I just have a stored proc with one parameter and executes a select statement for which the results will go on to a crystal report. It (...)
3 September 2008
SQL query to get the most recent entry from a group...: Hi Tom, Here are the table create and insert scripts: CREATE TABLE TEST ( HTENANT NUMBER NOT NULL, HMY NUMBER NOT NULL, SACTIONTYPE2A CHAR(1 BYTE), DTEFFECTIVE2B (...)
3 September 2008
The fastest way of creating a huge table for testing: Hi Tom, I wanted to create the below test table and insert about 300 million records into it. Is there a better (faster) way of doing that? Db version is 8.1.7.4 Standard Edition. <code> begin for i in 1..100 loop insert /*+ append (...)
3 September 2008
Which one is better to use with eBusiness suite DB - Streams or Materialized views?: Tom : My client is using eBusiness suite and from there they want to implement a data replication of certain tables to a central 10g DB. Their question is should they use Streams or Mviews? I do not know enough about eBusiness suite to know which (...)
3 September 2008
Question on replace: Tom, I have a question on replace function. Wanted to replace all the commans with sequence numbers. My original data is like this. SQL> select * from func_test; NO ERRMSG ------------------------------ (...)
3 September 2008
J2EE and Oracle's stored procedures: Dear Tom, I am a database developer/development DBA who ascribes to your development philosophy and approaches. I am also a Java certified programmer, although I do not claim Java as a primary skill. Recently, I have been tasked to (...)
2 September 2008
explain,sql_trace and tkprof: <code>Tom: I am doing some testing, i created table so(request int,error_flag int, interface int,order_source int,original int) and insert 100,000 rows into it, the values is (1,1,1,1,1) to (100000,100000,100000,100000,100000); then i (...)
2 September 2008
Flashback Question.: Tom , Could you please mention what privilege i need to get. Documentation says flashback against data dictionary view are allowed as below. You cannot retrieve past data from a V$ view in the data dictionary. Performing a (...)
1 September 2008
Table Compression -- in regards to Jan/Feb 2003 article: <code>In the Jan/Feb 2003 issue of Oracle magazine, you mention using the new COMPRESS feature of 9i R2. I think this was predicated on space being the only issue. Some applications may still want speed to be considered. In my tests of this (...)
1 September 2008
Passing an ARRAY from Java to PL/SQL: <code>Hi Tom, I need to Pass String array from Java to PL/SQL and also return array from PL/SQL. I refered your book and arrived at the below code. CREATE OR REPLACE TYPE STRARRAY AS TABLE OF VARCHAR2 (255) / CREATE OR REPLACE PACKAGE (...)
1 September 2008
Turning Column Into Rows: <code>Hi Tom, I have the following situation. Please see the SQL's and DDL statements. The question is: instead of 2 "INSERT INTO...SELECT" statements, is there any way I can do it in one shot? SQL> CREATE TABLE x ( 2 country (...)
1 September 2008
export to CSV: <code>Tom, I need to export data from a table into a .csv file. I need to have my column headres in between "" and data separated by ','. also depending on the column values a row may be printed upto 5 times with data differing in only one (...)
1 September 2008
Multi Tasking in PL/SQL: <code>Is there anyway possible in PL/SQL to invoke multiple sessions and run a procedure in the multiple sessions simultaneously. I want to use this in a real time applications where there are 250 users logging into the application. The users are (...)
31 August 2008
PGA memory causing swapping?: Tom, Last Thursday, I got a call from one of our systems administrators that users were having problems logging in. We were not under heavy load on the database, but we had a lot of jdbc connections (from oracle apps self service), we're running (...)
30 August 2008
Optimistic Locking in 10G: <code>I've just having a poke around the doco for 10G. I came across this. </code> http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10759/pseudocolumns006.htm#sthref707 <code> Usually in an optimistic locking strategy you would (...)
30 August 2008
Cluster on One Table: <code>I have a table having around 10Million rows. Data can be grouped against a key, each key generally having around 10-200 rows. Data against this key gets inserted over a large period of time. Distinct keys in this table at present are .7 (...)
30 August 2008
Redo and Rollback: <code>Sir,I still have some doubts in how Redo and Rollback are used. Answers to following queries will remove my doubts I have 5 records of a table in Data Block buffers 1.When I update the table,What are the entries made to the Rollback (...)
30 August 2008
Bulk operations. jdbc using batch option VS jdbc calling stored procedure: <code>Hi Tom ! I have a question regarding bulk operations (inserts/updates and deletes). I need to insert a large amount of records into a table. Performance wise what is the preferred way. Using JDBC with the Batch option or implement a JDBC (...)
30 August 2008
NOLOGGING: Tom I've been re-visiting my knowledge on NOLOGGING as a colleague asked me a question about it today and I hate to say I couldn't remember fully why the term is a tiny bit of a misnomer (there's no denying that there is a general confusion (...)
30 August 2008
PCTVERSION: <code>Hi Tom, Can U please explain me with an example on how to set/tune the parameter PCTVERSION ? Thankx, Subhakar </code>
30 August 2008
write "consistency": <code>Hi Tom, I have always worked under the assumption that a statement like update t set ... where ... is evaluated as 1) take a consistent snapshot of the table at statement start time 2) for every row in the snapshot, wait on (...)
29 August 2008
Hierarchical query: Hi: I have hierarchical query where i would like to know all parents, grand parents, their parents etc. i.e. as connect by value is changing i would like to know. I know in 9i we have sys_connect_by_path but i need that functionality in (...)
29 August 2008
Long Raw Datatypes: <code>I have 2 tables with 2 columns each; first column is VARCHAR2 & the second is LONG RAW - to store images - for both tables. I am trying to create 2 tables (for trial purposes) based on these tables using the following statement:- CREATE (...)
29 August 2008
Questions About External Files: <code>Hi, Tom. I am trying to use Oracle 9i's ability to treat ASCII files as Oracle tables and have run into a few problems. First off , here is the script I've written that creates a directory called "external_tables_dir" and then reads an (...)
29 August 2008
ORA-12560: TNS:protocol adapter error: i'm running oracle on standalone computer at home.it used to work properly but from couple of days i'm getting error stating "<b>ORA-12560: TNS:protocol adapter error</b> " what should i do? please help.. (...)
28 August 2008
Oracle Session Management in Web Applications: <code>Tom, I'm having discussions at my organisation on the merits of using Oracle HTTP Server for our web applications rather than PHP. I would be grateful if you could comment on a couple of things. 1) How does the Oracle HTTP Server (...)
27 August 2008
Statspack report: <code>I am new to DB administration world. I generated statspack report. I got a couple of pages report. How to find out the problematic areas in this report? Could you please give some tips to understand this report? (...)
27 August 2008
Inline Views Vs. Materialized Views: <code>Hi Tom, Can you please clarify the following: 1. What is the difference (in terms of performance) between using and inline view in an SQL statement and putting the query of the inline view in a materialized view and then using the (...)
27 August 2008
Reclaim space: <code>Hi, A very happy & prosperous new year!!! I want to reduce the size of USERS datafile. I had created a table with 50000000 records in USERS tablespace. So the size of datafile is around 4.5 GB. I dropped the table. However this had no (...)
27 August 2008
practice backup recovery and have a question: <code>Tom: I am practice backup and recover, one scenario is: 1. make a cold whole database backup, ( include all datafile, controlfile); 2. do some batch job 3. media crash destroy all datafile, controlfile and ONLINE REDO LOGFILES.( what i (...)
27 August 2008
sub query: <code>Hi I have select with sub query like select e.id,e.name from employee e where e.dept_no=(select dept_no from dept where dept_no=:bind) my question is how many time the sub query will run? one (...)
27 August 2008
Copying datafiles between different operating systems: <code>Dear Tom I looking for a good user oriented answer to the question: Why we can not copy the database files between our current system, running under HP Tru64 Unix, to our target system, running under Sun Solaris 5.8? I'll appreciate your (...)
27 August 2008
Partially cloning databases using RMAN or Hot backups: <code>Hi Tom, I have 2 questions hovering over my head: 1. Can I clone a source database to a target database using RMAN dumps?Assuming that the target database is on a different server and with a differenct DB_NAME, but on the same platform, (...)
27 August 2008
Freelists and Initrans: <code>This following is the view from v$waitstat CLASS COUNT TIME --------- --------- data block 74454 0 sort block 0 0 save undo block 0 (...)
22 August 2008
Automate loading of data from Excel spreadsheets into database: <code>Hi Tom, Is there a any way whereby I can automate the loading of data from an excel spreadsheet into my database? The only way we know is to manually export the data into an ascii file and then load it into the database using sqlload. (...)
22 August 2008
When is "DICTIONARY-MANAGED TABLESPACE" the correct choice?: <code>Dear Tom, When is "DICTIONARY-MANAGED TABLESPACE" the correct choice for a tablespace? We are working with a complex decision-support system that features advanced, multi-master replication, updatable snapshots, and row-level security. (...)
22 August 2008
Table Copy From One DB to Another DB from dblink: Dear Tom can u help me in how to do :-Table Copy From One DB to Another DB from dblink. i searched a lot but i m not getting satificatory asnwer. i dont want, that create a table n then through dblink insert a data from one DB to another DB. (...)
22 August 2008
Parameter to wait raising a critical oracle error to client: Hi Tom, I've just a small question for you. I would like to know the name of the parameter to slow the raising of critical oracle error (like datafile of tablespace full and can't increase it) to client. I saw that this parameter exist but I (...)
22 August 2008
select and insert one lakh records taking 3 hours: Step-1 In my daily process there are 6 to 8 lakhs data is downloaded in an table <table-1) Step-2 A procedure select this data as follows in a cursor select <col names> from <table_name> where status_flag='P' and rownum < 100001; (...)
21 August 2008
rule based optimizer used (consider using cbo): When I look at an explain plan and see "rule based optimizer used (consider using cbo)" How do I track down the reason for the RBO being used, and change it so the CBO is used?
21 August 2008
Settling argument over UNDO: Our product includes a Pro*C program that, once a month, gathers all of the month's transactions into a staging table for billing purposes. After putting the raw data into the staging table, the program updates a column in the table: <code> (...)
21 August 2008
Mandatory subtype records. And subtype/cross-referential integrity: Suppose I have a supertype table with subtypes, and a mapping (cross-reference) table, like this simple example: <code>CREATE TABLE super_type ( id NUMBER constraint super_type_pk primary key , sub_type CHAR(1) ) / CREATE TABLE sub_type_A ( (...)
21 August 2008
database design: Hi Tom, I need your help and advice in designing data warehouse tables. We have requirement as to bring in data with specific date range and explode them into daily records (Table A). After this, we put them into weekly and monthly buckets (...)
21 August 2008
SGA_TARGET_ADVICE: Hi Tom, In DW environment (where data read from disk to cache and used only once[ mostly] ), how relevant SGA_TARGET_ADVICE is? I mean, does it consider above factor during estimation process? What metric one should use to measure benefit/loss (...)
21 August 2008
Predicted x Real row count - optimizer issue: Hi Tom, I have a query on a view which I want to optimize. Here it is : SELECT "APLZL" , "ARBID" , "AUFNR" , "AUFPL" , "EQFNR" , "FSAVD" , "FSAVZ" , "INGPR" , "IWERK" , "LTXA1" , (...)
21 August 2008
Oracle 9.2 and Business Object 6: Hi Tom, Thanks for providing a great service to the community. I have a very short question to which I'm having a very hard time finding the answer to. All I want to know if Business Objects 6.0 can handle UTF-8 character set and if you know (...)
21 August 2008
Data Guard Development Environment: Hi Tom, As we are in the process of implementing Data Guard, we are having a discussion here among DBA's, SA's, and APPDEV about whether to have a dedicated DG DEVELOPMENT environment which is configured similarly to production. We have a 3 (...)
21 August 2008
ORA-1652: unable to extend temp segment by 1024 in tablespace ACCT: <code>Hello Tom, I have a tablespace named ACCT containing records named AcctEvent. I want to create a new index running the following SQL command: CREATE INDEX TimestampInd ON AcctEvent(Timestamp); And I get the following error: (...)
20 August 2008
Why is cost based optimizer behaving poorly?: <code>I am working with Oracle 8.1.7 database (running on Solaris 2.8). I know that Oracle recommends use of cost based optimizer in all future projects and agree with the fact that cost based optimizer has come a long way from the time it was (...)
20 August 2008
Reverse Key: <code>Tom, I have many primary keys that are sequences. Thinking of using reverse keys. How do I change the index created by the primary key? Rebuild as a reverse key? Thanks. Keith Cutler More info: Reading through the books I (...)
20 August 2008
Hierarchial query with cumulative factor: <code>Hi, Tom I have a slight problem with a hierarchial query that I hope you can help me with. We have Oracle 8.1.7.4 and I've setup a testcase consisting of the following : A table of items with an id, a name, and a type that can be (...)
20 August 2008
Fetch across commit: <code>what do you mean by 'Fetch across commit' </code>
20 August 2008
Storing Chinese Characters: <code>Tom, We have a new requirement where we need to store Chinese Characters in the database, diplay it and print it. This affects only one table in the database. Only one table will store the chinese characters. Currently our (...)
20 August 2008
DBMS_APPLICATION_INFO and V$SQL/AREA: <code>Hi Tom, Does setting the module and action of DBMS_APPLICATION_INFO via the SET_MODULE procedure cause different versions of the same sql statement to be stored in V$SQL? I've set up a test with two sql*plus sessions and I am executing a (...)
18 August 2008
Unique database vs multi databases (schemas) for multi countries application: Hi Tom, We are about to implement an international application (using Oracle database 10g) The project should start with 4 small (about 25Gb) countries and could be extended to more than 20 countries (about 500Gb). The level of independence (...)
18 August 2008
Web Application Testing: <code>Tom: 1. If you have a web application that is performing well when accessed by one borwser, how would you do testing in a real production mode with thousands of users using it? 2. If it slows down sometimes, how would you know if it is (...)
18 August 2008
Reading deadlock trace files: <code>Hi Tom ! How can I understand which two rows where involved in the following deadlock, which appears in this excerpt of a tracefile ? Especially in the last two lines, Oracle seems to convey this information, but I was not able to (...)
18 August 2008
Getting the user updating the data through EJB's - using generic a login: <code>We have an application based on EJB's sitting on ORACLE 9 IAS 1.0.2.2.1, writing data to an Oracle database. When the user logins to the application, the users uses his login/password to login to the application. Once the login/password is (...)
18 August 2008
High Water Mark: <code>Hello Tom, I have gone through with Oracle documentation and some of your article and question/Answer about High Water Mark. I am kind of confuced with HWM. According to my knowledge, "all Oracle segments have an upper boundary (...)
14 August 2008
Your favorite 10g features: <code>I have surfed OTN for 10g information, but believe me it?s a lot of documentation :) If possible can you just name few important features that you loved most in Oracle 10g (as a product designer or beta tester) It will help me to read (...)
14 August 2008
SQLPLUS options: <code>Hi Tom Ive written a piece of code in shell to extract data from various tables;;wherein eac table is conatained in several schemas.. The problem im facing is that the spool file contains some sql lines which are unwanted... hereis the text (...)
14 August 2008
How do i prevent end users from connecting to the database other than my application?: <code>Hi Tom, Thanks for your help. We have a client/Server and a web interface. We want our end users to connect to the database only through our applications. How can we prevent the user from connecting to the database using SQL*PLUS or (...)
12 August 2008
Deterministic function: <code>Hi Tom, Recently I created a function with DETERMINISTIC definition to get performance on my query, but I check that the function is called every time even that it receive the same input. Here the script that I used to check this (...)
12 August 2008
Undocumented secrets for super-sizing your PGA: <code>Hi Tom. Can we get your opinion about the following article in "searchoracle", (a specially after the note in the begining of this article) </code> http://searchoracle.techtarget.com/tip/1,289483,sid41_gci1116697,00.html?track=sy40 <code> (...)
12 August 2008
Explicit & Implicit Cursor: <code>Hi Tom, I have visited the Explicit & Implicit cursor archive question in this forum. Is it true that Oracle 7.3 onwards implicit cursors are optimized and they don't do double fetch Or Does they do an internal array fetch. The (...)
8 August 2008
Help in Array / Type or PL/SQL table as Input and OutPut Params in Procedure: <code>Hi Tom, (Version 8.1.5 on NT) Please also let me know how could I execute in SQL *Plus.. Thanks Can you please help me out in this Frustrated situation. I need to have Procedure that should accept a Type or Array or PL/SQL Table and (...)
8 August 2008
Names for Database Links: <code>Hi Tom, I only discovered today that in Oracle8 a database link must have the same name as the database it is accessing. Is this a new feature (read limitation) that came in with Oracle8? I?m positive you always used to be able to give (...)
8 August 2008
How to interpret strange values in ENDPOINT_NUMBER column in histograms: Tom, consider this: <code> SQL> column table_name format a11 SQL> column column_name format a11 SQL> column column_name format a11 SQL> column endpoint_number format 999,999 SQL> column endpoint_value format 9.999999EEEE SQL> column (...)
8 August 2008
Compile procedure automatically -- how to avoid cascading invalidations: <code>I've two procedures: A calls B to do something. If I compile B, then A will become invalid. Can I have any setting in the database in order to compile A automatically when B is compiled? Thank you for your sincere help! (...)
7 August 2008
SQL questions involving combinations: <code>Dear Tom... I have two questions that are somewhat related to each other in concept: 1) Consider the following table which represents a state history per each person identified by PERSON_ID: SQL> CREATE TABLE status_history 2 (...)
7 August 2008
Rownum: <code>Hi Tom I have 2 questions 1. I have a table called a where I have more than one row. When I put a query "SELECT * FROM A WHERE ROWNUM=1" it gives me the first row. But if I put a query specifying any number other than 1 for e.g. (...)
7 August 2008
Oracle8i connecting a IBM DB2 database: <code>Hi, Sorry if I ask the same questions that someone did but I couldn't find the answer looking on sites that I selected. I'm would like to connect to a non ORACLE database (IBM DB2) but I really don't know how can I do this (this is new (...)
7 August 2008
NULLS LAST And Index: Hi tom, I have two cases with me. Iam not sure why it happens that way. So here it is... CASE 1 ...
6 August 2008
rewrite a sql: Hi Mr. Tom I have a SQL like this: <code>select * from t1 where not exists(select * from t2 where t2.id=t1.id) union select * from t3 where not exists(select * from t2 where t2.id=t3.id)</code> I look into the explan and noticed that the (...)
6 August 2008
Sequence increment by more than 1: Hi Tom, Normally when we create a sequence we set the increment value by 1 but there is an option of set setting a inc. value by more than 1. My question is why we sometime need to inc. sequence by more than 1? Is this realated to (...)
6 August 2008
dynamic column header: Hi Tom, I need your help in getting dynamic column headers using a query. Table A : SQL> create table master(id varchar2(40), attribute varchar2(40), name varchar2(40), status varchar2(10)); SQL> insert into master values ('COL1', (...)
6 August 2008
AWR report as input to SQL Tuning Advisor: Tom, Would you please point me to some example on how to use SQL Tuning Advisor on sql statements reported in AWR report? I believe with Enterprise Manager this would be easy but unfortunately I don't have access to it. I need to do this using (...)
6 August 2008
Submitting Questions: Hi Tom, I've been consulting your site for years, and its almost always a source of answers to just about all Oracle issues I encounter. On rare occasions I might encounter an issue not previously discussed, but there's an uncertainty how long I (...)
6 August 2008
Priorities not obeyed while dequeing: Hi Tom, I have a queue ordered by priority, enqueue time and it works great till I add a dequeue condition: dequeueOption.setCondition("TAB.user_data.title='some condition'"); After which the priorities are obeyed. I read on the forum (...)
6 August 2008
Your new book : Expert one-on-one performance by design: <code>Tom , I've got a few questions about you new book. 1.How is your new book different than the older one? After reading the drafts I kind of figure(don't get me wrong here) might be getting a little repetitive there(wrt your older (...)
6 August 2008
Function to concatenate output: <code>Hi, Is there some way to concatenate the output of a select query to a single record. for ex. in SQL Anywhere there is a function LIST select list(tname) from tab; would give a comma seprate list of all the values in tname column. (...)
6 August 2008
Special Group: Tom, i got this scenario: create table items (itemno NUMBER, credate DATE, flag VARCHAR2(16), contract_id VARCHAR2(80), item_description VARCHAR2(80), primary key (itemno, (...)
6 August 2008
Selection of JDBC client side driver - Thin Vs OCI: <code>Hi Tom, As always, thanks for your help. I have a regarding which JDBC client side driver to use: Thin or OCI. Our web application uses Servlets and JSPs (we do not use applets), which are housed in a Tomcat engine. We presently use the (...)
5 August 2008
randomly selecting 4 employees: <code>tom, i need to randomely select 4 employees from one of our core tables SQL> desc people Name Null? Type ----------------------------------------- -------- ---------------------------- TYPE_ID (...)
5 August 2008
pl/sql as front end language: <code>I have a client using pl/sql for both backend db code and front end presentation. I am thinking it's more appropriate to use 3GL languages, such as java (Oracle ADF) and .net, for the presentation and business logic tier, and reserve pl/sql (...)
5 August 2008
Migrade Oracle 10g on AIX to Oracle11g on Linux: I want migrade Oracle 1og on AIX to Oracle 11g on Linux. Can you recommend the way to migrade. Thank you very much! Hoang
5 August 2008
Validating rman cold backup: <code>Hi TOm, Just some questions on RMAN. It's my first time to use it and it's great. I have created a strategy and a script on backing up our database using rman. But it's only a coldbackup ok in noarchivelog mode. I know you wont agree (...)
5 August 2008
Recursive Lag Function: i think this can be done with analytics <code> create table t(no integer, name varchar2(50)) / insert into t values(1,'karthick') / insert into t values(2,'') / insert into t values(3,'') / insert into t values(4,'tom') / insert into t (...)
5 August 2008
separating code from data: Tom - A data-architect at work has proposed that we should start using separate database accounts to hold the "code" (packages, procedures,views, etc) and the "data" (tables, materialised views, indexes etc) for an application. I've never come (...)
5 August 2008
Regarding Function: Why it is not RECOMMENDED for the functions to use the OUT and INOUT parameters?
5 August 2008
Block base on Refcursor and PLSQL Table: <b>For reteriving the 10000 record we have one stored procedure block. Which of the following we should use? Refcursor PLSQL TABLE</b>
5 August 2008
How to split a time period into ranges based on the overlapping periods?: Hi Tom, There are three degraded service modes: No Coin Mode No Note Mode No 10cents mode. Please note the overlapping of the degraded modes is possible. Service mode, start time and end time are stored in the following table. (...)
5 August 2008
want to get month value from date value column: hi all, i had a column with date datatype. in my column the dates are in 4/1/2007 format. now i want month value from that column and date value from that column. month and year are runtime parameters plz help me. thanks in advance, (...)
5 August 2008
snapshot for long datatype column: Dear Tom, I think it should be a common question. When I create snapshot for a table that includes Long datatype, it show below error message: ORA-00997: illegal use of LONG datatype. If I omit the column then the snapshot can be successfully (...)
5 August 2008
dates from reports into excel: hi tom, i have a problem while i export datas from my reports(written in packages) to excel sheet that is, if i export the date datatype in oracle to excel it is displayed as number in excel so how can i get date as date in excel pls suggest (...)
5 August 2008
differnce between oracle 8i,9i and 10 g: Hi Tom Can u please tell the differnce between oracle 8i,9i and 10 g Regards Gowrish
5 August 2008
Importing database from 8i to 10g XE: Hi, I'm doing a technical support for an application using 10g XE oracle database, previously the old version of our application used 8i oracle database. now i need to import a database from 8i to 10g and i have a dump file also there no network (...)
5 August 2008
SGA size: <code>Why is the size of shared pool in the query not exactly the same as in the init.ora (52,428,800)? It is slightly large (54,361,560). SVRMGR> select pool,to_char(sum(bytes),'999,999,999') from v$sgastat group by pool ; POOL (...)
4 August 2008
Join or Function: <code>We have a users table (~10,000 rows) that is accessed from lots of different stored procs. (they return ref cursors) to return a user's name. What would be the best overall solution (performance, maintainability and consistency) to (...)
4 August 2008
length limitation of CLOB in pl/sql: <code>Hi Tom, I understand there is a length limitation (max 32000) for all the plsql variable. However, I have the following procedure. CREATE OR REPLACE PROCEDURE CLOB_TAB_TEST IS NUM CLOB_TEST.ID_COL%TYPE; -- INT V_VALUE (...)
3 August 2008
Invalid Objects: <code>I've detected a few invalid objects in one of our databases. How do I make them 'VALID'? </code>
3 August 2008
developer 2000/6i -- setting iconic property: <code>how to set the property of a push button as ICONIC = 'TRUE' dynamically writing PL/SQL in the trigger when_new_form_instance? </code>
2 August 2008
Simple SQL: <code> SELECT HIST_COST FROM STORE_DETAIL_HISTORY WHERE HIST_MONTH= :MONTH AND KEY_NUM= :KEY_NUM AND HIST_TYPE = :HIST_TYPE (...)
1 August 2008
Howto select first value in a group by bunch of rows....: Hi Tom I have just begun using analytic functions, but have come up short on this: In a query where I group by a field, I would like to select the first values from a specific row. I have using something like: select distinct (...)
1 August 2008
Force use of index even when index value in where clause is modified by a function: <code>Tom, I just read the article entititled 'Insisting on Indexes' on oramag's home page. Here is a copy of some of the text from the article "When your WHERE clause uses a function on a column, the Oracle optimizer won't use an index to (...)
30 July 2008
ROLLUP OR CUBE: <code>Hi Tom Thanks for your continuous help to Oracle WORLD. I have table dept like SQL> Select * from test_dept; DEPTCODE DEPTNAME AMOUNT --------- -------- 100 SALES 100 100 SALES (...)
30 July 2008
How does v$session_longops work ?: Hi again Tom, I have tried to use the v$session_longops view to track database operations, but I've noticed that only some kinds of them really appear. Besides, for example, the CREATE INDEX statement is split into multiple (...)
30 July 2008
LOCKWAITS AND TIME OUT (FROM TIBCO APPLICATION): Good Morning Gentleman, We have a RAC system.(SUN E6900 & F6800).Its bread and butter to me. TIBCO is the middleware which brings requests into the DB. We used to get JDBC TIMEOUT from the application end. TIBCO calls some oracle (...)
30 July 2008
How are varchar stored internally in a block or page.: <code>Tom, I work with a modelers group, who would like to define every varchar2 field with the maximum length. Which means that a table with 20 varchar2 fields will all be defined with a maximum of 2000 or 4000 bytes. I tied to talk to him (...)
30 July 2008
multi-table insert: <code>Tom: I was interested in using the multi-table insert feature in 9i but am stumped by the restriction that the subquery can't contain a sequence. I use a sequence to assign a Primary Key in one of the tables that is the target of the (...)
29 July 2008
Using Rebuild parallel option on large table index with 6 CPU's: <code>Tom, In order to tune the rebulding of an index I have experimented with the "REBUILD PARALLEL 6" option while rebuilding an index on a large table; e.g. "ALTER INDEX index_name REBUILD PARALLEL 6" Are there any side affects to using (...)
29 July 2008
Dynamic LDAP-Entries with OID dependent on table data?: <code>Hy Tom, I am interested in LDAP with OID and I want to know, if it is possible to make entries in the OID LDAP-tree dependent on table data. This mean that with insert, update and delete on a specific table the LDAP-tree can adjust the (...)
29 July 2008
Sending e-mail! -- Oracle 8i specific response: How to send personalized email to clients registered in my portal www.intrainternet.com using the information stored in our Database Oracle 8i automatically?
29 July 2008
Table Reorg: Most of the DBA books on Oracle discuss that if a table has lot of empty blocks below the HWM, the best way to reorg is to do the following steps : 1. CREATE TABLE N AS select * from O 2. Drop O 3. Create Owith appropriate INITIAL_EXTENT (...)
29 July 2008
Deadlocks: <code>Hello 1.What is a deadlock and how can i detected, how can i fix a deadlock?? 2. I've a temp tablespace with 3 GB of size, acording to the high water mark anly have been use about 300 MB of the file, i try to resize to 1.5 Gb but it (...)
29 July 2008
Partition - Modifying the Range Values - 14400 Error: <code>Hi Tom, I have a table with the following partitions, CREATE TABLE DEMDATA ( DEM_ID INT, DLID char(8), DLID_TYPE char(1) ) PARTITION BY RANGE (DEM_ID) (PARTITION DEMDATA_01 VALUES LESS THAN (500) TABLESPACE DEM01, PARTITION (...)
29 July 2008
event triggers: <code>Hi Tom I am trying to write some triggers for some security purposes so I have been reading chapter 13 in Application Development Fundamentals from Oracle 8.1.7 documentation (working with system events) I have some questions regarding (...)
24 July 2008
Global Temporary Table: <code>Hi Tom, Thanks a lot for answering previous questions. Appreciate if you could help in the following questions: 1. Is there a big difference between a global temporary table with on commit preserve rows and a global temporary (...)
24 July 2008
Rebuilding Indexes: <code>I know this question has been asked earlier and I am sorry to take up this question slot but I am confused regarding rebuilding indexes. If I am interpreting it correctly, you don't recommend rebuilding indexes at all. I have talked to (...)
24 July 2008
Performance Investigation Approach when using STATSPACK: <code>This is just an sanity check on my appraoch... I've been asked to look at a site who have just carried out a DB upgrade. The feeling is that there has been some performance degradation since the upgrade. The performance hit has not been (...)
24 July 2008
Mutating Table: <code>Tom, I've got a mutating table problem... I have a table with two status fields: 'loaded_status' and 'processed_status'. When the 'loaded_status' column is updated to 'C' (complete) I want to fire off a trigger that passes the ID value for (...)
24 July 2008
FLASHBACK_TRANSACTION_QUERY: <code>Tom, Could you please explain the logic behind the FLASHBACK_TRANSACTION_QUERY view, or even better could you please point me to the documentation page with explanation ? I wasn?t able to find any. I would like to know how exactly oracle (...)
23 July 2008
temporary tablespace: <code> i have two questions about the temporary tablespace. 1.) if i'm running out space on temporary, how could i know which user or transaction is using the temporary tablespace. 2.) Sometime, it happens to me that, obviously no (...)
22 July 2008
Evaluation precedence and the optimiser: <code>G'day Tom I have a question on where clause evaluation precendence, and how this ties in with the optimiser. Consider this function, where pr_debug just inserts the value of v into some simple debugging table. create or replace (...)
22 July 2008
JVM in the Database: This is a "can you settle a dispute" kind of question. You may consider this to be the wrong forum for such a question. If so, no hard feelings. A colleague and I were discussing java in the database and when the JVM first made it into the (...)
22 July 2008
9i Oracle Text searching multiple tables: Hi Tom, I currently have a simple search page that incorporates 9i Oracle Text by using the Context index on a CLOB column. What I'd like to know is if its possible to use a single query with CONTAINS that searches a CLOB column in Table A (...)
22 July 2008
email - sending from D2K: <code>How to trigger email sending to clients while using Forms of Dev 2K? Or from database? Thanks a lot. Steven </code>
19 July 2008
Tuning SQL Statements Using Explain Plan: Hi Tom , I am new in tuning sql statements. Can u give a methodology of tuning the sql statements and to predict the output of explain plan. When i use explain plan for a particular query, i am able to understand what the result means. can u (...)
18 July 2008
PLS_INTEGER versus NUMBER versus "dynamic types": <code> It has been suggested to me that I use PL/SQL declarations like PROCEDURE foo ( p_id IN PLS_INTEGER )... instead of PROCEDURE foo (p_id IN NUMBER ) ... or PROCEDURE foo (p_id IN mytable.my_id%TYPE ) I've always preferred the last (...)
18 July 2008
MV is not getting selected: <code>Hi tom Information As you required. Hi Tom Think, i have not furnished details properly. Sorry for that. This is My FACT Table Create table Sbib_Ownr.Sbib_dochdr (Carnumcod Varchar2(4) Not null, Docnum Number(10) (...)
18 July 2008
sql trace and tkprof: <code>hi tom, i have seen lot of answers from you regarding sql trace and tkprof for sql performance tuning.but i am still not clear with few things. i hope you will explain me clearly those things 1.how does this sql trace and tkprof differ (...)
18 July 2008
Query update where select was in progress: Hi Tom, One more interview question. To you it may sound amusing but I was very much confused. Here goes the question... There is a large database say, Terrabytes in size. Select query has started executing say, sharp at 10 AM.. simple (...)
18 July 2008
All about the DUAL table: <code>Hi Tom Feels good to post a question after quite some time. Look at the following : SQL> desc dual Name Null? Type ----------------------------------------- -------- ---------------------...
18 July 2008
databse buffer cache: <code>hi tom, my question is that when a user used cursors for fetching the data and the cursor is placed in the uga(am i right)and theri is no place to store data in uga, then how it is possible that a cursor in the uga fetch data.or if used data (...)
17 July 2008
Comments on Tables: <code>Please Sir , I need to Know how can I get and the comments on tables or columns from the data dict. with respect </code>
17 July 2008
Initializing a PLSQL table of records: <code>Tom, How do you initialize a PL/SQL table of records in the Declaration section of a PL/SQL block? In the following snippet, I can successfully initialize a normal scalar PL/SQL table but am unsuccessful initializing a table of records. (...)
17 July 2008
Problem with buffer overflow: <code>I run a script from NT using plus80.exe, in this script it combines several steps including procedures, sqls. After it run about 7-8 min I got this error ORA-20000 ORU-10027 buffer overflow, limit of 1000000 bytes, and then the step aborted. (...)
17 July 2008
connecting as another user without their password: <code>I have an amazing trick, all what I need if it possible to connect as x user without changing his password if I am SYS, SYSTEM or internal. </code>
16 July 2008
How can I do a variable "in list": <code>I have a simple stored procedure, that I would like to have a passed in string(varchar2) for used in select from where col1 in (var1) in a stored procedure. I've tried everything but doesn't work. Followed is my proc. Thanks CREATE (...)
16 July 2008
freelists higher than 'sessions concurrently inserting': <code>Hi Tom, I have investigated the freelist machinery recently, and have found some results that you may find interesting, and I would be happy if you could comment on it. I know you're very busy at the present time - so pretend this question (...)
16 July 2008
DBMS_OBFUSCATION_TOOLKIT: <code>Hi tom, I am in the process of testing a web application. However I am trying to use your example about DBMS_OBFUSCATION_TOOLKIT but you used bind variables. So, I am running into a problem. I need to incrypt and most important descrypt (...)
16 July 2008
how to change freelist groups for a table without recreating it: <code>Hello Sir, I am using parallel server with dual Compaq Tru64 unix with 8.1.7 as the database.Some of the frequently used tables gets locked by one of the instance. Due to this other session are just in waiting mode. In one of your reply (...)
16 July 2008
Latches and serialization: <code>Tom, When there is latch, is the entire DB operation is serialized or certain operations are serialized? Assume there are queries with literals. (consider the case of latches on the shared pool/library cache) Ex, 1. select col1, (...)
16 July 2008
automatically generate a password: <code>Hello Tom, here I have created a table as shown below,can you please give me a procedure for the auto system generation of password and want to know the probabality of that password being occured. CREATE TABLE USER_INFO (Title (...)
16 July 2008
SQL Query: Hi Tom, I have a table like this create table temp1 (f_name varchar2(100), f_date date, f_amount integer ); Records in this table will be like this: Ajay 15-JUL-02 500 Bhavani 15-JUL-02 700 Chakr...
16 July 2008
Oracle 10g upgrade: Tom You mentioned that the AskTom site was recently upgraded to 10g. I take it therefore that you consider it to be of sufficient quality to run production code, even though the officially released version is only a couple of months old ? (...)
16 July 2008
Transaction and SCN: <code>Tom I need to gather the TPS(transactions per second)of my database. I used two methods below to find the SCN 1) Prior to 9i alter system checkpoint select checkpoint_change# from v$database 2) Oracle 9i select (...)
15 July 2008
regular table to partition table: <code>Tom, I have a situation where i need your expertise advice. Our product, at the client site, creates table and populates data. The table creation happens once in a while depending upon the client's requirement. As Oracle releases with a (...)
15 July 2008
Open cursors exceeded: <code> Hai Tom, I have a oracle 8 production database . I am getting an error like ' ora-01000 maximum open cursors exceeded' . I got a solution from oracle docs.It suggests that I have to increase the value of no.of open cursors (...)
15 July 2008
High Watermark (was White watermark): What is the main purpose of white watermark and in a case secnario where all the records have been deleted and we try to lookup that table why does the data is looked untill it reaches to an end of whte watermark
15 July 2008
Why PK ... why not unique index + not null: <code>Hi Tom, I just got one question. In one of the table design, developer came up with the question, Why should one use Primary Key and why not Unique index + NOT NULL. It does the same thing. He is not expecting any FKs to this table (...)
15 July 2008
Sequences: <code>Hi Tom, What you suggest is the best way to generate system generated numbers. As Oracle Sequences are not gap free, can you please suggets an alternative. Thanks.. Prasad </code>
11 July 2008
Database centric architecture: <code>Tom, I just wanted to ask a question and get your opinion or some links to some good sources for information that you may know of. The company I am working for we have an application where 90% of the business logic is in the database and the (...)
11 July 2008
join example, new vs old syntax: <code>Hi Tom, I know in oracle9i we have the cross join and full outer join. Do they exist for 8i if so can you point me to the documentation. If they don't can you give an example/breakdown of how to rewrite in 8i. Thanks in advance, (...)
11 July 2008
sqlplus command line usage: <code>Hi Tom, I am calling sqlplus from a batch file .. I want to call sqlplus and execute a packaged procedure in 1 line.. Is it possible ? currently i am calling a .sql file as : sqlplus scott/tiger @upd1.sql wht i want to do is (...)
11 July 2008
Adding columns to a table: I'm using an "ALTER ADD TABLE ADD COLUMN..." command to add multiple columns to a table. Is the order of new columns in the altered table determined by the order in the statement, or is does the database do some optimization, potentially leading to (...)
11 July 2008
Character data type usage: I need to know which is the best character data type to store variable employee names in Oracle. Is it Char, varchar, varchar2 or anything else. Let us say, I store 'Kapil' and 'Kapil ' which are two distinct names under each of the data types. (...)
11 July 2008
Who is Clarke??: We used the export function of Toad to get a large table out of production to load to our dev database. Unfortunately, the thing exported all 6 million rows as insert statements instead of just data. So to load it, I'm using SQL+ in 'run a script (...)
11 July 2008
Select query if behaving strangegly depending on the repositioning of where clauses.: When i execute the query <b>select * from cust_mv where cust_no in (00002438)</b> i get "Invalid Number" error. I know the reason because, i haven't given the quotes. But when i tried to do a simple join to another table <u>idn_sys_membs</u>, i (...)
11 July 2008
Storing Files in an Oracle Database: Hi Tom, if possible could you please explain the advantages and dis-advantages of storing files within an Oracle database. We are currently using 9.2 and 10g. We have no immediate plans to move to 11g. Thanks, we all appreciate your (...)
11 July 2008
What are considerations for Highly Transactional Database: Hi, Can you please tell me about considerations for highly transactional database?.Is Oracle 10g RAC better or Oracle DB with dataguard? Thanks. Regards, Rashmik.
11 July 2008
soft parse: <code>Tom How is it possible that we avoid a softparse? My understanding is that when the first time a query is given it is hard parsed i.e. it reads the disk. But after that every time you give that query it is read from memory i.e. (...)
11 July 2008
WHERE w/ CASE WHEN and NESTED CASE WHEN: Good day. I'm trying to use nested "CASE WHEN" clauses in my WHERE statement to in essence create a dynamic query based on a few input variables... I know there are other programming languages available to me, but I'm trying to keep to as much (...)
11 July 2008
Read Consistency of a function within a single SQL: Hi Tom, do we have read consistancy when we are using a function in a single SQL, but the function itself executes some SELECTS. Example: SELECT col1,col2,func1(col3) FROM t; create or replace function func1(p_parm varchar2) returning number (...)
10 July 2008
Undo Tablespace Blocksize: Tom, With the support of multiple block size in a database, how does a single UNDO tablespace store the before-images of data blocks from tablespaces with different blocksizes? Is there any performance impact on this? Thanks, (...)
10 July 2008
Currently running SQL statements: <code>How do you interpret LAST_CALL_ET field from the v$session view? What is the significance of this number field? Can I get the execution time by getting the difference between LOGON_TIME and SYSDATE? How do I find the (...)
10 July 2008
Calcluating "ending inventory" from a table using "open" and "close" dates: I work with Oracle-based case management systems, and I've been asked to create a query that shows an inventory of case rows still open at the end of each day in a month. Each row in the table I'm querying (called ISSUE) has a 'open' date called (...)
10 July 2008
Performance poor after analyzeing table: <code>Hi Tom, As my understanding, it will use Cost Based Optimzer after analyzing a table. But sometimes the preformance is very pool after analyzing. So shall we need to monitor all SQLs and change them to use Hint if have performance problems? (...)
10 July 2008
Format the Number for display: <code>Hello Guru, Q1) I need to display numbers from a database in a specific format. Table Tn (n number(6,3)); The format is 999.999 SQL> insert into tn values( 123.123) ; 1 row created. SQL> insert into tn values(0) ; 1 row created. (...)
10 July 2008
Regarding On Update Cascade: <code>Dear Tom, We know that when we delete a parent record, automatically child record also will be deleted if we used "on delete cascade". Is it possible to update automatically a child record when we update parent record? (Do we have "On (...)
10 July 2008
Partitioning question: Let's say we have a table of documents with key of doc_id. This table contains millions of documents(rows). There is also a table that assigns one or more industry codes to each doc: create table doc_ind ( doc_id integer, (...)
10 July 2008
Analytical Function - Individual Address Start date: create table test68 ( i_id number, a_id number, ts date ) ; INSERT INTO test68 VALUES ( 123,67,to_date('20070101','YYYYMMDD')); INSERT INTO test68 VALUES ( 123,68,to_date('20070115','YYYYMMDD')); INSERT INTO test68 VALUES ( (...)
10 July 2008
Partition elimination: <code>Hi Tom, I have a table that is using range partitioning. I have a query I feel should be utilizing partition elimination but that doesn't show up from observing autotrace output. The table has 340,632 blocks. The autotrace output shows a (...)
9 July 2008
Fastest way to diff table from wo databases: <code>Tom, To diff the contents of the same table on two dabatabases: Method 1: (the old/easy way) select * from tab minus tab@remote_db; select * from tab@remote_db minus tab; Method 2: I learned from you; this is faster: select (...)
9 July 2008
Constraints: <code>Tom, Are the following a full list of all possible user constraint_types P - primary key? C - check? R - foreign key? U - unique ? And what are their full meanings? Are the dab constraint_types the same? Thanks Brian (...)
9 July 2008
Parse CPU to Parse Elapsed: <code>Hi Tom, I got given a statspack report to check out. The Parse CPU to Parse Elapsed seems terrible. In you book Expert 1 on 1 page 481 your example was 87.88% and you say for every CPU second it spent about 1.13 seconds of wall clock (...)
9 July 2008
compatible init.ora parameter: <code>need extensive documentation on compatible parameter any pointers ? Thanks </code>
9 July 2008
Optimal number of partitions: Tom, I have this table with around one million records. The table has the potential to grow to 2 million, 3 million or even more... I am planning on partitioning this table using hash partitioning on the most queried column. A few questions: (...)
8 July 2008
Saving more than TWO millions pictures in the database: Dear Tom: In one of your answers related to dealing with pictures, you suggest to save them in the database rather than in a folder on the server. This is fine with me as long as we are talking about a relatively small number of objets (...)
8 July 2008
Function based vs. regular index usage during sort operations: Hi Tom, I'm trying to figure out why a regular b-tree index is used during some sort operations and a function based index is not. Here's an example. <code> create table sort_test1 as select a.*, initcap(object_name) mixed_object_name (...)
8 July 2008
OWS* roles in database: Hi Tom, I have searched Metalink, OTN and other on-line repositories of Oracle-related information, and have not been able to find an answer to this question posed to me by some friendly auditors. I have three roles in a database I inherited (...)
8 July 2008
temp tablespace location: Hello Tom, On UNIX systems can I use /tmp location for creating temporary tablespaces, because we don't care if the tempfile disappers after a reboot? I know it works because I tested it, but my question is, is that an acceptable thing to do? will (...)
8 July 2008
oci or hibernate ?: Hello Tom, I found your side an I am sure you can give me an neutral answer... I use oracle since about one year, it's really the best database I found. I am also programming Visual C++ since years, so that I access to Oracle with the OCI, not (...)
8 July 2008
Bulk insert and forall in remote table.: Hi Tom, Is there a way i can perform bulk insert (forall) into a remote table. Please advise... Thanks in advance Tom.
8 July 2008
inserting not printable character in sqlplus: I am building triggers on the fly using tables in user_tables. select 'create or replace trigger ' ||table_name || '_triggername before insert.........END; / ' from user_tables; How do I insert a Carriage Return (CR) between the 'END; / ' so (...)
8 July 2008
How to fast refresh MVIEW instantaneously across a db_link: Can NOT create materialized view over db link with "ON COMMIT" refresh option, failed with the following error. But if create the same MV in local database without db_link, everything works perfectly with the exact same "create materialized view (...)
8 July 2008
Index Quality Evaluation: Our application vendor defines "index quality" as a number (between 0 and 100, I think) computed as follows: <code> SELECT I.OWNER, I.INDEX_NAME, ROUND((SUM(TC.AVG_COL_LEN + 1) + 7) * I.NUM_ROWS / (I.LEAF_BLOCKS * (8079 - 23 * (...)
8 July 2008
Language Issue: <code>Hi Tom, I have faced a problem with NUMBER datatype. Currently I am working on a code dump from Finland. The dump contains the command "REPLACE(total_sum,'.',',')". This code works in Finland. But it does not work when I import the (...)
8 July 2008
History of DDLs executed on a table: I am just curious to know all the DDL that have been fired on a table and the user that fired them. There has been some changes to the structure of a table and I wanted to know who might have changed the table structure. Is there a way to know this (...)
7 July 2008
Protecting "some" data using VPD: Tom, Here is the situation. We have a table called TAB (tab_id, tab_name, labelcol). This table is protected by VPD and the predicate for our VPD policy is simply labelcol = sys_context('labelcol','currlabel'). So, each row in this table (...)
7 July 2008
Reset unrecoverable_time in v$datafile: When I query v$datafile looking for unrecoverable changes, I get something like this as the result set. <code> select df.tablespace_name, df.file_name, v.unrecoverable_time, v.unrecoverable_change# from dba_data_files (...)
7 July 2008
DB File Scattered Read: Hi Tom 1) Is Db File Scattered Read event not an I/O thingy? Why is always CPU hitting high when there's a DB File scattered read event besides I/O? 2) If my tablespace is 64KB block size and I set db_file_multiblock_read_count=128 will the (...)
7 July 2008
Backup and Recovery: Tom, In responding to a question you mentioned that when you take a cold backup in NoArchiveLog Mode, it is a bad idea to include redo log files, in case you later decided to put the database in archivelog mode. I understand that if we (...)
7 July 2008
Evolute from a Developer to DBA: Hi Tom, I found this website by accident one year ago and get addicted to it in just a couple of hours. Thank you for all the interesting and useful instructions/suggestions. I am a database developer who uses PL/SQL to code everyday. I always (...)
7 July 2008
sqlloader versus external table.: <code>hi Tom, Is there any advantage of sql* Loader over the new 9i feature external table. Which of them is faster. sql* loader direct path load or direct path load from the external table. thanx in advance Nikhil India (...)
7 July 2008
Select Count(1): How it works: <code>Hi, Will the following code ever result in l_num_rec_count being more than 1 in any case? SELECT count(1) INTO l_num_rec_count FROM <Table Name> WHERE <condition1> AND <condition2>; I am unable to find syntax of count(1) and I (...)
7 July 2008
Sybase to Oracle: <code>Hello,Tom My company plan to migrate from "Sybase SQL Anywhere 5.0" to "Oracle 8.16". Do you know any tools we can do this? Where can I download for testing purpose? Thanks always. Shelly (...)
7 July 2008
Trace file: <code>Hi Tom IS there any way to create separate trace file for different alter session set sql_trace=true comamnd. My problem is in a connection pool scenarion . If I wanto trace a purticular user I am not able to cretae a separate file. (...)
7 July 2008
Unable to drop user: <code>Dear Tom, I just simply can't drop one user in sqlplus. SQL> drop user a; drop user a * ERROR at line 1: ORA-00604: error occurred at recursive SQL ldwvel 1 ORA-00942: table or view does not exist ORA-06512: at line 7 I also (...)
7 July 2008
adaptive cursor sharing in 11g: hi tom after reading your article about adaptive cursor sharing in 11g from http://optimizermagic.blogspot.com/ I have some questions 1. Does oracle 11g calculate histograms for all tables and are histograms are enabled by default in 11g. (...)
7 July 2008
Number Datatype: <code>Hi Tom, I declared the datatype of a column of a table as NUMBER & Presumed it to be Number(38) to be specific. But What I found to be strange that, it is accepting digits beyong 38 digits.ie. 1.111E+125. If the Datatype's precision is (...)
7 July 2008
Oracle Text - handling plurals: <code>Hi Tom, I've created a new Oracle Text index with a user defined datastore. The index works, but I'm having trouble understanding how Oracle Text handles plural vs. singular when searching. For example: SQL> insert into foo (col1) (...)
7 July 2008
Parameterized View & Truncation of Table: <code>Dear Tom Hi!!! My problems... 1)Why does'nt Oracle permits to create a Parameterized View in the lines of a Parameterized Cursor. I think there should not be any problem of creating a parameterized view because in case of a view only its (...)
7 July 2008
Interpreting the Statspack report.: <code>Tom, I am running Oracle 9.2.0.4 on ALPHA OPEN VMS with 2 CPU's. I was reading this STATSPACK report, Could you please help me interpret this See the snippets of this report. DB Name DB Id Instance (...)
7 July 2008
Comparing Apache/mod_plsql and dbms_epg: Dear Tom, With 11g Oracle now seems to use dbms_epg as the default engine for APEX. Could you elaborate a little on the differences and give us some hints on if and how to migrate existing Apache/mod_plsql (but not APEX) application to dbms_epg. (...)
1 July 2008
Database Recovery: <code>Hi Tom, by mistake, we had delete one datafile and now the DB could not be open, but can be mount. Unfortunately, we are running our DB in NOARCHIVE mode and we don't hav a backup too. b'coz we are fools (I just came to know that.) plz. (...)
28 June 2008
Update statement and Null-values -- update a table from another table: <code>Tom, When updating a column with an update-statement, the value of some records (records that don't need to be updated), are changed into the value NULL. I use the next statement: update table name B set columnname = (...)
28 June 2008
Second Edition of "Expert One on One Oracle": <code>Ok, so it has been four years since Expert One on One Oracle has been released... In the way back machine, we see that it was released one week before 9iR1 went production and since then, we've had 9ir1, 9ir2 and 10gr1 go out the door. (...)
28 June 2008
Restricted session: <code>Hi! Where do I find if session is in restricted mode ? After I executed the "alter system enable restricted session;" command . </code>
28 June 2008
Application contexts in a web environment: We have a web application that performs all its database accesses via stored procedures. Like many applications, we're using an application server that manages its own pool of connections, each of which is logged in using the same username. We (...)
27 June 2008
Hierarchical Tree: <code>I have a table: SQL> desc emp; Name Null? Type ------------------------------- -------- ---- EMP_ID NUMBER NAME VARCHAR2(10) (...)
27 June 2008
CPU utilization: <code>How can I reduce CPU utilization of Oracle database? </code>
27 June 2008
Merge - oracle 9i: <code> Tom: I am using the merge command, using the this program with this message: MERGE INTO newemp n1 USING external_table e1 ON ( n1.empno = e1.empno ) WHEN MATCHED THEN UPDATE SET n1.empno = e1.empno, (...)
27 June 2008
Help required to obtain output for variable string length: I had the following requirement where I need to get the below output create table test_table1(xstring varchar2(20)); insert into test_table1 values('11111'); insert into test_table1 values('101011'); insert into test_table1 values('1000111');...
27 June 2008
If one column is not null then another column should be not null, both can be nulls: Hi Tom, My requirement is : If col1 is having a value then col2 should have a value and vice versa. Also the combination of col1, col2 should be unique. Both the columns can be null. I am trying to create a unique index on the combination (...)
27 June 2008
Schema vs User: Hi Tom, When an user is created using "create user" it will create a user as well as a schema with the same name as user. Also, there is a way to create only schema as well, using "create schema". But how do I create a user without and associated (...)
27 June 2008
using aggregate functions: <code>What is the best way to get the 'other columns' also of rows retruned when using aggregate functions. For eg. select max(inv_amt), cust_code from invoices group by cust_code; Now, say if you want to get the (...)
27 June 2008
Listener Question: <code>Hi Tom, This is an urgent query. Please let me know if the port 1521 is default port for the TNSLinstener. I have a database server on port 1527 how can I make the clients connect on this port or can I have one listener service connect to (...)
27 June 2008
Questions on Locking: Hi Tom, Here are the few questions on Locking. 1)How do I use v$lock to figure out lock details ? Details like whos has locked,on which table,who else is waiting, and what type of locking (shared or exlusive ?), since when it is (...)
27 June 2008
advantage of procedure within a procedure: I've seen package code where there are procedures defined within a procedure, then the "within" procedure is called once in the main procedure - actually I've also seen 3 "within" procedures and the only thing the main procedure does is call these 3 (...)
26 June 2008
Bulk deletes: Hi, Tom. I read the articles about the bulk fetches to move large numbers of rows. What I did not see was a great way of DELETING stale data. I need to take large numbers of rows and place them into an archive table (both need to be online, but (...)
26 June 2008
lookups: <code>Hi tom, I am developing a personnel system which will have a master table for the employee information and a lot of lookups that describe his relegion, gender, qualification, degree, work unit, department and more ... suppose we have the (...)
26 June 2008
effect of UNDO_RETENTION: <code>Hi Tom, If I shutdown immediate a Database that should rollback a huge transaction, How does the UNDO_RETENTION time comes into play?? In Oracle Student Guide it says it could take upto the time specified in UNDO_RETENTION to shutdown the (...)
26 June 2008
Replicate or not !!!: <code> Hi Tom, I have to two databases and what i need to do is synchronize 5 tables of one database with other database. Tom do you suggest i should go with replication or i should create a link with trigger on these tables so that these (...)
26 June 2008
Suggested way to keep test environment up2date: Dear Tom, I wanted to ask how you would keep a test environment up2date. I'm currently typically using exp/imp to copy the contents of a production system to a test system and am looking into alternatives. The process should possible be: (...)
25 June 2008
General PL/SQL Record type: Hi, i have number of tables (about 100), which have common columns. For example: table T1(C1 number, C2 number, T11 number, T12 number, ...) table T2(C1 number, C2 number, T21 number, T22 number, ...) table T3(C1 number, C2 number, T31 number, (...)
25 June 2008
ORA-01401: Inserted value too large for column: <code>Hi Tom, Thank you so much for what you are doing. Here's an Oracle message that is familiar to many of us developers. "ORA-01401: Inserted value too large for column" It doesn't tell you which column is too large, it leaves it to us to (...)
25 June 2008
Tkprof use for tuning a query....: Hi sir, How can I use TKPROF to find the problematics queries?The only point that I know is that when the elapsed time is more higher that means that query spent more time waiting on something.But still I am not surehow to use TKPROf for the query (...)
25 June 2008
pga_aggregate_target: <code>Hi Tom I would like to take advantage of the new pga_aggregate_target that is available in 9.2 to allow dynamic automatic management of the sessions pga-memory allocation. My question is: how does the currently available pga-memory affect (...)
25 June 2008
NVALID OBJECTS COUNT RANDOMLY INCREASING AND DECREASING: Hi Tom, We have been noticing that the invalid objects count in our EBS database goes up and down randomly. When urltp.sql is run, the count goes down to 47. But after sometime, the count goes back up to (say) 95 and back down to (say) 75. When (...)
25 June 2008
unexpected cardinatlity results with and without statistics: In an attempt to diagnose some performance issues, I observed that a query's cardinality seemed to be more accurate once I deleted my table statistics. My execution plans and LIO all seemed to be unaffected but I'm wondering if I may have other (...)
24 June 2008
implicit conversion while using OPERATOR: Why we need OPERATOR instead of just FUNCTION? Why we need extra layer between user and function? While calling function in SQL or PL/SQL block, oracle implicitly converting data type (in example 1 and `2? values are implicitly converted to (...)
24 June 2008
dbms_space.free_space: <code> What is the use of this stored proc? It tells you how many blocks have free space for updates, right ? But it does not tell you how much free space in each block. We can get free space info. from dba_free_space. Can you show how this (...)
24 June 2008
Why index other users table?: Tom, Developers have been giving me headaches because they keep creating indexes under users that do not own the table. I continually ask them to quit doing it because it makes packaging up changes to roll to other environments tedious and more (...)
24 June 2008
Disk fragmentation: Hi Tom, Apologies if this question has already been asked, or is too general. I was wondering if data retrieval in Oracle is likely to be adversely affected by disk fragmentation (particularly on Windows systems, where data is held on a single (...)
24 June 2008
CURSOR_SHARING=FORCE: Sir, recently in one book i read, "In the old days, it was up to the developers to build applications which relied upon sql statements which contained bind variables. This forced the Oracle kernel to avoid caching similar sql (...)
24 June 2008
Cursors versus REF Cursors versus NDS: <code>Tom: Even though I have been using implicit cursors and REF cursors I am still unclear on when do you use each: 1. For regular cursors, which are pointers to a SQL statement result set, you can use either explicit or implicit. Implicit (...)
24 June 2008
Ref cursor to bulk fetch all column values along with rowids: Hi Tom, I am trying to write a procedure which would take a given table and bulk collect into 'table of rowtype' variables along with rowids (table of rowid). Something like below. create table vtest1 (a number, b number); 1. Works fine (...)
24 June 2008
Data Access Layer, O/R Mapping: Mr. Kyte, I enjoy reading the data on your web site. I have searched it extensively using the search words "hibernate, toplink, object relational mapping," and so on. I would like to append a question to the article (...)
23 June 2008
10g shrink space with compressed table: <code>SQL> SQL> select tablespace_name, def_tab_compression, segment_space_management 2 from dba_tablespaces; TABLESPACE_NAME DEF_TAB_ SEGMEN (...)
23 June 2008
Create trigger on different user with different schema: Please correct me, I create trigger on different user than the owner of the trigger. Is this action possible? RK@orcl3> create table t (x int); Table created. RK@orcl3> grant all on t to bahan; Grant succeeded. RK@orcl3> conn bahan (...)
23 June 2008
connect by: <code>hi tom can u explain me in detail about "start with connect by" sql statement (tree structure).i know there is documentatin but it is very confusing. </code>
23 June 2008
Multilingual Database and UTF-8: <code>Greetings Tom !! I am always here when I need to get that 100% right answer and so here is my question . The existing Database (WE8ISO8859P1 Char Set) needs to cater to English ,Chinese,Japanese and Korean as well . Before I opt to (...)
23 June 2008
Rows into columns: <code>Hi Tom, I have a query sql> SELECT tr_date, item_id, adult_price adult, child_price child FROM mytable WHERE tr_date BETWEEN '01-jan-2004' AND '31-jan-2004' AND tr_code LIKE 'D%' The output is TR_DATE ITEM_ID (...)
23 June 2008
moving tables across tablespaces: <code>Tom, What is the best way to move tables from one tablespace to another tablespace in the same instance? </code>
23 June 2008
Solaris direct i/o: <code>Tom, We are running Oracle database (mostly 8.1.7) on Solaris 2.6 and 2.8. We are interested use of Solaris UFS direct i/o feature. You mentioned at NYOUG meeting that you have been using the feature. Do you see a significant (...)
23 June 2008
Direct-path INSERTs are not propagated during a "Materialized View Fast Refresh": <code>Hello The Oracle8i Concepts Guide mentions on page 10-19 that "For Materialized Views that use the fast refresh method, a materialized view log or direct-loader log keeps track of the changes to the master tables". From what i read, I (...)
23 June 2008
ORA-01555: snapshot too old: <code>I've been dealing with this error off and on for the last 3 years and I just wanted to verify that my understanding of its cause is accurate. Cause 1) There is only one user logged into the database. That user is (...)
23 June 2008
Determining the row locked ina table: <code>Tom, I want to be able to figure out as to which rows in a table are locked. I can get to know which table is locked in a database using a join between dba_objects and v$locked_object. But How do i get to the row level in that table. (...)
23 June 2008
Blocks - 2 or 4 or 8k?: <code>What benefits can be gained from changing from 2k blocks to 4k blocks or even 8k blocks </code>
23 June 2008
snapshot too old error: <code>Tom, Would you tell me what snapshot too old error. When does it happen? What's the possible causes? How to fix it? Thank you very much. Jane </code>
22 June 2008
How to tell if DB has been upgraded to the patch release: <code>I was wondering if there was a way to verify if the Oracle data dictionary has been upgrade to the patch release that is identified in the version that is displayed when we login: sqlplus scott/tiger SQL*Plus: Release 9.2.0.5.0 - (...)
22 June 2008
Function in SQL: Hi Tom, Thanks for the reply. fn_get will do the following: Its basically passing the tier_id (col_a) and tier_amount (col_b). We need to find which group this one belong to. So, most of the record will be unique for combination of col_a and (...)
20 June 2008
query performance is bad: Tom, I've a query is running more than 2 hrs with out giving any results. please advice on this. thank you very much. here is the query SELECT /* From med_clm_hsty table */ a.med_clm_sk, a.src_sys_cd, a.clm_stu_cd_sk, a.pat_dcrg_stu_...
20 June 2008
Outer Join why is it so expensive?: <code> select columns from tab1 (1000 rows) tab2 (800 rows) tab3 (299 rows) where tab1.primarykey = tab2.primarykey and tab23.primarykey =tab2.primarykey(+); The above is one of the scenarios I'm facing Tom I have (...)
20 June 2008
SQLLoader: Hi Tom, I need to load the data from text file into multiple tables. In each row, the first, second, fourth fields should go into table"A", third, sixth fields should go into table"B" and fifth field should go into table "C". The (...)
20 June 2008