Tag Info

New answers tagged

1

When you process a script in SQL Developer using the "Run Script" button (not the "Run Statement" button), it uses SQL*Plus internally to execute your script. Therefore using a SQL*Plus control setting which stops processing on errors should work. Try adding to the top of your script line like this: WHENEVER SQLERROR EXIT FAILURE


0

I was able to resolve this issue by copying the contents of the SQLPLUS folder into the Instant_Client_12_1 folder. Then SQLPlus was able to launch.


1

You need to restore/recover your database. If you can't then yes the code is buried in the system table spaces. But it won't be easy looking through the data file with a hex editor trying to find the code. You should either try to restore the database or just rewrite the code. Do you have a backup of your database? Do you have all of the data files, control ...


2

It sounds like you're dumping 30 million rows into an existing table, then immediately trying to update those rows. I have a couple of thoughts, which may or may not work for you (since I can't see your code currently): 1) Is there a way to combine the inserts with the updates? It seems suspect to me that you're creating 30 million rows, then immediately ...


0

The following post answers my question and we ignored the stoplist when we created the indexes. https://community.oracle.com/thread/2181803


1

The difference of oracle instant client and the oracle client installed by Oracle Universal Installer is in how they are installed. The Oracle universal installer maintains a registry which the instant client does not have or use. But the software components are the same independent of the method you use to install. If you use the jdbc oci driver then you ...


0

Assuming you mean that the database is "missing" logs from P->R, and that your primary database no longer has those logs either, then the answer is that you'll have a functional database (upon standby activation as a primary) but you will be missing data from P-> the Activation point. The thing to remember here is that your standby is only as good as your ...


1

I figured it out. Instead of having the environment variables set for the user, they had to be asserted when starting the JVM for the application, just like you have to set the LD_LIBRARY_PATH and CLASSPATH for the JDBC Driver. If everything is set when starting up the JVM, and you have a sqlnet.ora file in the directory you've set TNS_ADMIN, the file will ...


2

Got a workaround from Oracle: /*+ opt_param('_simple_view_merging', 'false') */ or alter session set "_simple_view_merging"=false; So the following query works: SELECT COUNT(patientId) n FROM ( SELECT /*+ opt_param('_simple_view_merging', 'false') */ DISTINCT pat.patientId, pat.bval1, pat.bval2 FROM pat LEFT OUTER JOIN (SELECT /*+ CARDINALITY(tab ...


1

You can't use contains because there is no Oracle Text index on the database source. You could, I suppose, write a query that copied the data from dba_source to a custom table, create an Oracle Text index on that table, and search that table using the contains function. It would generally make more sense, though, to just query dba_source with a like query ...


1

If you actually have different databases (and it sounds like you do), you can't grant permissions for users in one database to access data in another database. You'd need to create a database link in the EmployeeDB database that connects to the ContractorDB database. That database link can either use a fixed username and password in the ContractorDB ...


2

Using PIVOT: WITH combined AS ( SELECT o.customer_no, EXTRACT(month FROM o.entry_date) AS order_month, EXTRACT(year FROM o.entry_date) AS order_year, t.order_final_tot FROM orders o JOIN order_totals t ON (o.order_no = t.order_no) ) SELECT * FROM combined PIVOT ( SUM(order_final_tot) AS ...


0

If (and only if) order_no is the primary (or a unique) key of order_totals, then you could use a similar CASE expression as in your first query, to get the order values: SELECT TO_CHAR(o.entry_date, 'MM') AS month, -- count orders SUM(CASE WHEN EXTRACT(year FROM entry_date) = 2013 THEN 1 ELSE 0 ...


0

Use CASE statement as following SELECT CASE WHEN ref_id = 'RIDERS' and subcod = 'DISABILITY' THEN description ELSE NULL END AS DISABILITY, CASE WHEN ref_id = 'RIDERS' and subcod = 'DISMEMBERMENT' THEN description ELSE NULL END AS DISMEMBERMENT, CASE WHEN ref_id = 'RIDERS' and subcod = 'MEDICAL' THEN description ELSE NULL END AS MEDICAL, CASE WHEN ...


0

Got a Solution. Increase the Value of INITRANS and Rebuild the Indexes Helped. Table accessed via DB_LINK has INITRANS = 1, resulting in too much ITL Waits ~200 and Row Lock Waits ~100000. Now the INITRANS Value is increased to 20, resulting in No Row Lock wait for Distributed/Concurrent Transactions.


0

This seems to be the query from TOAD when you click the used_by tab - you just need to replace the 'OBJECT_NAME' with whatever the name of the object you are interested in is, and change the object_type to whatever is appropriate for your object. WITH Objs AS ( SELECT object_id FROM (SELECT object_id, referenced_object_id ...


0

One of Codd's principles was that a relational database should be able to look after itself using relational techniques. Following from this there has to be a table (or table-like thing) that holds the columns for a table and their relative ordering. This requires a definite data type to be declared for your columns' metadata and hence an upper limit on ...


0

mmmmmpie offers you a very viable way to do it. However, especially if you want to get your test environment as close to possible as prod for testing purposes, I highly recommend you look into RMAN cloning. You will end up with a block-for-block recreation of your production environment, which IMO is worth its weight in gold in regard to performance tuning ...


3

No, your case does not block on Oracle, nor should it. Concurrency is one the the major reasons to use an RDBMS instead of say, Excel. But just to make it interesting, if you insert the same value in both sessions, it will then block. Session #1 CREATE TABLE t1 (c1 NUMBER PRIMARY KEY); INSERT INTO t1 VALUES (1); -- do not commit transaction Session #2 ...


0

This solved the issue for me: grant execute on sys.dbms_crypto to myuser; I had a similar problem with the random function.


0

Solution found. After some research I got to a nice example on how to find the current user via dual. The solution is pretty simple just add select sys_context( 'userenv', 'current_user' ) into user_name from dual; and user user_name instead of user. The body looks as follows CREATE OR REPLACE PACKAGE BODY PKG_DONE AS PROCEDURE ACK_FROM_WEB(P_UUID ...


1

You can use one of the functions of Oracles owa_util's package named WHO_CALLED_ME. To demonstrate this feature, we can create a table which will store the results of the call to Oracle's procedure. create table T_CALLSTACK ( OWNER_NAME varchar2(30), OBJECT_NAME varchar2(30), LINE_NUMBER number, CALLER_INFO varchar2(100), ...


0

A function has to return something (in your case an INTEGER) somewhere. Try SELECT SOLICITAR_DISPONIBILIDAD(1,0,2014,0) FROM DUAL


0

Is there any extra cost associated with, for example, having to go potentially further back in the undo data to return the records? Example: You have a table getting one insert every minute. If a SERIALIZABLE transaction started at 4:00pm, and queried that table at 4:15pm, then it will have to read at least 15 undo records to return to the state of ...


2

Short answer: CREATE TABLE must be explictly granted to the object owner for DBMS_DATAPUMP to be called from within a package or stored procedure. Long Answer: In an effort to solve this question, I interrogated the data dictionary to get all of the object and system privileges granted by the IMP_FULL_DATABASE and EXP_FULL_DATABASE roles (and all roles ...


0

Privileges acquired via roles are not effective in a stored procedure. You need to grant the required privileges to the account that creates the package.


0

For recent SQL: select * from v$sql For history: select * from dba_hist_sqltext


1

Broadly, they mean that the partition(s) that need to be accessed will be determined at runtime. In the first plan, where both are KEY, some earlier step in the plan is producing one or more values for the partition key. Those partition keys are then used to determine which partitions Oracle needs to scan the index for. In the second plan, the ROWID from ...


2

This can be achieved with the expdp CONTENT flag: expdp username/password DIRECTORY=directory_object_name \ DUMPFILE=dumpfile_name \ TABLES=table_names|TABLESPACES=tablespace_names|FULL=y \ CONTENT=metadata_only


0

change to insert into t select 1, to_timestamp ('2009-02-10 12:34:45.56','YYYY-MM-DD HH24:MI:SS.FF2') from dual; should work. Don't rely on default formats.


0

Database control has been desupported in 12c. You need to use EM Express or EM Could Control


1

If you have Oracle Database Release 12c Release 1 (12.1.0.2) then you can store JSON in the database in a VARCHAR2, CLOB, or BLOB column, and then query it in SQL using JSON path expressions (analogous to XPath expressions for XML). You can use SQL functions json_value, json_query, and json_table, and SQL conditions json_exists, is json, is not json, and ...


0

search " libaio.so.1" file in directory, copy it or move to your path mentioned in error, as /u01/app/oracle/product/11.2.0/db_home/lib/ and this worked for me


0

col dt1 noprint new_value dt1 SELECT TO_CHAR (SYSDATE, 'yyyymmdd') || '.log' dt1 FROM DUAL; spool C:\log_file_&dt1 sql commands spool off


6

Sargable (or sometimes sargeable). It's not really a word, it's made up of Search ARGument, and when a WHERE clause is sargable, that mean's it's possible for it to use an index. It doesn't mean it will use the index, and it doesn't mean it will seek, either. A lot of factors go into the optimizer's choice, and the rules can clearly differ between different ...


6

Well it does now - Oracle 12c introduced IDENTITY columns, see: Identity Columns in Oracle Database 12c Release 1 (12.1) e.g. CREATE TABLE identity_test_tab ( id NUMBER GENERATED ALWAYS AS IDENTITY, description VARCHAR2(30) );


-5

SELECT Y.name, CASE WHEN X.value = 0 THEN X.value * Y.value ELSE Y.value END Value FROM (SELECT name, value FROM abc1 WHERE value=0) X, (SELECT name, exp(sum(ln(value))) AS VALUE FROM abc1 WHERE value > 0 GROUP BY name)Y ...


1

First, let me state that I think that running anything as the non-oracle user on your filesystem may result in significant database issues! (I'll explain why in a minute) Aside from the pathing issue, which you solved, I think that Colin is correct. You (or your admin) does need to give read on the binary: [root@db01 ~]# useradd captjackvane [root@db01 ~]# ...


0

Try providing executable permission for the file with the following command Go to location where you have the file. chmod 100 opatch Also you can refer the following permissions to the file 400 Read by owner 200 Write by owner 100 Execute by owner 040 Read by group 020 Write by group 010 Execute by group 004 Read by others ...


0

Try this R CMD INSTALL --configure-args='--with-oci-lib=/usr/lib/oracle/12.1/client64/lib --with-oci-inc=/usr/include/oracle/12.1/client64' ROracle_1.1-11.tar.gz


1

In Oracle you can have your own aggregation function. To write one, we need to implement an interface which looks like this: create /*or replace*/ type mult_agg_type as object ( totalAggValue number, static function ODCIAggregateInitialize(sctx IN OUT mult_agg_type) return number, member function ODCIAggregateIterate(self IN OUT ...


1

Based on the documentation: An image copy is an exact copy of a single data file, archived redo log file, or control file. Image copies are not stored in an RMAN-specific format. They are identical to the results of copying a file with operating system commands. RMAN can use image copies during RMAN restore and recover operations, and you can ...


1

You can specify your queries per table with the QUERY parameter with Data Pump export. For example: QUERY=employees:"WHERE department_id > 10 AND salary > 10000" With the above, only those rows are exported from the employees table, that have department_id > 10 AND salary > 10000. More information in the official documentation: ...


3

The syntax for the alter tablespace statement only supports one tablespace at a time, you can't list more than one as you're trying to. If you're scripting that command already, simply have your script issue multiple alter tablespace statements rather than one. If you're generating your list of tablespaces from dba_tablespaces, you can dynamically alter ...


3

Regular trigger will see changes made in other tables within the same transaction. However, if you create the trigger (or other programmable object) with PRAGMA AUTONOMOUS_TRANSACTION, it will be executed within the scope of new transaction and won't be able to see "parent" transaction changes.


0

On Oct 24, 2011, I wrote some Stored Functions to perform Ancestor and Descendant traversals (Find highest level of a hierarchical field: with vs without CTEs ). From my post, you would use GetAncestry. From your data, the code in GetAncestry will traverse until it sees -1 as a parent id. No changes in the algorithm are needed except to change parent_id to ...


0

The only sensible answer to this kind of requirement is Goldengate. Goldengate can do two-way sync, but it costs $$, and you need to be careful in your design to try and avoid conflicts (e.g. updating the same row on both DBs). My first question would, in reality, be to ask if is this actually necessary. It's complex and error-prone. There's usually a ...


0

For this you need a logical standby database, instead of a physical one. This is a little more complex. Not all data types are supported and when data is updated on both sides on the same time, there could be some problems ahead.


1

All users have a profile assigned. You can alter the user to have the default profile by: ALTER USER username PROFILE DEFAULT;


4

You could use a row-level security policy with a statement_type limited to update (or more likely update and delete, and maybe insert too). See the DMBS_RLS.ADD_POLICY docs for details. Dummy scenario: a list of tasks, only task owners can modify their task. create table owners(owner_id int primary key , owner_name varchar2(10)); create ...



Top 50 recent answers are included