New answers tagged

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 ...
Paul W's user avatar
  • 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 ...
Paul W's user avatar
  • 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 ...
Balazs Papp's user avatar
  • 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;
Ana Esquivel's user avatar
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, '...
Michael Kutz's user avatar
  • 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 ...
Balazs Papp's user avatar
  • 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','...
Paul W's user avatar
  • 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 ...
Alexander Molchanov's user avatar
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 ...
Phill  W.'s user avatar
  • 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) > ...
Paul W's user avatar
  • 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 ...
Littlefoot's user avatar
  • 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), '<<...
TSCAmerica.com's user avatar
-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 ...
Viren's user avatar
  • 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 ...
Keith Rust's user avatar
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 ...
Littlefoot's user avatar
  • 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$ ---------- ...
Balazs Papp's user avatar
  • 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" *....
S M's user avatar
  • 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/...
Balazs Papp's user avatar
  • 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 ...
Boneist's user avatar
  • 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 ...
Gerrat's user avatar
  • 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 ...
Paul W's user avatar
  • 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 ...
Balazs Papp's user avatar
  • 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).
StinkySocks's user avatar
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 ...
Shane Lobo's user avatar
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, ...
Paul W's user avatar
  • 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 ...
Balazs Papp's user avatar
  • 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/...
pmdba's user avatar
  • 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 ...
Balazs Papp's user avatar
  • 40.4k

Top 50 recent answers are included