Wonder why ddl

Worked like a breeze!!! A reader, June 29, - am UTC. The above followup along with a couple of cool formatting tips from this same thread worked like a breeze and I have now create a generic script to generate create user script for any user entered as an input variable. Thankyou for making our jobs easier!!! Thet schema has a lot of table and a lot of foreign key constraints. Thanks a lot Tom. August 03, - am UTC. Not sure, you'd want to work that with support. Hi Tom, For following issue, Oracle Support suggeting for patches 9.

Dump of memory from 0x to 0x [ Can you help regarding this. Regards, Suvendu. August 13, - am UTC. I wrote it for you. Sean, September 06, - pm UTC. Hi Tom, If I run 'create table t1 c1 number ' script while table exists already, I get the error message.

Is there any way to use the script to check whether table exists or not? It the table exists, drop it and create new one. If not, don't run drop table script and simply run create table script. I understand that one can always run drop table script and ignore the error if the table does not exist, but our client does not like error message even though it is harmless. Thanks so much for your help.

September 06, - pm UTC. But I just wanted to create a script.. This is becoming a "very long" task for me Do you have something up your sleeves.. September 07, - pm UTC. Moving longs to another tablespace mohini, September 07, - pm UTC.

I am trying to move the table with a long columns from one tablespace to another.. September 08, - am UTC. So as I understand.. Do you have a script handy for this.. September 08, - pm UTC. You can pre-create the table anywhere you like, you don't have to use create, use insert. How about trigers? Steve, October 03, - am UTC. How can I get that? October 03, - pm UTC. Hi tom, I have problem with index usage by table. After I import dump file, query doesn't use my indexes. But indexes are existing with Valid state.

I have Done: 1. Rebuild the indexes, then execute the query. Still it is not using. So finally I drop and recreate the tables, then query use the indexes perfectly. For testing: I have again import the dump, still got the same problem. Do you whats wrong with my system. I have oracle 9. October 28, - am UTC. Does any one have done some thing like this? December 13, - pm UTC. What privileges are needed to get the DDL?

You mention previously that it will not work for 'tables you are not allowed to see'. I get similar results in a 9. December 14, - am UTC. But I still got the same error. How to fix the package issue? December 14, - pm UTC. Charater set is UTF8, database version is 9. I found many of our databases have the same issue.

Most of our databases were created with some scripts. Not sure whether the scripts are missing something or parameters set up causes the problem. I searched on metalink and didn't find the solution. December 15, - pm UTC. It is a bug and need a patch. January 13, - am UTC. I went through the Security Model section you mentioned above several times but still can't get it to work.

January 13, - pm UTC. I cannot reproduce, if you do the following script, what happens for you? Raj, March 09, - am UTC. Hi Tom, Are you not there?? March 09, - pm UTC. Use DBCA, that'll create create database scripts from cradle to grave. Kindly guide me where I have gone wrong. March 29, - am UTC. Which is little time consuming. Just curious,to know any other "faster" way to extract DDL details.

Kindly let me know which way you will prefer to go. March 31, - pm UTC. Need more information - what about partitioned indexes?

This is a 9. Please let me know if there is any way I can get the ddl script. These scripts for some reason is neither available with the previous DBAs nor with the developers!

Thanks, Srinivas. June 22, - pm UTC. Just one more clarification, can this script be used to create b-tree index? Thanks very much Tom! I greatly appreciate it! But can we remove or add the "bitmap" keyword and create a b-tree or bitmap index keeping the other values as it is in the script?

The reason I am asking this is I am working on a troublesome report now and I feel some of indexes are wronly created, so I want to check for the peformance by changing the index types. Thanks much! June 23, - am UTC. You need not get them on all columns. Thanks very much! The information is very useful! June 26, - am UTC. September 01, - am UTC. Lots of lines in one statement in foo. When I export the ddl into foo. Is there any method to combine these lines to one line for each SQL statement in the foo.

But I have to use the grep. September 20, - pm UTC. It works fine. October 19, - pm UTC. October 20, - am UTC. June 01, - pm UTC. A reader, June 04, - pm UTC. June 06, - pm UTC. Java - hard, plsql easy. Is there a magic???? In a process I have the need to drop index of certain table and do update on that table and again create the index.

I have to drop the index because while the update is in progress the index field may have duplicate values but at the end of the update we take care that the values are unique, and the index is a unique index. So once the update is done I need to recreate the index. With this I get my create index script and I store it then drop the index and create it later. But one of our clients is in 8i so I need to do some thing.

So I have the following question. Is it possible to do the update without dropping the index? Or, Is there any thing like disabling an index for a while and enabling it afterwards. Note: Avoiding duplicate values in the process of update is not possible at this point, so we need to find a way to make the unique index disappear for a wile and then get it back. Regards, Karthick. Need some super magic karthick, September 06, - am UTC.

But my problem is Our application runs over the copy of people soft production data base which will be send out for testing by the client.

Our application masks the sensitive data. So once our application is done with what ever it has to do to the data the structure of the people soft database must be the same as the original.

I was searching for some thing like disabling the Index. Because once I made the index unusable I was not able to perform an insert on that table. Martina, I can get lots of procedures from the net and I also can build my own. Hi Tom, My Application is running on oracle 10g Relese Net and Oracleg. Here i am pasting Code ,which 'll defenetly help you to understand my question. November 21, - pm UTC. Because that is obviously, definitely, absolutely a bug in your code.

Your transactional logic is somewhat "botched" there. Do you see the flow of logic here - and how broken it is? This code doesn't need to be reworked to not use DDL, this code needs to be a restated as textual requirements b designed from scratch c implemented as if this bit didn't exist because there is just too much wrong with the existing code.

DO NOT commit or rollback in your stored procedures - not if you do not understand transaction processing. It is that easy. Thanks Tom for answring my question. Vishal Taneja. November 26, - am UTC. Just build a single query.

You don't need temporary tables to write SQL. You have with subqueries, you have inline views. You don't need to write data into tables, update it and then select it out that is soooooo sqlserver. Tom, The code I see here is great for tables, views and indexes. What about "soft objects" such as procedures, packages and functions? Thanks, Evan. May 19, - pm UTC. May 20, - am UTC. Hi Tom, Is there any way to look at the package s defintions only from the full user based export?

May 20, - pm UTC. Thanks a lot! A reader, May 20, - pm UTC. Thanks --Shukla. May 21, - am UTC. Tom, Hello again! One of your comments was: 32k linesize limits for text files is almost always better than good enough. I am trying to do the same, as a way to automate the backup of our code for each schema Wondering if you had any other suggestions? I am working with tables right now and have coded it this way June 19, - pm UTC.

You need source code control. Janel, June 19, - pm UTC. I am going to risk it and ask for some clarification. I am thinking you mean having, say, an outside application for source code versioning i. SourceSafe , or are you referring to something from Oracle?

Either way, we do not use anything like that Select statement works fine, and produces output. Note: I'd list the whole DDL here, but frankly i'm not sure it would fit. We're not sure what to do here. The original DB works fine, but it doesn't seem that we have any way of moving this view to another system July 24, - am UTC. I notice the single line comment before the first sum in the section of view text above: "select a. FYI: Turns out we ended up figuring out what the problem was.

Still not sure how this tool specifically: PLEdit by Bethnic Software managed to get code to compile that all others didn't Thanks for your help! August 04, - pm UTC. But I am having a problem with importing this, because the table objects are created with the original initial extent, which is in some cases very big and takes a lot of time and diskspace while creating.

My goal is to import non data objects before importing the data objects using transportable tablespace. Oracle Version 9. August 20, - am UTC. The tool always adds a space to the end of the code when it creates a procedure or package. SQLPlus always trims the space off the end unless execute immediate is used to create the procedure. February 04, - am UTC. May 11, - am UTC. Many times we need to export and import only structure from existing huge Database to new DB. Why does oracle not come up with export import option that allows us to export and import object definitions excluding storage clauses.

Getting indexfile formattting etc is very time consuming. Especially when we need to do the same over and over again. June 16, - pm UTC. Hi Tom, Thanks for your great site. I would request your help on the below case. I use 10g release 2. I have 2 packages in one Data base. These packages refers tables, indexes, sequences, views, some other stored procedures.

Now, I want to move these two packages to someother Data base. In the new DB i have to create all these referenced objects which are used by my 2 packages. Could you please share me, the best way to do these kind of things.

It would be great if you can share an example. Regards, Pointers. I cannot imagine the use case whereby I could point at a package and have that decide what tables should be created, I manage code in applications - you would just check out an application from your source code control system. Hi tom, Thanks for your reply to my earlier post. We dont use any source code control system. We simply load the package from server through plsql developer and work on it.

Is there any workaround to do the task. My aim is to get DDl in the form of script for all the package refered objects and if the object is table then the foreign key referred tables as well. Say, if package refers 'emp' table then I need the 'emp'table script and dept script as well, as emp.

I did like, I made a list to get all the refered objects in the package by going through the package code, then DDL for them, then created all these referred objects in another DB and finally package code. Have I done the right process. Please share your idea. July 06, - pm UTC. I'm going to constantly go back to "you need source code control" You don't like my answer, but it will not change. Scofield, July 09, - pm UTC.

Sir, After your enourmous contributions to Oracle community, Oracle company should build your statue to every Oracle head office in every country.. Hi - We are trying to come up with a way to generate ddl from our production system so that we could apply the generated ddl to our test environments and dont have to worry about any issues like synonyms, views etc getting out of sync when the test environment is refreshed.

The two methods that we considered were 1. We decided to go with the second option. Datapump looks to be so simple. Already have an account? Sign in. By signing up, you agree to our Terms of Use and Privacy Policy. Enter the email address associated with your account.

We'll send a magic link to your inbox. Email Address. All Sign in options. Enter a Email Address. Choose your interests Get the latest news, expert insights and market research, sent straight to your inbox. Newsletter Topics Select minimum 1 topic. Data Management.

Tags: IBM Db2. Sandeep Roy March 18, 0 Comments. Any explanation is appreciated. Nice explanation. Thanks, Tom. And just to emphasize, the implicit commits occur only for the DDL commands that change the data dictionary. Am I right, Tom? Just ddl that does stuff to the data dictionary. Why not autonomous transaction? Can you clarify?

June 24, - am UTC. Thanks Rangadham. February 26, - am UTC. Rangadham, March 17, - am UTC. March 17, - am UTC.