5

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 Database, there will be tons of objects (tables,functions,procedures,indices,packages,etc..) constitute your Oracle Database. All these objects are owned by SYS,...


4

You can use a select on dba_users Example select a.USERNAME from dba_users a Where TEMPORARY_TABLESPACE = '<NAME TEMP TABLESPACE>';


4

'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 bat. When you start inserting, you are using space that was previously allocated for the extent. Only when that extent is full and more space is needed will ...


4

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 single block is 8 KB) can store a lot more than 1 row, so even though you revoked the quota, that space is already allocated, and you can write into it, which is ...


4

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.


4

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 Initialization Parameter Starting in Oracle Database 18c, the UTL_FILE_DIR parameter is no longer supported. Instead, specify the name of a directory object. Even in ...


3

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 table t1(c1 number, c2 number); Table created. SQL> create materialized view log on t1; create materialized view log on t1 * ERROR at line 1: ORA-12014: ...


3

Your tnsnames.ora entry: MNTDB19 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = monta19-cluster-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = mntdb19.vboxlab.es)(UR=A) ) ) Your listener status: Service "mntdb19" has 1 instance(s). Instance "mntdb19", status UNKNOWN, has 1 ...


3

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 requirements: This parameter is valid only in the Enterprise Edition (check) of Oracle Database 11g or later. To import a table or table partition in parallel (...


3

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 for each modified record, or create an audit table with ROWSTAMP and CHANGEDATE and insert into that table in addition to every other change that the triggers ...


2

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 able to create a standalone sequence. I suspect it has to do with setting up the link between the table and the sequence. Here is an example: SQL> connect ...


2

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 show parameter local_listener. Are you sure that database instance is not using SPFILE and if yes, the displayed PFILE is the right one ? What is missing in ...


2

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 privileges, you must have the ON COMMIT REFRESH object privilege on any master tables that you do not own or you must have the ON COMMIT REFRESH system privilege." So ...


2

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%'; PRIVILEGE ---------------------------------------- ALTER ANY TABLE ALTER ANY CLUSTER ALTER ANY INDEX ALTER ANY SEQUENCE ALTER ANY ROLE ALTER ANY PROCEDURE ...


2

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 specifies whether table-level SELECT privileges are required to execute an update or delete that references table column values. If this option is disabled (set to ...


2

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/import your application schemas and user accounts. Never do a "full" export/import. If you have already overwritten some data dictionary objects and ...


2

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 apply Oracle Database patches. An RPM-based Oracle Database installation is not available for Standard Edition 2. RPM-based database upgrades using rpm -...


2

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 table will always have full control over that table. This can be a security risk. To prevent that, applications should never connect as the owner of the table. You ...


2

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 CDB_TABLES view. CDB_* Views For every DBA_* view, a CDB_* view is defined. In the root of a multitenant container database (CDB), CDB_* views can be used to ...


2

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 (indeed, there is one, very oddly named, account that you cannot changed in any way, shape or form!). You should not change the behaviour of the DEFAULT profile. ...


1

ORA-03114 not connected to ORACLE Cause: A call to Oracle was attempted when no connection was established. Usually this happens because a user-written program has not logged on. It may also happen if communication trouble causes a disconnection. Common causes for the ORA-03114 error include: Listener process is not running DB is shutting down or session ...


1

Try basing your MV directly on the source table and not on an intermediate view. Include your "isgis=1" condition in the MV query. create materialized view primary_system.workorder_mv build immediate refresh force on demand start with sysdate next sysdate + (30/(60*60*24)) as select cast(workorderid as number(38,0)) as objectid, wonum, ...


1

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 default value is TRUE. alter system set sql92_security=false scope=spfile; Then restart the database, as this parameter can not be changed online. Demo: create ...


1

This is a documented behavior change in 12.2: RDBMS 12.2 - Users Being granted The ALTER USER System Privilege Cannot Change SYS Password (Doc ID 2263715.1) SYMPTOMS A user who is granted the ALTER USER system privilege is not able to change the SYS password as of Oracle RDBMS 12.2.0.1. This was possible in the earlier versions. CAUSE This is the expected ...


1

I am using Oracle 11.2: SQL>select banner 2 from v$version 3 / BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production CORE 11.2.0.4.0 Production TNS for Solaris: Version 11.2.0.4.0 - Production ...


1

Our identity management tool wants to change the sys password regularly ... I would suggest that this is unwise. The SYS password can be used by more than just Oracle Database. The DataGuard Broker and Enterprise Manager are two that spring to mind. The latter will have the SYS password stored away in it at some point (given OEM's tendency to "...


1

I had exactly the same errors after installing a 32-Bit client (19.9.0.0.201020). Windows application event log stated an error concerning "C:\Windows\system32\ntdll.dll". This is a 64-Bit DLL with the 32-Bit counterpart in "C:\Windows\SysWow64". This is the solution You need to add C:\Windows\SysWow64 to the PATH environment variable. ...


1

Yes. I am upgrading from 11.2 to 19 and my link to 10.2.0.3 works: SQL> select * from v$version@MYDBLINK; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi SQL> select banner from v$version; BANNER -------------------------------------------------------------------...


1

Based on this document from Oracle Support, the answer appears to be "No". A 19c client (the owner of your DB Link) is only supported/compatible with a server version of 11.2 or greater. Conversely, a 10.2 database can only support a client as new as 12.1, so any upgrade of your 12.1 database to any newer version will break your db link.


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