Hi I've made some changes to columns in a database table (increasing the size of varchar), however I need to have the view of the table refreshed to pick up this change. In SQL server 2005 I would use the script to -> alter to recreate the script for the view. I'm trying to figure out what the Oracle SQL command would be to rebuild the ...
Hi, I am trying to drop a tablespace in oracle 10g , using my application . A bit about my application -- In my application I can create tablespaces. Now what happens in oracle is that when you create a tablespace , then a new user automatically gets created and is attached to the database. When you have to drop a tablespace what one...
Hi! I'm wondering if its possible to create a view that automatically checks if there is a new monthly created table and if there is include that one? We have a new table created each month and each one ends with the number of the month, like table for January: table_1 table for February: table_2 etc... Is it possible to create a v...
Hi, I am trying to invoke ExecuteNonQuery() method of two different objects of OracleCommand class. Both the objects use same connection object but have different commandText and parameters. I am using ODP.net, C# (.net 2.0 framework) and Oracle 10g. Code snippet is as follows : // OpenDatabaseConnection() methods checks and opens dat...
I have a large statement: SELECT a.user_id, a.user_name, s.name, s.value, d.default FROM accounts a, settings s LEFT OUTER JOIN default d ON ( d.name = s.name ) WHERE s.user_id = a.user_id; The problem is that settings contains a large amount of entries and I need to pick the one with the highest ID. I can ...
Hi all, I'm using Oracle DB and I would like to write a SQL query that I could then call with JDBC. I'm not very familiar with SQL so if someone can help me, that could be great ! Here is the problem. I have a table MY_TABLE wich contains a list of another tables, and I would like to keep only the nonempty tables and those that their na...
I have a query select * from table where name in ('52 T&M', '60 T&M'); The "&" is causing the query to expect a parameter. How do I qualify the "&" in the query to sting so that the query can find string with the "&" character in them? ...
Hi, I have a table in Oracle and I want to select all the duplicated values, I mean: I can detect that a register is duplicated because some column contains the same value: ColumnA ColumnB 1 A 2 A 3 A 4 B I want to get something like: 1 A 1 A 1 A Thanks a lot. ...
Hi, I'm having some performance issues when querying a table in a production database. While the query runs in 2.1 seconds in the test database (returning 8640 of 28 million records), at production, it takes 2.05 minutes (returning 8640 of 31 million records). I'm having a hard time to find the problem since I'm not an oracle expert. S...
Hi, How to grant DDL privileges in oracle ? On database I've users SCHEMA_1, SCHEMA_2 and SCHEMA_3 and now i want to from schema_1 be able to do DDL only on SCHEMA_2 Is the grant is possible from SCHEMA_2 level or system only ? ...
I have a table with data as following: id col1 col2 1 c1 1 c2 2 b1 3 a3 4 e1 4 e2 Now I need to move this data in another table in such a way that the record that has same id goes as a single record in this new table. so the new table data would look like this: id col1 col2 1 c1 ...
I have to sync data from one Oracle db1 table tb1 to another db2's table tbl2. To keep simple, I can truncate data on db2 tbl2 and then copy all the data from db1 tbl1. Both tables have the same data column structure. Since two tables are on different databases, I am thinking to use a media file like txt or xml as a media. I export data...
I want to generate composite sequences in the following format: <Alphabet><2 digit numeric code> Each alphabet series will have numeric values ranging from 00 to 99. The initial value will be A00, the subsequent values will be A01, A02 and so on. Upon reaching A99, the next sequence should carry-on to B00. When the "B" series is exha...
I have a cursor that selects all rows in a table, a little over 500,000 rows. Read a row from cursor, INSERT into other table, which has two indexes, neither unique, one numeric, one 'DATE' type. COMMIT. Read next row from Cursor, INSERT...until Cursor is empty. All my DATE column's values are the same, from a timestamp initialized at t...
I need to import Oracle Database from production to development just for one schema. What's the easiest way to do this? What I did is to drop the user with this schema and recreate the user with the same options and run import command. Is this the right way to do? I use Oracle 10g and I am a developer not DBA but my DBA gives me the du...
I am havng the following problem after installing oracle 10g on vista machine and running an asp.net application. Rather strange: One 2010 project on the same machine using the same database is able to work but the other that was converted to 2010 recently isnt working on the same machine. The error is : OCIEnvCreate failed with return...
I have the following code that works fine in MS SQL Server: delete grp from grp left join my_data on grp.id1 = my_data.id1 and grp.id2 = my_data.id2 and grp.id3 = my_data.id3 and grp.id4 = my_data.id4 where my_data.id1 is NULL Basically, I want to delete all occurrence that can be found in grp and don't have any equivalence in my_data...
I need to automate a selective table / user object backup I currently am doing via PL / SQL Developer. The way I currently do it is via Tools/Export Tables and Tools/Export User Objects, manually select tables / objects, then set the options, choose destination and export. I do this from a windows laptop and the database is located in a...
Hi all, I'm very newbie for Oracle and very not sure about Oracle. one question I want to know. if I want develop oracle windows application. first I must install oracle on server for database server but I'm not sure if I don't want install oracle on client. I must install oracle for client YES or NO !? thank for help. ...
I have a table with this structure: date colname value ---------------------------- date col1name col1's value date col2name col2's value date col3name col3's value Now I need to convert it into this structure: date col1name col2name col3name ----------------------------------------------- date col1's ...