Skip to main content
Share Your Experience: Take the 2024 Developer Survey
6 votes

UTL_FILE directory name changes oracle 19c

Starting with 18c, the UTL_FILE_DIR parameter no longer exists, thus the usage of directory paths instead of directory objects in UTL_FILE is no longer possible. Desupport of UTL_FILE_DIR ...
Balazs Papp's user avatar
  • 40.7k
5 votes
Accepted

Oracle newly created DB is filled with datatables

Just to give you an analogy. When you install Windows OS or Linux OS, would you have expected zero files on your filesystem? The same applies for the Oracle Database. After creation of an Oracle ...
Bjarte Brandt's user avatar
5 votes
Accepted

Zero/NULL Case Trick

It is variously known as: conditional sum filtered aggregation conditional aggregation selective aggregation It may be either written explicitly using CASE or equivalently using FILTER syntax ...
Paul White's user avatar
  • 85.7k
4 votes

How to list all users that have a specific Temporary Tablespace like as default

You can use a select on dba_users Example select a.USERNAME from dba_users a Where TEMPORARY_TABLESPACE = '<NAME TEMP TABLESPACE>';
Roberto Góes's user avatar
4 votes

Oracle allows inserting into table even after revoking quota

'quota' specifies how much space may be used of a given tablespace. Space is allocated in extents - a logically contiguous series of blocks. When you create a table, you get one extent right off the ...
EdStevens's user avatar
  • 2,034
4 votes

Oracle allows inserting into table even after revoking quota

When you inserted the first row in the empty table, Oracle allocated an extent, which typically contains 8 blocks with default settings for the first extent of a table. 8 blocks (typical size of ...
Balazs Papp's user avatar
  • 40.7k
4 votes
Accepted

Report based on MV: Deliberately make the report fail if MV is out of date?

Why bother at all with a new column, when you can just check LAST_REFRESH_DATE in ALL_MVIEWS before executing the actual query. ALL_MVIEWS And there is basic/advanced query rewrite as well.
Balazs Papp's user avatar
  • 40.7k
3 votes
Accepted

Why am I able to create MV logs using PK — even though there is no PK?

A unique constraint on a NOT NULL column (which you have, as I checked this table in a database where Maximo was installed) is a good enough substitute for a primary key in this case. SQL> create ...
Balazs Papp's user avatar
  • 40.7k
3 votes
Accepted

Grant rights to create a table with an identity column on another schema

In order to create a table using the GENERATED BY... mechanism you need also the SELECT ANY SEQUENCE privilege, in addition to CREATE ANY SEQUENCE. I don't know why that is the case, since you are ...
Albert Godfrind's user avatar
3 votes
Accepted

Error "RMAN-04006: error from auxiliary database" in the middle of duplicate executiion

Your tnsnames.ora entry: MNTDB19 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = monta19-cluster-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ...
Balazs Papp's user avatar
  • 40.7k
3 votes
Accepted

ORA-39014 import in Oracle 19c from DMP file of Oracle12c

It looks like your Oracle instance is failing you. But let's roll it up from the beginning. IMPDP in Parallel To execute the IMPDP command with the PARALLEL parameter you have to meet the following ...
John K. N.'s user avatar
  • 17.9k
3 votes
Accepted

Get sequence value date?

There is no way to add a date to a sequence, or to know inherently when a sequence value was generated. You would need to add a CHANGEDATE column to each table and have the trigger update that column ...
3 votes
Accepted

Does trailing wildcard use index? (where column like 'value%')

Yes it can. This is just a range predicate really: Where column >= 'value' And column < 'valuf' A wildcard somewhere else in the string will change how much of the string can be used as the ...
Andrew Sayer's user avatar
  • 2,678
3 votes
Accepted

How do I SHOW TABLES in Oracle Database?

The documentation you referenced is not for Oracle 19c; it is for a defunct project that is completely unrelated. Oracle database does not have a SHOW TABLES command. Depending on what you're looking ...
pmdba's user avatar
  • 3,114
2 votes

Column name on NOT NULL constaint violation not shown

There is a security consequence to setting SQL92_SECURITY to false. From https://www.stigviewer.com/stig/oracle_database_12c/2020-04-01/finding/V-61429 The configuration option SQL92_SECURITY ...
pmdba's user avatar
  • 3,114
2 votes
Accepted

Column name on NOT NULL constaint violation not shown

Other than the obvious solution (grant select on data.xtest to app;), you can revert to the old behavior by setting SQL92_SECURITY to FALSE. In 11.2, its default value was FALSE, but in 19c, its ...
Balazs Papp's user avatar
  • 40.7k
2 votes

Oracle 19c database instance doesn't register with listener

Oracle Net configuration looks OK: local_listener looks OK. We should see the instance registered with the listener: doublecheck that local_listener parameter is the one used by database instance with ...
pifor's user avatar
  • 1,166
2 votes
Accepted

What permissions are required for complex cross-schema materialized view in Oracle?

The issue appears to be the "ON COMMIT" portion of your DDL. From the documentation: "To create a refresh-on-commit materialized view (REFRESH ON COMMIT clause), in addition to the preceding ...
pmdba's user avatar
  • 3,114
2 votes
Accepted

What privileges do I need to change the password of sys in an Oracle Database?

You'll need to be connected as a user with the SYSDBA privilege. Even "alter any user" will not be enough. SQL> select * 2 from session_privs 3 where privilege like 'ALTER ANY%';...
Connor McDonald's user avatar
2 votes

Oracle allows inserting into table even after revoking quota

if there is another command to really prevent the user from inserting again. The others have discussed why you are seeing what you are seeing. I'm focusing on this question. First The owner of a ...
Michael Kutz's user avatar
  • 4,829
2 votes

Migrating data from 11G Linux to 19C on Windows

Always exclude them. They contain version and instance-specific metadata that should not be migrated. When performing this kind of migration (porting applications to new DB versions), only ever export/...
pmdba's user avatar
  • 3,114
2 votes
Accepted

Oracle 19c Standard Edition RPM installation

Restrictions and Guidelines for RPM-Based Installations Patching Oracle Database software using RPMs is not supported. Please use the OPatch utility and follow the regular patching process to ...
Balazs Papp's user avatar
  • 40.7k
2 votes
Accepted

Is it possible to give PDB name to a procedure and select from it?

Sorry, but this is just wrong. A dblink can not be provided as a parameter like that. And a dblink is not required at all for this. You can access information about all tables in all PDBs using the ...
Balazs Papp's user avatar
  • 40.7k
2 votes

Can I change the profile of oracle sys user?

Our monitoring keeps telling us the password of sys has expired. The "Default“ profile has a standard limit of 180 days. You should not try to change the Profile for internal, Oracle accounts (...
Phill  W.'s user avatar
  • 8,805
2 votes
Accepted

Oracle 19c - find primary DB information from a mounted standby

select distinct machine from v$session where username='PUBLIC' and osuser='oracle'; You can find the hostname of primary database server.
sarat's user avatar
  • 60
2 votes

Zero/NULL Case Trick

Excel (and possibly other spreadsheet software) calls this a "pivot table" because you take the row-based groups and essentially pivot them by 90 degrees when you put them into columns. ...
AndreKR's user avatar
  • 587
2 votes
Accepted

Expdp partitioned table, Impdp Into non-partitioned table same structure

PARTITION_OPTIONS A value of MERGE combines all partitions and subpartitions into one table. Example The following example assumes that the sh.sales table has been exported into a dump file named ...
Balazs Papp's user avatar
  • 40.7k
2 votes
Accepted

Automatically materialize CTE? (instead of temporary table CREATE and INSERT)

The MATERIALIZE hint in the WITH clause does that. SQL> with cte as (select /*+ materialize */ 1,'Transaction-specific private temp table' from dual) 2 select * from cte union all select * from ...
Balazs Papp's user avatar
  • 40.7k
2 votes
Accepted

Can I convert a local user to a common user in Oracle?

Short answer - No. You can't convert between common and local user accounts. Even if you could, where would the database store the account's "data and objects"? You should not have any user/...
Phill  W.'s user avatar
  • 8,805

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