New answers tagged

0

I came to know from Oracle, inserting a sequence is possible with inserting the same sequence at the source as well, called cyclic sequence. For more details check the MOS doc "Implementing replication of cyclic sequences in GoldenGate (Doc ID 1321558.1)".


1

If it was me, I would partition by hour, then you can just drop 24 partitions every day. That is only 744 partitions per month. Then if you query within a relatively short window, you will look at much less data. You may also want to look at your index strategy. My guess is that the cost of maintaining the indexes is too high. IMHO, smaller partitions should ...


0

I confirm that a full or incremental backup during business hours can make your database inaccessible to end users and web services. Factors that can make things worse: allocating too many channels to RMAN using an out of support version like Oracle 10 having a database that is CPU or memory or network IO bound running Dataguard at the same time ...


2

First of all check whether your pluggable database is opened or not. SQL> select name, open_mode from v$pdbs where name='PDBORCL'; If it is in mounted mode then, SQL> alter pluggable database pdborcl open; Now on SQL Developer Create new connection as, Connection Name: demo-pdborcl Username: demo Password: ***** Connection Type: Basic(If you ...


0

Performance won't be affected by the number of users updating so much as by them updating a single table or group of tables. I have seen a number of applications use a single user account to access the database and have separate user accounts managed at the application level.


1

When it comes to Windows, something is always different. TWO_TASK works on Linux/UNIX, but not on Windows. The Windows version of TWO_TASK is LOCAL. SQL*Plus Environment Variables LOCAL Windows environment variable to specify a connection string. Performs the same function as TWO_TASK on UNIX. ... TWO_TASK UNIX environment variable ...


0

AUDIT_TRAIL If you want the SQL text to be captured, you need to use the extended option for audit_trail (db, extended or xml, extended). alter system set AUDIT_TRAIL=db, extended scope=spfile; or alter system set AUDIT_TRAIL=xml, extended scope=spfile; Changing this setting requires a database restart to take effect. For FGA you can specify a ...


1

In Oracle 12c Mixed Mode is enabled by default which makes the use familiar with Unified Auditing prior to switching to full Unified Auditing. Mixed Mode allows to pre-12c audit functionality to co-exist with new Unified Auditing functionality. Using Mixed Mode audit data can be found both in the traditional locations as well as in SYS.UNIFIED_AUDIT_TRAIL. ...


3

That depends on your disk subsystem. A full backup is going to involve a metric crud-load of I/O (that is a technical term). That is going to put a huge load on your I/O subsystem. Depending on how I/O bound your system is, how much spare bandwidth your I/O subsystem has, how fast your I/O subsystem is, etc. the impact will range from "yeah, maybe it's a ...


0

I had a very similar issue in Oracle 12c. In experimenting, the PRECOMPUTE_SUBQUERY optimizer hint 'forced' the subquery to execute first, thus making use of the defined indexes. In your snippet, that would look like this: ... where t.ObjectId = 12345 or t.Id in ( select /*+ PRECOMPUTE_SUBQUERY */ TaskId from ObjectAffectingTasks where ...


0

Creating Directory Objects and Export/Import are two different operations. Directory Objects are owned by SYS. A directory object maps a name to a directory path on the file system. DBAs must ensure that only approved users are allowed access to the directory object associated with the directory path. If you are not a privileged user, then before ...


2

As you can see you have RESOURCE role granted to the user user_kshitz. And you have to query the ROLE_SYS_PRIVS. SQL> select * from role_sys_privs where role='RESOURCE'; ROLE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- RESOURCE CREATE TRIGGER NO ...


1

This is expected behaviour. FULL A full export does not, by default, export system schemas that contain Oracle-managed data and metadata. Examples of system schemas that are not exported by default include SYS, ORDSYS, and MDSYS. 12.1.0.1 EXPDP Does Not Export APEX Schema (Doc ID 1912162.1) This is the expected behavior in 12.1.0.X version, ...


2

You should be using the system user for full datapump export/import operations. Using "/ as sysdba" or "sys as sysdba" may give you unpredictable results. Do not start Export as SYSDBA, except at the request of Oracle technical support. SYSDBA is used internally and has specialized functions; its behavior is not the same as for general users. ...


0

When you want to display aggregate results for each row without grouping, use the analytic versions of standard aggregate functions. For example: SELECT CASE mod(ROLE_TYPE, 2) WHEN 1 THEN MAX(SALARY) OVER () ELSE MIN(SALARY) OVER () END FROM HR;


0

Whenever the optimizer looks at a query to produce a plan the relevant statistics are examined and it will compare the cost of various plans and pick the cheapest. It doesn't look at all possible plans as it can 'shortcut' some options. An execution plan will be produced whenever a hard parse occurs and this only happens when the query to be executed does ...


2

That is the typical error message you get on Windows without properly setting up the environment. While the steps in the manual may work flawlessly on Linux/UNIX, on Windows you need extra steps. On Windows, you need to create a service with oradim, start it (if it was not started), and after that you can use sqlplus / as sysdba. The bare minimum is: set ...


1

In order to do a clone, you need to connect to the target database which is the database that you are cloning from. In this case you are doing a restore. So there is no auxiliary database. Just do a startup nomount, provided that you have a pfile with the proper settings. Then restore a specific control file as in "restore control file from ...


0

Is this doable in GoldenGate(GG)? The Map or Table parameter can be used to select Table, depending on whether to apply in Extract or in Replicat. The Cols and ColsEXCEPT attributes of the Table parameter can be used for Columns selection. I believe that the Oracle's official documentation is good link to create the solution. Here are some. ...


0

You can simply use IN for XYZ, so that you don't need to have one for each relation): SELECT * FROM T1 LEFT JOIN T2 ON (T2.T1_ID = T1.ID) LEFT JOIN T3 ON (T3.T2_ID = T2.ID) LEFT JOIN T4 ON (T4.T3_ID = T3.ID) LEFT JOIN XYZ ON (XYZ.ID IN (T1.XYZ_ID, T2.XYZ_ID, T3.XYZ_ID, T4.XYZ_ID))


-1

The problem is the size of message box. Is enough with resizing the message box. Go to lower rigth corner for message box and resizing. The screen does not go away because is waiting your response in the message box.


0

If you use DataPump impdp and expdp commands, you can include the parameter metrics=yes in the impdp command parameter file. The log file will then time-stamp each phase.


0

Going forward, you could implement auditing of alter user commands which would capture when a user ID is unlocked, but would not capture when it got locked due to number of unsuccessful login attempts. Take a look at http://docs.oracle.com/database/121/DBSEG/audit_config.htm#DBSEG1115 for details. If you really need to know when it was locked, etc. as ...


0

Unless your development or testing team already has a set of regression test cases, you are out of luck. The possible things to test are astronomical. Now, as others have pointed out, there are some things to look out for, like more restrictive privileges, and reviewing the "What's New" for both 11g and 12c. One big example from the past that burnt a lot ...


2

So initially it's T2[ id PK, name, T1_id NOT NULL, -- because it is hierarchy, T2 can not exist outside the parent (T1). FK1 = T1_id ref T1(id) ] ... and you also say LINK_T1_T2_XYZ[T1.id, T2.id, XYZ.id] with a check T1 should parent of T2 You can migrate keys down the entities hierarchy T2[ id, name, T1_id NOT NULL, ...


1

First off, if your example is ACTUALLY a good representation of what you are trying to accomplish, then you are going about it all wrong.. You have three domains here - one is businesses, one is business offices, and one is products. Table BUSINESS should only contain information about the actual business (Netflix, for example) ... no office information at ...


0

The Oracle database instance has several background sessions it creates in order to function, and those count against the number of sessions. For example, here is an Oracle 12c instance, one user connected: Prod:SYS> select type, count(*) from v$session group by type; TYPE COUNT(*) ---------- ---------- USER 1 BACKGROUND ...


0

I would guess that there is a weekly maintenance job that is running on that one day a week, that is affecting something that is forcing extra reads of the index. Is your daily batch job always run long on the same day of the week each week? If so, look at the views dba_scheduler_jobs and dba_scheduler_schedules to see if any jobs are scheduled. Now the ...


0

Any application that uses GPUs for processing has to be custom-written to access the GPU's processing power. Where GPUs shine are in floating point arithmetic, something that is low on the list of database operations, so no, no database software is likely to harness GPUs for processing.


3

No, you would have to use separate parameter files/spfiles for each database. As you've pointed out, you'll need some settings to differ (locations of files, database name, etc.). In many environments, you'd want to adjust memory and other performance settings differently because the development environments often don't have nearly as much RAM as the ...


1

Generally we use a different spfile for every database. Even though the spfile is binary, you can use the strings command to extract out the settings in the spfile. and you can also compare spfiles from one database to another to look for differences. It seems to me that the underlying issue is a change management issue. You should have a configuration that ...


4

As has already been mentioned more than once, you cannot expect rows to be in a certain order without specifying that order explicitly using the ORDER BY clause. For the problem described in your question, you actually do not need a UNION at all. Use only the LIKE condition to cover both full and partial matches: WHERE name LIKE '%roma%' Then use a ...


1

It seems like the obvious answer is that you can't stop a shutdown that is happening, but you can do either a shutdown immediate or shutdown abort from a different session if you did a shutdown normal. As long as you have enough online redo log groups and each group has a log that is large enough, then you can recover from either a shutdown immediate or ...


4

The simple answer is.... To enforce an order in ANY resultset, not just your specific case, you have to use an order by clause. The Oracle documentation spells it out clearly: Use the ORDER BY clause to order rows returned by the statement. Without an order_by_clause, no guarantee exists that the same query executed more than once will retrieve ...


0

Could you please tell us how you connected to rman? For example: rman target sys/asdf@prod auxiliary sys/asdf@dev The reason for you problem is that the two databases have trouble communicating with each other. If you use auxiliary / because your rman session is located on the dev server this is not going to work. The reason is pretty simple as follows. ...


1

If I'm reading this correctly you want to see all the full matches followed by all the partial matches with sorting within each set. If that is the case then you can add a column to your column list as below; SELECT * FROM (SELECT 'FULL' as match_type, column_1, column_2 FROM a_table UNION SELECT 'PART' as match_type, column_1, column_2 FROM ...


0

Community Wiki answer containing the solution originally added to the question by the author. Finally I found the solution: I didn't change permission to be owned by the oracle user and oinstall group for the database dir in which the Oracle software is. I think there are some scripts or programs related to listener and the oracle user could not run it.


0

I don't have direct experience of an index with this kind of REGEX so I would avoid it by creating a 'standardised' telephone number column. Create a process to go through the telephone numbers and store the clean, formatted version in the new column. Perform the 'standardising' process on the user input and use that as a search against the new telephone ...


0

You can use: shutdown abort; But it is not recommended on production database.


2

Reading the explanation in the question, it seems that you don't really care about the ordering but you want to know if for the same name there is at least one row with status = 'ACTIVE'. This can be done with a window function, using OVER (PARTITION BY name). The CASE expression has a default of NULL so MAX() will return null if there is no such value. ...


0

This will return your desired results and will also work if you have more than the 2 example rows given...if you want to return ACTIVE if any of the other rows are ACTIVE. I'm not sure if this meets your requirements or if you are looking for a solution that will evaluate only the closest row. CREATE TABLE #temptable ([status] varchar(10), [name] ...


2

UPDATE: What Worked... So after reading the link from @Raj and reading @ora-600's answer I tried to validate the database with the RMAN command backup check logical validate database;. While this worked fine, it was also clear that it was not looking at everything that the ANALYZE INDEX command would. After trying many different variations, I finally ...


3

I write a bash script to perform the backups (RMAN, datapump and also xtrabackup and dump for MySQL in a similar way) and then call that from cron. That way you can handle the email as you want. So a simple crontab line is like this 00 18 * * 0-6 /dba/bin/rman.sh > /tmp/dba_rman_SID.log 2>&1 Then everything is done in the script. So define ...


0

Recently I had to rewrite a bunch of queries from an implicit FOR loop into explicit cursors. The reason was that the queries fetched data from an external database via link and this database had a different encoding than our local database. When transferring data from the implicit cursor into a locally defined record type misterious intermittent errors ...


0

As other have asked, it would be helpful to know what business requirements need this functionality. For example, if you need to know when a certain row started appearing in query results for other sessions (which would only occur on the commit, not the insert), then you could rename your table and add a column that is an effective date. When you insert ...


0

You have three cursors here, but one is implicit in the for loop. Your code indicates that for each row in the testnirmal table you want a column name from the table and then a row from the testnirmal table that has a literal value of that column name. It is unclear what you are trying to accomplish, but here is one interpretation: CREATE OR REPLACE ...


0

No You can get a list of reserved words by typing help reserved words, but that's about it.


1

You can try: shutdown immediate startup mount alter database datafile '/u02/oradata/hfd1/index/index17.dbf' offline drop; alter database open


1

Redo logs can be mirrored, your query does not take into account that. Controlfiles are also part of the database. select 'Datafiles', sum(bytes)/1024/1024 from dba_data_files union all select 'Tempfiles', sum(bytes)/1024/1024 from dba_temp_files union all select 'Redo logs', sum(bytes)/1024/1024 from v$log l join v$logfile lf on (l.group# = lf.group#) ...


1

SESSIONS Default value Derived: (1.5 * PROCESSES) + 22 ... Oracle uses the default value of this parameter as its minimum. Values between 1 and the default do not trigger errors, but Oracle ignores them and uses the default instead. If you want to decrease sessions, you need to decrease processes as well. Still, if you decrease processes, ...



Top 50 recent answers are included