Tag Info

Hot answers tagged

4

Is it still outer join? Or is it becomes inner join? It is still an outer join: SQL Fiddle Oracle 11g R2 Schema Setup: create table tab_a(num integer); create table tab_b(num integer); insert into tab_a(num) values(1); insert into tab_a(num) values(2); insert into tab_b(num) values(2); insert into tab_b(num) values(3); Query 1: select * from ...


4

If I understood you correctly, you have two sorts of entities to manage: Players (human beings that participate in your game) Characters (fictional entities that the players use/manipulate in your game) Once you look at your problem like that, you could model it easily like this: players table, with a player_id as PK and information about your real ...


4

There are a number of things that can cause the actual plan to differ from the estimated plan (and if you want to get really deep in the weeds, there are lots of things that can cause different methods of producing the actual plan to produce different results but I'll ignore that). The simplest (and most common) revolve around bind variables. If I do an ...


4

Assuming that your database character set is AL32UTF8 (if there is a difference between the two, this is almost certainly your character set) and that all the data is characters that exist in the US7ASCII character set ("english" might constitute more than that depending on the definitions we're using) there won't be any noticable performance differences ...


4

You can generate a customized index table, for example, that has a row for all year&week pairs that the date range (a certain vacation etc) encompasses. Then you can join dateranges by going through that index table. It will be large, but does avoid large scans as you can just list all vacations that have any weeks that are in common with another ...


3

If you do not have access to create Directory, then you can use SQL Loader to get data into the database (provided you have Oracle client installed on your system). An outline of the steps is provided below: a.) Create a table with the same structure as your excel file b.) Convert your Excel File to .csv format file c.) Create a Loader Control File. Refer ...


3

I know that I should test my SQL script as well, but I don't have time to make a test before leaving for work! I can make a SQL Fiddle later, but for now it seems that no one has quite answered the question correctly. SELECT COALESCE(t1.cod, t2.cod) cod, t1.text1, t2.text2 FROM t1 FULL OUTER JOIN t2 ON t1.cod = t2.cod ORDER BY cod; If you want to test ...


3

select subscriber, date_column, time_column, value from ( select subscriber, date_column, time_column, value, rank() over (partition by subscriber order by value desc, time_column) as r from mytab ) where r = 1 and subscriber = 'ccc';


3

Yes, by using database links. For example, in db2: create database link db1_link connect to myuser identified by mypassword using 'db1'; create view myview as select * from mytable@db1link; select * from myview; Where using 'db1' refers to a TNS entry, but it could be replaced with the actual TNS descriptor.


3

Choose Install database software only, then later you can upgrade your databases one by one with DBUA (Database Upgrade Assistant) from the new Oracle Home, or by performing a manual upgrade.


3

If the USING INDEX clause is omitted, the index will be still created automatically with the same name as the constraint, in the same tablespace where table is created. This will not cause an error. http://docs.oracle.com/database/121/SQLRF/clauses002.htm#SQLRF52225 If you specify schema.index, then Oracle attempts to enforce the constraint using ...


3

Autotrace in SQL Developer gets the plan from v$sql_plan, and also gets the stats from your session, does a delta of session stats before and after running the query. Explain Plan asks the database what it THINKS the plan will be for your query. Your co-worker is right, they can differ wildly, and you're better served to use AutoTrace or our new feature in ...


3

Assuming there are no LOB fields or other specialties: select sum(blocks) as blocks, count(*) as extents from dba_extents where owner = '&table_owner' and segment_name = '&table_name';


3

In the simplest case, when you restore a database, you tell RMAN to recover it to the most recent point in time by applying all the redo (archived and online) that is available. That will re-apply any statements that were executed during normal database operations including things like dropping tables and deleting data. If you want to restore a database to ...


3

This is in fact a bug: Materialized View Log (Mlog$) Index Not Created in its Default Tablespace (Doc ID 1959658.1). Excerpt: The index created when a materialized view log is created appears to be created in the user's default tablespace rather than the same tablespace as the MV log table. Additionally, if the user has no quota on their default ...


3

http://www.oracle.com/technetwork/database/features/oci/ic-faq-094177.html#A8434 Can LDAP be used with Instant Client? Yes, LDAP can be used with Instant Client, as of 10.1.0.4.


2

define/& is a simple text substitution mechanism that does not know anything about sql. And sql and the Oracle Database server do not know anything about the text processing mechanism. So the text that results from the 'define/&'substitutuin must be valid sql statement. Otherwise you get an error. CREATE TABLE P747||_MU as Select percent from ...


2

Not possible, redo logs get transferred unprocessed. If you want to transfer changes related to just some specific objects or schemas, Streams (deprecated, but free) or GoldenGate (requires extra license) does that. Another option for reducing network traffic is to configure redo transport compression (requires the Advanced Compression option), but this ...


2

select t1.cod, t1.text1, t2.text2 from t1 full outer join t2 on t1.cod=t2.cod


2

You can simply group by IPDL: SELECT RAP.IDPL, MAX(C41.TIP_COD_DESEU) AS CD41, MAX(C41.STOC_VAL) AS CANT41, MAX(C42.TIP_COD_DESEU) AS CD42, MAX(C42.STOC_VAL) AS CANT42, MAX(C43.TIP_COD_DESEU) AS CD43, MAX(C43.STOC_VAL) AS CANT43 FROM CHEST_UL CHT INNER JOIN UL_PUNCT_LUCRU rap ON cht.fk_id_raportor = rap.id FULL OUTER JOIN ...


2

Oracle 11g R2 Database Release 2 only support Windows, Linux, and Solaris. So if you want to using Oracle 11g R2 on a Mac OS you will need to using something like Virtual Box or VMware Fusion. You can read more about installing Oracle 11g R2 on these operating system here.


2

"VMware is supported by Oracle but not certified." Oracle does not certify VMware but it does not certify any hardware, too. "how will Oracle know that I'm on VMware": I don't know if Oracle support can find out that you are running on VMware if they analyze the output of their RS (remote diagnostic agent). In many situations it is not relevant on which ...


2

Looks like my short answer was not to everyone's taste, so let me show you the long answer First of all, let's create our lab. @WestFarmer mentioned a couple of tables SQL>create table A ( 2 some_column number, 3 another_column number 4 ); Table created. SQL>create table B ( 2 id number 3 ); Table created. Also a mentoned ...


2

SQL Developer the client application is free of charge. Of course, the Oracle database would need to be licensed in a way that allowed you to connect with any tool. PL/SQL Developer is a completely different tool produced by a different company that you would need to license. Define "secure". Assuming a default database install, using any tool to connect ...


2

You should use SYSTIMESTAMP instead of SYSDATE, in order to get fractional seconds (which you desire as I assume) SYSTIMESTAMP returns data type TIMESTAMP WITH TIME ZONE, thus you don't have to specify explicitly. So it is SELECT TO_CHAR(SYSTIMESTAMP AT ZIME ZONE 'UTC', 'yyyy-mm-dd"T"hh24:mi:ss.ff3"Z"') from dual


2

The Instant Client is easier to install and more lightweight. It doesn't even need to be installed, you can just unzip it and use it. You don't need an Oracle Home either. On the other hand, it does not support deprecated features like Oracle Names. If you don't need any feature the instant client omits, I suggest you use it. It looks like you are covered ...


2

Overlapping date ranges can be found thus WHERE FromDate <= QToDate AND ToDate >= QFromDate You will get one row returned for each partially or fully overlapping range. For example, if you had separate pay rates for morning, afternoon and evening shifts and someone worked all three, there would be three rows returned. The same basic pattern ...


2

Solved this with help from http://stackoverflow.com/questions/3664903/how-can-i-return-multiple-identical-rows-based-on-a-quantity-field-in-the-row-it The resulting answer with the original table looks like this: with qtys as ( select 3 as qty from dual union all select 10 as qty from dual union all select 9 as qty from dual union all select 7 as ...


1

As Oracle support note "Client / Server Interoperability Support Matrix for Different Oracle Versions" ( Doc ID 207303.1 ), Oracle Client 12.1 does not support connections with Oracle DB servers 10.1 and below. Connections with Oracle DB 10.2 are still supported, but not recommended. The recommended option its to use Oracle 10.2 client.


1

You can check some options like checking FAL_SERVER parameter, LOG_ARCHIVE_DEST_n parameters, Dataguard Broker, Recovery Catalog, Cloud Control, etc.. if they are used, but none of them are mandatory for a DataGuard configuration. In fact, the standby nodes doesn't need to send any kind of information to the Primary db at all. Since the heartbeat is ...



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