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

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

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

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

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

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 ...


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; ...


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

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

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

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

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 ...


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

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


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

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

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

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

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

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

You can calculate a RANK/ROW_NUMBER based on this logic: ROW_NUMBER() OVER (PARTITION BY IMMS_ID ORDER BY CASE WHEN status='DI' THEN 1 WHEN status='OI' THEN 2 WHEN status='FI' THEN 3 WHEN status='OV' THEN 4 WHEN status='FV' THEN 5 END)


2

If you change file's permissions during copy operation, tar will throw "file changes as we read it".


2

version <= 11.2? No.* But in 12c, there is a new feature called Asynchronous Global Index Maintenance. The DROP PARTITION operation completes immediately, leaving the global index in usable state. The trick is, that index entries are not maintained, they are orphaned, and these orphaned entries will be cleaned up later. The cleanup process happens ...


2

This version of Oracle XE is based on 11g2 edition, which is not compatible with CentOS 7. In fact, it isn't even 100% compatible with CentOS 6, however it will run there without problems, after you install a number of RPM packages not installed/required by Oracle installer (especially libaio both 32/64-bit). These are formal OS requirements for XE 11g2: ...


2

You do realize the standby is a "physical" copy of the primary (read-only in this case)? If you change the password in the primary, the changes will be propagated to the standby also. You can not change such things in a physical standby independently.



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