Hot answers tagged

47

The query is syntactically correct SQL even if table_b does not have a name column. The reason is scope resolution. When the query is parsed, it is first checked whether table_b has a name column. Since it doesn't, then table_a is checked. It would throw an error only if neither of the tables had a name column. Finally the query is executed as: select a....


5

Because Oracle performs a correlated subquery when a nested subquery references a column from a table referred to a parent statement one level above the subquery. http://docs.oracle.com/cd/E11882_01/server.112/e41084/queries007.htm#SQLRF52357 It means in order to determine whether subquery is correlated Oracle must try to resolve names in ...


4

path to data files is included in full dump. No, SIDs don't have to match You may want to add REMAP_DATAFILE option (https://docs.oracle.com/database/121/SUTIL/GUID-7C9DDBED-91AC-4FA2-AB91-1FF214DA89B0.htm#SUTIL926 ) to impdp . Alternatively, you can just pre-create all tablespaces on target database.


4

The answer is right there, in the data. (I assume that the time part is 00:00:00 in all of them): The order by has ties. There is not one "first row" but 3 of them. So, when the 2nd query restricts the result to one row, one of the 3 is - arbitrarily - chosen. It may not be the same row chosen the next time you run the same query!


3

I have seen this only once so far, but there is an issue described in the My Oracle Suppport note: FIPS use causes ORA-01017: Invalid Username/password with Managed ODP.NET (Doc ID 1937500.1) I will not post the full content of this as it is not public, but here is the essence: This bug has been fixed in 12.1.0.2 Managed Provider. Please note that the ...


3

You can try as below: SELECT name, LISTAGG(item_name, ' ') WITHIN GROUP (ORDER BY item_name) AS item_name FROM item GROUP BY name;


3

Introduction to the Oracle Database Instance A database instance is a set of memory structures that manage database files. A database is a set of physical files on disk created by the CREATE DATABASE statement. The instance manages its associated data and serves the users of the database. Every running Oracle database is associated with at ...


3

WHERE PARENT_ID IN my_array; This will not work. First, as the error message states, you are not allowed to use local collection types in SQL statements, you need to define them in the database. Second, that syntax does not exist. So first you define the type: CREATE TYPE arr_type is TABLE of VARCHAR2(11 BYTE); And after that, you can use the ...


3

In your case you could not use transportable tablespace because the source database have to be Enterprise edition. In general, I would prefer upgrade using rman. But in your case it is not so easy because direct upgrade from 10.2.0.4 to 12.1.0.2 is not supported. You would have to upgrade to intermediate release (e.g. 10.2.0.5) that can be directly upgraded ...


3

There is no name field in table_b so Oracle takes the one from table_a. I tried the EXPLAIN PLAN but this gave me only that there is a TABLE ACCESS FULL. I presume that this will generate some kind of Cartesian Product between both tables that result in a list of all the names in table_a is returned by the sub-query.


2

If it was me, I would partition by hour, then you can just drop 24 partitions every day. That is only 744 partitions per month. Then if you query within a relatively short window, you will look at much less data. You may also want to look at your index strategy. My guess is that the cost of maintaining the indexes is too high. IMHO, smaller partitions should ...


2

select id, name, customer_code, min(column1), min(column2) from the_Table group by id, name, customer_code order by id; Note that if there are more rows with non-null columns this would still only return one per combination of id, name and customer_code (but it does return the result in your question) Edit (after the question was changed) To get all ...


2

db_block_size is the default block size of the database, but it does not necessarily mean all your tablespaces have the same block size, you can have tablespaces with different block sizes. You need to define seperate cache areas for tablespaces with non-default block size. For example, if your db_block_size is 8192, and you want to create a tablespace with ...


2

It looks like your ACCEPT prompt includes quotation marks. But quotation marks are also used to delimit the prompt string, so if you want to include those characters into the prompt, you need to double them: … prompt 'Fecha de inicio (''DD/MM/YYYY-HH24:MI:SS''): ' … prompt 'Fecha de fin (''DD/MM/YYYY-HH24:MI:SS''):' Although I think you could simply ...


2

It should be a simple boolean. This should work and be more efficient. The SQL%ROWCOUNT attribute will be populated with the number of rows deleted. If you didn't delete any then just exit. FOR i IN 1..100 LOOP DELETE FROM mytable WHERE date <= (SYSDATE - 30) AND ROWNUM < 5000; EXIT WHEN sql%rowcount = 0; COMMIT; END LOOP; If you aren'...


1

Checking Allow inprocess for the OraOLEDB.Oracle provider seems to have resolved the problem.


1

Yes, databases can be created without using DBCA from command line. 1) you need to export required environment variables 2) Create a parameter file (init$sid.ora) under $ORACLE_HOME/dbs substitute $sid with your instance name. 3) Create a SQL script for database creation 4) Connect to sqlplus, put the instance in no mount state and run the script to ...


1

Callbacks are totally against the idea behind SQL. It's designed as declarative language which queries describe the task and query optimizer builds composition of sorts, index seeks, merges, etc to meet the query. No any row-by-row processing by callbacks. Oracle supports overloading, with restrictions http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/...


1

You use fund_acct_reg_view.fund_acct_no fund_acct, in SELECT but use pr_line_item.fund_acct_no in GROUP BY. Use fund_acct_no from the same table in both cases.


1

One possible reason: Bug 18162779 : ORA-01428: ARGUMENT '000000000000000000' IS OUT OF RANGE Try running the query after setting the below: alter session set "_disable_function_based_index" = TRUE;


1

$ alias sqs='sqlplus / as sysdba' $ sqs SQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 15 20:33:31 2016 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics and Real Application ...


1

TO_DATE functions accepts the NLS language as an optional parameter .. have you tried pulling it in via SQL*Loader as a CHAR, then do a TO_DATE conversion on it inline? so your SQL*loader syntax might look like: ... my_date CHAR "TO_DATE(:my_date, 'dd-MON-RR', 'NLS_DATE_LANGUAGE=American')" ... See if that works any better ??


1

Just add a WHERE condition. And if you are only displaying one value, RUNNING, then there is no reason for a CASE. Otherwise you will want to evaluate each condition in the CASE including what should display in the event none of the conditions is met; a default value. So here is the code to your original question: SELECT 'RUNNING' AS A from message_log ...


1

On Windows platform you can find oracle_home path in the registry. Run regedit, then navigate to: hkey local machine/software/oracle Under oracle folder in the registry you will find something like: KEY_OraDb10g_home1 There you can see oracle_home variable.


1

Many ways, but as you suggested, you can use DUAL: CREATE PROCEDURE NODATAFOUND AS nowt VARCHAR(10); BEGIN SELECT * INTO nowt FROM DUAL WHERE 1=0; END; / It'll produce a ORA-01403: no data found when executed.


1

I do have privileges on the table accessed by SQL as the query runs fine. And that is a false assumption. Just because you can query a view, it doesn't necessarily mean you have privileges on the underlying tables. Anyone can query the view ALL_TABLES, but a regular user doesn't have the privileges to query the underlying tables such as OBJ$ or TAB$. ...


1

If you are connecting to an Oracle instance 11.2+, you can fetch data from the column temp_space_allocated of the view v$active_session_history, which shows the amount of TEMP memory (in bytes) consumed by your session at the time the sample was taken. Otherwise, you may look at the V$TEMPSEG_USAGE view, column blocks, and convert it to megabytes depending ...


1

If you want to query the OEM Repository, you can try something like this: SELECT TARGET_NAME, TARGET_TYPE, TARGET_GUID, METRIC_GUID, METRIC_LABEL AS METRIC_GROUP, COLUMN_LABEL AS METRIC_NAME, TO_CHAR(ROLLUP_TIMESTAMP, 'MM/DD/YYYY HH24:MI:SS') AS ROLLUP_TIMESTAMP, AVERAGE, MINIMUM, ...


1

This query runs on 11.1.0.7 Enterprise and provides similar results to the the OEM Grid performance page. Both require the diagnostics package. SELECT TO_CHAR(SAMPLE_TIME, 'HH24:MI ') AS SAMPLE_TIME, ROUND(OTHER / 60, 3) AS OTHER, ROUND(CLUST / 60, 3) AS CLUST, ROUND(QUEUEING / 60, 3) AS QUEUEING, ROUND(NETWORK / 60, 3) AS ...


1

I confirm that a full or incremental backup during business hours can make your database inaccessible to end users and web services. Factors that can make things worse: allocating too many channels to RMAN using an out of support version like Oracle 10 having a database that is CPU or memory or network IO bound running Dataguard at the same time ...



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