Hot answers tagged

10

I am not proud of publicly writing something like this. Sample + data: CREATE TABLE donaldduck ( SURNAME VARCHAR(100), TOWN VARCHAR(100) ); CREATE TABLE daisyduck ( SURNAME VARCHAR(100), TOWN VARCHAR(100) ); CREATE TABLE goofy ( something number(1), SURNAME VARCHAR(100), TOWN VARCHAR(100) ); INSERT INTO DONALDDUCK ( SURNAME, TOWN ) VALUES ( ...


8

I think people were having trouble understanding your question due to the table structure which is so bad it seems designed to give you a headache. As Balazs Papp indicates very little can be implemented that will scale or not look like something hacked together. However there are solutions that can be done in PL/SQL. A pipelined table function will end ...


7

Have you seen this? https://community.oracle.com/thread/889338?start=0&tstart=0 It states you can only exchange partitions from a partitioned table to a non-partitioned table, or vice-versa. You'd need to temporarily exchange the partition into an interim, non-partitioned, table, then into the target partitioned table. There are some interesting ...


4

This answer is wrong, because the value for rownum is assigned before the order by is executed. Below is the correct one, SELECT start_date, end_date, resort_id, ROWNUM FROM (SELECT start_date, end_date, resort_id FROM employee_activity WHERE employee_id = 27 ...


4

The following works but there's probably some more clever version. Explanation of the query's logic: We first find how many "resets" have been done up to and including the current row by counting the not nulls of the reset_val column, so we can separate the rows into subgroups. We also use another window function LAST_VALUE() with IGNORE NULLS, so we can ...


4

As an academic exercise, I've implemented a solution in Postgres. Now, I know this was a question about Oracle, but it was also framed as a SQL question! :) If someone is more familiar with Oracle's recursive query syntax, maybe they can find the necessary changes to make this run in Oracle. Recursive Querying (in Postgres) In this solution, I make use ...


4

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.


4

According to the My Oracle Support, ORA-01792 is caused due to an unpublished bug. Oracle suggest to alter the query and/or view definitions to avoid the error. However in cases where the SQL cannot be adjusted then the checking can be disabled by: SQL> alter system set "_fix_control"='17376322:OFF'; Alternatively an interim patch for this fix (patch ...


4

Based on your description of this behaviour and your other comment, I guess your database runs on Windows. The command you used should suffice and delete the datafiles. Except on Windows. On Windows, the datafiles are not always deleted, it is a known limitation. Before dropping tablespaces or datafiles, take them offline: alter tablespace ... offline; ...


4

If you're using Linux/Unix, there's a way of making SQL*Plus loads friendlier on the command line - use rlwrap. It adds readline support to SQLPlus, allowing you to use the cursor keys to navigate to previous queries (it retains a history), and edit them when you've made a mistake. Launch SQL*Plus with rlwrap -i sqlplus or add alias sqlplus="rlwrap -i ...


3

Oracle will automatically delete obsolete files. In general, you should not have to manually delete anything. An important part of RMAN maintenance is deleting backups that are no longer needed. If you configure a fast recovery area, then the database automatically deletes unneeded files in this area automatically From Overview of RMAN Backup and ...


3

Although this is an old post with an accepted answer, I think the LAG() analytic function works well in this case and is noteworthy: LAG() removes duplicate values in column num2 with minimal expense No need for non-trivial regular expression to filter results Just one full table scan (cost=4 on simple example table) Here is the proposed code: with nums ...


3

You should be able to resolve this by copying CONTROL01.CTL over CONTROL02.CTL since the first one has a higher version number. Make a copy of CONTROL02.CTL in case it does not work


3

The only way to get around this was to use credentials in the database links definition. Not nice if you ask me but it is what I found. This was in 10g and 11, did not test with 12c.


3

Ok, that error (ORA-00600 [6856]) is related to corruption. Normally a DBA would go to My Oracle Support, follow the notes (for example ORA-600 [6006] ORA-600 [6856] During Startup Instance, Followed by Termination by SMON (Doc ID 549000.1)), identify the affected objects then try to repair them, recover corruption, or restore a valid backup. If you ...


3

This example code shows how to parse a ref_cursor. There are additional columns available, see the definition of SYS.DBMS_SQL.desc_tab2 for a full list of available columns. In this example, my ref_cursor is defined as select * from user_tables. You would of course substitute in your own ref_cursor. DECLARE l_csrid INTEGER; l_coldesc ...


3

Since you've done an open resetlogs, you've changed the incarnation and are now trying to restore to a previous incarnation. From the Backup and Recovery User's Guide: The procedure for DBPITR within the current incarnation is different from DBPITR to an SCN in a noncurrent incarnation. In the latter case, you must explicitly execute the RESET ...


3

I always believe that the output given by oerr utility helps alot to resolve the problem. Oerr: Oerr is an Oracle utility that extracts error messages with suggested actions from the standard Oracle message files. ORA-01190: control file or data file string is from before the last RESETLOGS Cause: Attempting to use a data file when the log ...


3

I went ahead and opened an Oracle SR, and this is what Oracle had to say: 1) Do the batch commits span multiple connections? No. Oracle will write data from Buffer to redologs once sufficient redo is generated by the session. Since your session is closed Oracle will write it immediately. 2) Is there anyway to find out how often the batched commits ...


3

Why bother with a RAM disk, when you can just increase SGA, increase buffer cache, define KEEP pool, use supported, built-in tools? Why lie to the database, and make it think it uses disk and perform all the related extra unnecessary operations, while it uses memory indirectly ineffectively? Why risk database corruption and losing the data by using memory ...


3

After a pretty good amount of digging, I found this blog post on doing this for date ranges, and it turns out to work equally well on numeric ranges, with a little modification for handling the 0 and 1 endpoints. Taking the query from there and renaming the columns to be a bit more clear, we have SELECT * FROM (SELECT MAX(T_END) OVER (ORDER BY T_START) ...


3

It seems that granting the privilege references allows the user to see the column names: grant references on the_table to app_user; The grant needs to be done for the base table not for the view.


3

Yes, it is possible. https://docs.oracle.com/database/121/NTDBI/intro.htm#NTDBI2648 Oracle Database supports multiple Oracle homes. You can install this release or previous releases of the software more than once on the same system, in different Oracle home directories. This allows flexibility in deployment and maintenance of the database ...


3

There is a built-in procedure for that. set serveroutput on declare feature_boolean number; aux_count number; feature_info clob; begin dbms_feature_data_guard(feature_boolean, aux_count, feature_info); dbms_output.put_line(feature_boolean); dbms_output.put_line(feature_info); end; / Sample output when Data Guard is not used: 0 Data Guard ...


3

This is really a Unix/Linux question, but it does relate to Oracle, so I'll give an answer. In Unix, the system makes great use of what it calls "spawning" (or "giving birth"). When you run a command, a new process is created which takes all of its information from the parent. Essentially, it's a copy of the parent - it's a new bash shell, which then sets ...


3

% is not a valid format specifier for numbers. See the documentation below: Number Format Models


2

Use the /*+ NO_XML_QUERY_REWRITE */ hint. My environment for the below is 11.2.0.3.0 on Linux_x86-64 platform, Enterprise Edition. create table xml_table (c1 xmltype) pctfree 99; (PCTFREE 99, just a simple trick to spread the table over many blocks, thus making full table scan even more costly and inefficient. 1 row/block in this case.) insert into ...


2

Your import did not complete successfully: Job "SYSTEM"."SYS_IMPORT_FULL_01" stopped due to fatal error at 18:05:56 The datapump job most likely failed before it could import the package bodies. You can check the alert log to see if there's a more detailed error there. I would also recommend installing the latest patch set. 11.2.0.1 is pretty old, and it ...


2

Some notes first: Considering that Accounts has a unique constraint on (account_type, account_store), a Payment seem to be associated with only one Account. That suggests that the FK from Payments should reference Accounts (and not Stores). This change will also solve issue #2. Adding account_type to Payments (and modifying accordingly the foreign key to ...


2

Export the PATH variable. You have missed that. PATH=$ORACLE_HOME/bin:$PATH export PATH



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