Hot answers tagged

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

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

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

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


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

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


3

This online compression is extra cost option. And moreover usually online compression has worse compression ratio, then offline one. It's because the 1st one work on row level while the latter one works on block level. So if you really want to spare some space in the database and your old data are mostly read-only you should use: ALTER TABLE table_name MOVE ...


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

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

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


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

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

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

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


2

if you do not have problems with your disks and the controlfile is corrupted you can do the following: start the database in nomount (startup nomount), then check the output of the command show parameter control (run the command in sqlplus). If the command shows more than one control file, you can overwrite D:\APP\FROZEN\ORADATA\ORCL\CONTROL01.CTL with ...


2

Use AUTOMATIC. RECOVER AUTOMATIC DATABASE UNTIL TIME '2015-10-20:13:54:16' USING BACKUP CONTROLFILE; CONTINUE [DEFAULT] is a different branch and can not be used together with clauses like UNTIL and USING BACKUP CONTROLFILE. Control file created. SQL> recover automatic database until change 473319 using backup controlfile; Media recovery complete. ...


2

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


2

That is not how you load a BLOB field. BFILENAME returns a BFILE locator, and BFILE data is not stored in the database, but in files outside of but accessible to the database. The above command would not even succeed if BLOBCOL is really of BLOB type, you would get the below error: ORA-00932: inconsistent datatypes: expected BLOB got FILE Another mistake ...


2

A solution using recursive CTE. It assumes that the CYCLEAGE values start from 1 and increase by 1, without gaps: with cte (CYCLEAGE, AMOUNT, RUNNING) as ( select CYCLEAGE, AMOUNT, least(0, AMOUNT) from TEMP_UNPAIDINTEREST where CACCSERNO = 1725 and CYCLEAGE = 1 union all select t.CYCLEAGE, t.AMOUNT, ...


2

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


2

For major database statistics regathers, it is best to forget the old stats and essentially regather statistics freshly on the whole system. I do not advise doing this frequently because it is a significant workload undertaking on your system and there are better methods of controlling statistics over time. This would likely require a script to go out and ...


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


2

It sounds like you just want a CREATE TABLE AS SELECT CREATE TABLE your_new_table_name AS SELECT * FROM your_materialized_view If there are indexes on your materialized view that you want to create on the table, you'd need to create those separately.


2

I would not touch that directory, I have not found any references of other users using the SQL Developer software that comes with database binaries. Additionally, SQL Developer shipped with the database binaries is kind of outdated. Even 12.1.0.2 comes with SQL Developer 3.2 I would just perform a seperate install. Also, I find it unusual that someone, who ...



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