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

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

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

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

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

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

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


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)


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;


2

I don't think that tkprof is the problem. It is a tool that summarizes information from trace file. Tracing is the problem. If you enable tracing of a process, this may slow down the process considerable. Besides that the trace files may fill up your disks. Finally you should be aware that security relevant information is written to trace files.


2

The session(s) using the temporary table can be queried as: select sid from v$lock where type = 'TO' and id1 = (select object_id from dba_objects where object_name = '&temp_table_name'); Then you can kill these sessions or have them release the locks gracefully.


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

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


2

You won't need downtime just to add additional redo logs to your redo groups. Take a look at this: Oracle Configuration Best Practices And then look at this: Creating Redo Log Groups and Members You will need downtime to add additional control files: Creating Additional Copies, Renaming, and Relocating Control Files


2

I've struggled the same problem and I think the problem comes from gnome failing to pop up the confirmation dialog. That's why there is nothing in the error logs. So what I did is changing the window manager from gnome to xfce. That solved the problem. It would probably work with another manager or there may be a fix for gnome but here is how to install xfce:...


2

The method you describe is ok. This method was the backup method used before Oracle introduced RMAN and is still valid. It is still used by tools that do snapshot backups (like Netapp's backup tool for Oracle) and I often use this in similar situations. You also should make a controlfile backup if all the controlfiles are on these disks.



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