Union All Performance Sometimes queries with UNION ALL are slower than executing multiple non-overlapping queries and concatenating the results in code. In Clause Performance Sometimes IN clauses can be very slow. If you can replace them by JOIN the query might run a lot faster
You can create an index which will be synchronized automatically everytime you commit: Create index … on … INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS (‘SYNC ( ON COMMIT)’) or just periodically (p. x. every hour): Create index … on … INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ‘SYNC (EVERY “SYSDATE+1/24”)’) See Topic on StackOverflow
Usually the file is located under: C:\oracle\tns_admin\tnsnames.ora You can add an entry like this: <myapp> = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<ipaddress>)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(service_name=<myapp_servicename>))) If everything is ok you can try to test the connection Issuing a ping tnsping myapp or login as user sqlplus myuser@myapp or login as sysdba sqlplus sys@myapp as sysdba
Exporting the table using SQLWorkbench Imagine you have a table images with a character column filename and a clob column image_data First you need to install the SQL-Workbench. Install does just mean to extract the archive in that case. The following command exports the table into a textfile. It does also generate the loader file […]
General pitfalls Every statements should be ended with ; Create Procedure statements shall be ended with a ; and a / on the next line like: create or replace PROCEDURE ExampleProc IS BEGIN NULL; END; / Identifiers shall have a maximum of 30 characters, otherwise the code using them fails Output text from within a […]
First you need to activate it and call the script: set serveroutput on format wrapped; call SOME_SCRIPT(150); In the script you can output something with that line: dbms_output.put_line(‘Start’); After the script has been executed you can see the output for example in the Script Output tab of SQLDeveloper.