Tag Info

Hot answers tagged

13

No. All (standard) packages are written in PL/SQL. The DBMS engine itself is written in C Edit: Oracle does include a JVM which runs on the same machine as the database itself, but that is not used to run any "DBMS related" code. It's only there to run stored procedures/functions written in Java.


10

In addition to Craig's advice I would like to advise you to examine the storage parameters of the affected tables. I am currently in a similar situation to yours. The largest table in my system contains ~200 million records and the performance was really bad. Tune the storage parameters of your tables and indexes Besides adding several indexes to the ...


6

Deferred indexing would be nice, but isn't currently supported. Adding indexes has a cost - write performance. They're a trade-off. COPY won't help much if index maintenance is the main issue. The simplest solution is to drop the indexes, and re-create them when you're done importing. Since you can live with losing all your data if the DB crashes, you ...


6

There's a nice technology available in Oracle called External Tables. In your scenario, you could access your external plain-text data using External Tables from within the database and update your existing data in database with SQL statements you love and are used to – for example, INSERT, MERGE etc. In most cases, using Oracle supplied utilities is the ...


5

Just saw the update, 60-col table with mostly VARCHAR(2k) fields -- that is (potentially) a monster table. First things first... You have to understand your bottleneck FIRST. On the app side, go all the way back to your single-threaded batch-insert solution (1/2/3k at a time) and begin running it and login to the DB machine and run a 'top' -- see how much ...


5

If someone can point me to a better, performant way of searching for a non-primary key data [...] Add an index in your database on the column(s) you are filtering on - in this case COL_5. The resulting execution plan should be a single index seek and the performanze difference negligible (or even zero). Also rather than getting the entire result set, ...


4

The simple version would be to generate a hash from the CLOB and use this as a key. This key will fit into the allowed key width for your engine (900 SQL Server, 767 InnoDB, 1000 MyISAM etc). The hash can be generated by the engine as a computed column, trigger, or by some ETL process, or by the application There is a faint chance of collision (birthday ...


4

Invoking direct path insert with the append hint causes an exclusive lock to be taken against the entire table, so having multiple threads performing the insert will not help. You would need to explicitly address a different partition with each insert ... insert /*+ append */ into my_table partition (partition_name_1) ... ... to get partition level ...


3

Incorrect syntax near '-'. This tells me that you've named a database, table or column with a dash in it, not that POJO is having an issue mapping your columns. As an example, if you've named your entity bar-none and POJO issues the following CREATE TABLE statement: CREATE TABLE dbo.foo(bar-none BIT); Or this one: CREATE TABLE dbo.foo-bar(none ...


3

Your trigger doesn't need the CALL keyword. create or replace trigger TRYTABLE_BEF_UPD_ROW before update or insert on TryTable for each row begin TryJavaHelperRun(:new.arg1, :new.arg2); end; / Generally, you should never use CALL in a PL/SQL block-- just execute the procedure. I assume that CALL is some ancient leftover syntactic remnant from some ...


3

Already answered at a parallel thread on serverfault: http://serverfault.com/questions/345253/oracle-11-updating-blob-field-db-file-sequential-read-inappropriately-slow/345588#345588 In Oracle, LOB (including BLOB) is stored as: in-the-table LOB - if the LOB is smaller than 3900 bytes it can be stored inside the table row; by default this is ...


3

You can get most of those messages, but unfortunately not all. See my question on Stackoverflow regarding that. In general those messages (e.g. messages from a PRINT statement) are returned as warnings on the Statement object by the JDBC driver. To retrieve them use Statement.getWarnings() in a loop: Statement stmt = ...; stmt.execute("some sql"); ...


3

You ask two quesions, firstly: is there a significant difference in terms of scan speed when searching for a data in a table that containts 350 columns than in table containing 25 columns? If you have an index defined, then no, there should be no difference. If you don't have an index, then there may be a difference. Assuming you don't have ...


3

Consider the example below: CREATE TABLE test.child ( id integer, p_id1 integer, p_id2 integer ); CREATE OR REPLACE FUNCTION named_cols() RETURNS SETOF test.child LANGUAGE sql AS $$ SELECT 0, 12, ...


3

Reading the log seems to make the problem fairly apparent: 2013-11-27T15:08:08.791992Z 9 Query drop table devicedata That's not mysql dropping the table. That's your application dropping the table. There's another line in the log that is very suspicious: ,s.Plan_Hash_Value FROM V$sql s WHERE s.Sql_Text LIKE 'SELECT /*+ FIRST_ROWS XX */ V$sql is ...


2

Take a look at Comparison of database tools, there you find some tools supporting both HSQL and MySQL. Then it should be possible to export the MySQL DB and directly import to HSQL DB from the same GUI.


2

I am not sure whether you need compulsory callable statement or not. If you are ready to use other ways, best way is convert your Excel sheet into CSV file and you can directly load data from CSV file into database by using following syntax: "Load data infile "c:/filename.csv" into table tablename Fields terminated by ',' Lines terminated by '\r\n'" This ...


2

Compiler messages are not returned to the client through the getWarnings() on the statement or the connection. Instead once you have identified the warning through Statement.getWarnings() you need to parse retrieve the compiler errors from the view ALL_ERRORS Something like: SELECT line, position, text, name, type FROM ...


2

This is probably not the best way to do what you want to do. The "proper" way would probably be to execute the SQL commands in your file via jdbc. That said, that's a long way from where you are now, and we can probably make what you are trying to do work. The likely problem is that there is a space in the path to psql.exe. When you use that particular ...


2

The parentheses form a row-constructor, so your query returns a single column row literal, essentially an anonymous composite type. Compare: regress=> SELECT (1,2); row ------- (1,2) (1 row) regress=> SELECT 1, 2; ?column? | ?column? ----------+---------- 1 | 2 (1 row) You would've quickly realised this if you'd run the query ...


2

I was able to move the Java code to Oracle. I am writing this for people who are facing similar issue. Log on to server where Oracle database is installed through Putty (command prompt in case of windows) Place the Java file in server using WinSCP. Find the Java compiler present in oracle home using command find / -name javac Compile the Java file using ...


2

Instead of EXEC, you should put it between begin ... end, or use the { call } syntax. Here is an example, in the documentation: http://docs.oracle.com/cd/E11882_01/java.112/e16548/getsta.htm#i1014127


1

I have two suggestions Commercial Product There is a product called ScaleBase that can handle Geographic replication topologies. Handcrafted Solution If you want to set up Geographic Replication using MySQL Replication, you will need to make the MySQL Instance in each Data Center resilient. Here is what thinking: IDEA #1 : Local Data Redundancy In each ...


1

If I am reading your requirements correctly. From the dataset provided the start_ip_num and end_ip_num will be the same. Why you may ask? you are not storing a range of ip addresses; take for example a /24 range: 192.168.1.0/24 this provides a range 192.168.1.0 - 192.168.1.255, whilst really only ip's 192.168.1.1 though 192.168.1.254 are usable (.255 being ...


1

Ok, what I believe you are after is creating Java routines (ie, stored procedures implemented in Java and stored within DB2). You will want to follow the documentation in this section on Java routines. Pay special attention to the Creating, Building, and Deploying sections. And even though this is an older article, you may wish to check out this article ...


1

You can try setting up a shell script that runs the SQL scripts or write a program in perl/whatever language which tries to log the errors and return codes from MySQL. Otherwise it is very difficult to troubleshoot without knowing the errors. You could also add SHOW ERRORS; and SHOW WARNINGS; to your SQL scripts, but I suspect the problem is not with your ...


1

I don't think Rolando thought about what this variable does before answering. Don't set the thread_stack to 32M. It's is allocated per connection and 100 connections is going use 3.2GB. thread_stack = 192K Setting it to the fairly standard 192k should be fine.



Only top voted, non community-wiki answers of a minimum length are eligible