New answers tagged oracle
1
vote
Accepted
Oracle Batch Deletion
With 1.8 billion rows you really should have EE. By far the best method is to recreate the table without the rows you want to delete. Formatting new blocks, especially direct path, is much faster than ...
- 510
0
votes
How do I launch sql script on every pdb using catcon with a SPECIFIC USER?
I can't speak to catcon.pl, but another option if you are logged in locally and can connect internally is to script it yourself by looping in shell through all PDBs that have the user you want and ...
- 510
0
votes
Oracle Batch Deletion
CTAS with NOLOGGING is a very good option for deleting ~15% of a big table.
Export + import with QUERY parameter is another, a very similar concept.
Both methods require additional storage space and ...
- 40.4k
0
votes
Oracle Redaction and Definer's Rights Stored Procedures
The error ORA-28081 is removed with the grant Exempt Redaction.
In this case
GRANT EXEMPT REDACTION POLICY to Data_base_owner_user;
0
votes
Accepted
Oracle multiple PIVOT clauses
Same query? yes.
Same pivot? no.
note The table alias goes after the pivot
(You'll also need to remove the duplicate cat column)
with data as (
select '1' id, 'a' cat, '...
- 4,819
1
vote
Accepted
How to configure the location of the Oracle password file?
No. You can use symlinks, but that is not a feature of the database. And of course the passwordfile can be changed or moved while the database is online.
An instance started with sqlplus or rman uses ...
- 40.4k
4
votes
How to locate a procedure in the body of a trigger
There's no need to search the text. You can leverage Oracle's dependency registration instead:
SELECT *
FROM dba_dependencies
WHERE type = 'TRIGGER'
AND referenced_name NOT IN ('DBMS_STANDARD','...
- 510
0
votes
Connection rejected based on ACL filtering, but the ACL is disabled?
Please ensure you have set up your data source connection properly:
https://www.jetbrains.com/help/datagrip/how-to-connect-to-oracle-with-oci.html#step_1_preparing_drivers_for_the_oci_connection
If ...
0
votes
How to configure the location of the Oracle password file?
Even if this were possible, why would you want to do so?
With Oracle Database, you'll have [more than] enough to worry about without mucking about with system defaults that are, probably, best left ...
- 8,259
3
votes
How to find out when Oracle table statistics became stale?
If you look at the view definition, you'll see that staleness isn't a column value stored in a table anywhere, but a formula calculated at query time:
when ((m.inserts + m.deletes + m.updates) >
...
- 510
0
votes
Oracle colsep is adding newlines. Why?
select TO_CHAR(je.key_date, 'YYYY-MM-DD HH24:MI:SS'),
je.message, --> a lot of spaces between this ...
je.id --> ... and ...
- 1,011
0
votes
How to format sql-plus-spool-file to *.csv with all columns in one line and row-content with linebreaks as one field?
I tried the following and it worked for me, try it..
SELECT '"' || REPLACE(MEDIENNR, CHR(10), '<<LINEBREAK>>') || '"',
'"' || REPLACE(ISBN, CHR(10), '<<...
- 139
-1
votes
How to format sql-plus-spool-file to *.csv with all columns in one line and row-content with linebreaks as one field?
You can use the following. (it worked for me)
set markup csv on
You can use SQLPLUS -MARKUP "CSV ON" or SET MARKUP CSV ON to produce reports in CSV format.
Reference: Generating Reports ...
- 1
0
votes
TNS-00534: Failed to grant connection ownership to child
Possible reasons could be:
There aren't enough processes to handle the request.
The accounts running Oracle DB and listener services are not the same.
Or, in my case, the server's hostname is over 15 ...
1
vote
How to get all rows which created or updated for a specified day
It is about invalid scope.
data source - in your example - is a subquery
where clause references ora_rowscn which is supposed to be contained in column list returned by that subquery
but, there's no ...
- 1,011
2
votes
Accepted
For a tablespace what does the value 3 mean in the online column?
It means the tablespace was dropped:
SQL> create tablespace ts1 datafile '/tmp/ts1.dbf' size 10M;
Tablespace created.
SQL> select online$ from ts$ where name = 'TS1';
ONLINE$
----------
...
- 40.4k
1
vote
Accepted
RMAN backup restoration
Try using these init parameter file settings in the destination:
*.DB_FILE_NAME_CONVERT = "/u01/app/oracle/oradata/db1/datafile","/u01/app/oracle/oradata/db2/datafile"
*....
- 196
1
vote
oracle 19c rac cluster nodes show in different time zones
Check the actual value of TZ variable of database processes on both nodes, example:
$ ps -ef | grep [o]ra_smon
oracle 1296361 1 0 20:46 ? 00:00:00 ora_smon_KIRA1
$ sudo strings /proc/...
- 40.4k
1
vote
Having trouble writing a query to pull 1st record from a table
There are a number of issues with your SQL statement:
You're applying your aggregate function to the 'employeeid' column, but it's the 'enteredondtm' column that you're trying to find the lowest ...
- 234
0
votes
Export pump all schemas except Sytem related schemas
Instead of excluding the system-related schemas on the export, and assuming your new instance only contains system-related schemas before the import, you could just exclude them on import:
impdp ...
- 137
0
votes
Is there any Oracle processes that exists only when database is in open mode?
The instance's set of processes all start on instance start (before mount and open). There may be something non-critical that starts after open, but I wouldn't rely on it.
Assuming you have a crontab ...
- 510
0
votes
How to bypass ORA-01033 ORACLE initialization or shutdown in progress for a regular user?
Why trigger an alert only if the database is only in MOUNT stage?
Don't you want to trigger an alert when the database is shut down or is in NOMOUNT stage? Receiving such an error while attempting to ...
- 40.4k
0
votes
How to see list of databases in Oracle?
These commands worked for me:
show con_name
show pdbs
I recently installed Oracle Express Edition (21c).
- 101
0
votes
can't execute opatch even I'm in the oinstall group with -rwx--x---
This issue can be resolved by setting the path environment variable.
Before setting the environment:
[oracle@oracle 35643107]$ opatch version
bash: opatch: command not found
Run thew below to set the ...
1
vote
SQL breaking my query where there is NULL value
Since all three columns of country are defined as NOT NULL, you have to provide a value for all three columns when you insert a new row. To load that table using distinct values from airportdemodata, ...
- 510
3
votes
SQL breaking my query where there is NULL value
Error was thrown because of trying to insert NULL values in the COUNTRY column, not COUNTRY_CODE. You did not specify any value for COUNTRY column, an INSERT like that would insert NULL values in ...
- 40.4k
0
votes
Accepted
Expecting "," or ")", found "nvarchar2"
NVARCHAR is not a valid SQL Loader data type. SQL Loader datatypes do not map 1:1 to database column data types. For a delimited file, you must use char.
See documentation: https://docs.oracle.com/cd/...
- 3,069
2
votes
Accepted
Why does Oracle Database rounds numbers smaller than `1e-130`
NUMBER Data Type
The NUMBER data type stores zero as well as positive and negative
fixed numbers with absolute values from 1.0 x 10^-130 to but not
including 1.0 x 10^126.
BINARY_DOUBLE
Value ...
- 40.4k
Top 50 recent answers are included