I'm exporting schema with following command - expdp system/system@xxx.xxx.xxx.xxx:1521/orcl schemas=schema-name directory=test_dir dumpfile=Schema.dmp logfile=SchemaLog.log but it results into following error - Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production ORA-31626: job does not exist ORA-04063: package body "SYS.DBMS_INTERNAL_LOGSTDBY" has errors ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_INTERNAL_LOGSTDBY" ORA-06512: at "SYS.KUPV$FT", line 1009 ORA-04063: package body "SYS.DBMS_LOGREP_UTIL" has errors ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_LOGREP_UTIL" I googled a lot and tried solutions provided around ORA-31626: job does not exist and ORA-04063: package body "SYS.DBMS_INTERNAL_LOGSTDBY" has errors but none helped to solve the problem. Could you please help to resolve this?

I have a two node Oracle RAC cluster with that runs an oltp application against it. I have both a production and a duplicate test environment with the same setup. Upon running stress tests on the Test environment (about 160 tps), The average latency is about 70ms. I noticed that when I examined the maximum transaction time within each minute, that there is a regular spike in the maximum transaction time every 5 minutes of several transactions that take about 1200-1500 ms. I've traced the individual long transactions down to the second and them seem to last for anywhere from 3-5 seconds. There don't seem to be any regular running jobs that would effect the transactions. In environment, we see the same thing and in fact once in a while, we see bigger spikes of transactions taking 15-20 seconds (15000-20000 ms) to complete. While our production environment is in a pilot mode right now, and only pushes about 4 tps, we do still see these spikes where maximum transaction lengths reach 1200-1500 ms, and they also seem to occur on 5 minute intervals. I have performed ASH analytics on the small few second intervals, and it seems that there is no common wait event that occurs during these spikes. Sometimes we see "log file sync" events, sometimes we see "gc busy" or other "gc" wait events. It seems to vary. I'm just trying to figure out how to diagnose the underlying cause of this periodic slowness. Any ideas would be appreciated.

Couple of weeks ago i faced UNDO issue at my database. Now it is always full and system asks to add some space. I have added another 32GB autoextensible file now it is 32+32+32, but in a couple days, situation repeated. Previously i just ajusted RETENTION in order to get rid of "**ORA-01555: SNAPSHOT TOO OLD**" Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 50000 undo_tablespace string UNDO_NEW0 SQL> select RETENTION from dba_tablespaces where TABLESPACE_NAME='UNDO_NEW0'; RETENTION ----------- NOGUARANTEE SQL> select sum(bytes)/1024/1024/1024 "size_in_gb" from dba_data_files where tablespace_name='UNDO_NEW0'; size_in_gb ---------- 95.9999542 SQL> select u.maxquerysqlid, 2 round (((max (u.unexpiredblks) * p.value) / 1024 / 1024 / 1024), 2) as gb_unexpire 3 from dba_hist_undostat u 4 , v$parameter p 5 where 1 = 1 6 and p.name = 'db_block_size' 7 and u.end_time >= sysdate - 7 8 group by u.maxquerysqlid, p.value 9 having round (((max (u.unexpiredblks) * p.value) / 1024 / 1024 / 1024), 2) >= 95 10 order by round (((max (u.unexpiredblks) * p.value) / 1024 / 1024 / 1024), 2) desc; MAXQUERYSQLID GB_UNEXPIRE ------------- ----------- cj2h00pd9h4uf 96 0rc4km05kgzb9 96 SELECT tablespace_name tablespace, 2 status, 3 ROUND (SUM (bytes) / 1024 / 1024 / 1024, 2) sum_in_gb, 4 COUNT (*) counts 5 FROM dba_undo_extents 6 GROUP BY tablespace_name, status 7 ORDER BY 1, 2; TABLESPACE STATUS SUM_IN_GB COUNTS ------------------------------ --------- ---------- ---------- UNDO_NEW0 ACTIVE 0 1 UNDO_NEW0 EXPIRED .01 8 UNDO_NEW0 UNEXPIRED 95.99 25312 SQL> SELECT ses.sid, 2 ses.username, 3 ses.status, 4 ses.program command, 5 round(tra.used_ublk / 8192 / 1024 ,1 ) as MB 6 FROM v$SESSION ses INNER JOIN v$TRANSACTION tra ON ses.saddr = tra.ses_addr; no rows selected So my questions are: 1. What may be the reason that despite I have add 32 GB for UNDO it still asking for more? For last couple of years 64GB was completely enough, now 96GB - almost everyday it asks to expand UNDO. Where i must to dig in order to get the solution? Of course data amount is growing, but linear and slowly. There were not any new features and calculations implemented. 2. How come the internal Oracle`s mechanism got 96GB of undo? I am talking about **"0rc4km05kgzb9" - "select 1 from obj$ where name='DBA_QUEUE_SCHEDULES'"** ? I have seen that Oracle have a doc about it, but i do not have access there. 3. Is it OK that so many SQLs consumes almost full size of UNDO? I understand that decreasing of RETENTION may help here, but cannot figure out why i have such a problems now, after it worked well for a so long time?

consider this working pl/sql block: declare o_result boolean; io_error varchar2(30000); tab1 someTable1; tab2 someTable2; begin tab1(1).col1 := 'str'; tab1(1).col2 := 1; tab2(1).col1 := 'str'; tab2(1).col2 := 0.5; tab2(1).col3 := 6; o_result := some_function(io_error, 2,'str', 5, to_date('01-JAN-23','DD-MON-YY'), to_date('31-DEC-23','DD-MON-YY'), 4, 'A', tab1, tab2, 'str', 'B', 'str'); end; how to get the explain plan for pl/sql function calls where its inputs are dates and custom types?

I have a table called " Product " it has: **ID_P**, **Name**, **expiration date** Now I want to create another table called "Stock" this table has **ID**, **Quantity_of_the_product**, **product_location_in_the_stock**, **ID_P** (foreign key), and **expiration date** I want to bring the expiration date from the table product I am new to SQL oracle so please be patient with me I am using: SQL ORACLE **UPDATE:** and is it right if I do this CREATE TABLE stock (ID, Quantity_of_the_product,product_location_in_the_stock, ID_P foreign key) AS SELECT expiration_date FROM Product WHERE stock.id_p = product.id_P

I have an on premise oracle 11g database that i have to migrate to an EC2 instance hosting Oracle 11g in AWS. I have three options to achieve this 1. Use Oracle Data guard to replicate the on premise database to cloud database 2. Using RMAN to backup the existing database and restore it on Cloud 3. Using expdp and impdp commands to export and import the database My database size is approximately 2 TB. I have following doubts Q1: What is the best way among the three options? Q2: Using data guard means that the AWS and the on premise database should be connected the entire time until the data is replicated on cloud, what if there is a network disconnect in between the process? Do i have to restart the replication process or will it continue from where it stopped? Edit: We have a 2 day downtime window in which the data has to be migrated and restored on cloud. The on premise database is hosted on Solaris Operating System and Operating System on the AWS cloud will be RedHat Linux since Solaris in not available in AWS. Will Oracle Data guard or RMAN support the change in the Operating system on the servers?

I have a table (table1) with fields package_id and contract_id. Data in table 1 is like that 1 package_id can have 1 or more contract_id associated with it. I have another table (table2) with contract_id and status. status can be ('active' or 'dead') I need to find the package_id which doesn't have any contract_id associated with it which is 'ACTIVE' or in other words I need to find package_id which has all contract_id associated with it in 'DEAD' state. Please help.

I have an Oracle database 11gr2 and I have installed Oracle Grid on Oracle Linux 5.9. I have two disk groups. /etc/init.d/oracleasm listdisks : flash_recovery_area DATA The first disk group is mounted on /u01 and the second is mounted on /flash_recovery_area. The value of fra parameters are: db_recovery_file_dest = /+flash_recovery_area db_recovery_file_dest_size = 30G When I want to connect to rman and get backup from current database, I can but I can not find anything in /flash_recovery_area destination. Can any one help me?

I have a table partitioned by list create table ICT_ENCOURS_BRUT ( code_exercice VARCHAR2(4), CODE_PRODUIT VARCHAR2(2), ....... ) partition by list (CODE_EXERCICE) ( partition CODE_EXERCICE_2018 values ('2018') tablespace DATA_BI_PARTITION_2018 , partition CODE_EXERCICE_2019 values ('2019') tablespace DATA_BI_PARTITION_2019 , partition CODE_EXERCICE_2020 values ('2020') tablespace DATA_BI_PARTITION_2020 , partition CODE_EXERCICE_2021 values ('2021') tablespace DATA_BI_PARTITION_2021 ); and i have 2 indexes : CREATE INDEX EKIPIC.IDX_CODE_EXERCICE ON EKIPIC.ICT_ENCOURS_BRUT (CODE_EXERCICE) TABLESPACE INDEX_BI LOGGING LOCAL ( PARTITION CODE_EXERCICE_2018 NOCOMPRESS TABLESPACE INDEX_BI_PARTITION_2018, PARTITION CODE_EXERCICE_2019 NOCOMPRESS TABLESPACE INDEX_BI_PARTITION_2019, PARTITION CODE_EXERCICE_2020 NOCOMPRESS TABLESPACE INDEX_BI_PARTITION_2020, PARTITION CODE_EXERCICE_2021 NOCOMPRESS TABLESPACE INDEX_BI_PARTITION_2021 ) ONLINE; create index IDX_CODE_PRODUIT on ICT_ENCOURS_BRUT (CODE_PRODUIT) tablespace INDEX_BI ; the explain plan show a full scan access when i perform SELECT * from ICT_ENCOURS_BRUT where code_produit='LOA' ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 812259 | 242053182 | 32268 | 00:07:32 | | 1 | PARTITION LIST ALL | | 812259 | 242053182 | 32268 | 00:07:32 | | * 2 | TABLE ACCESS FULL | ICT_ENCOURS_BRUT | 812259 | 242053182 | 32268 | 00:07:32 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ------------------------------------------ * 2 - filter("CODE_PRODUIT"='LOA') the same way when i try to use select * from ICT_ENCOURS_BRUT i where i.code_exercice='2019' Plan Hash Value : 2304474508 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1604226 | 474850896 | 12511 | 00:02:56 | | 1 | PARTITION LIST SINGLE | | 1604226 | 474850896 | 12511 | 00:02:56 | | 2 | TABLE ACCESS FULL | ICT_ENCOURS_BRUT | 1604226 | 474850896 | 12511 | 00:02:56 | -------------------------------------------------------------------------------------------- Why cannot use the indexes assuming that stats is updated?

I have duplicated the primary database to standby database and then I added the primary database in data broker configuration. But when I'm trying to add standby database I'm getting following error: DGMGRL> add database 'orcl_standby' as connect identifier is orcl_standby maintained as physical; Error: ORA-16606: unable to find property "" Error: ORA-16554: translation not valid Failed. Is this a problem with log_archive_dest_state_2? I have enable it. Or is there any problem in DR2ORCL.DAT files? Any help will be appreciated.

I need your help about backup in oracle database. Currently I am using Export Utility of Schema Backup but taking more time to complete and size is 40GB. For full database backup using Image Copy and taking time more than 15 hours to complete and size is 650GB. Which backup method is best for schema backup and full database backup which take less time and less size. Database version Oracle Database 11gR2 Regards

I have server under RHEL 6.3 installed on that server Oracle 11gr2 and 12c, I want to create user lets say Ahmad but I want this user to be sysdba on both servers, for single server I edit .bach_profile to be like the following: 11g: ORACLE_HOSTNAME=oracledev; export ORACLE_HOSTNAME ORACLE_UNQNAME=DB11G; export ORACLE_UNQNAME ORACLE_BASE=/oracle11gr2/u01/app/oracle; export ORACLE_BASE ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME ORACLE_SID=DB11G; export ORACLE_SID PATH=/usr/sbin:$PATH; export PATH PATH=$ORACLE_HOME/bin:$PATH; export PATH LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH PATH=$PATH:$HOME/bin unset USERNAME cd $ORACLE_BASE export PATH and if I want to make him sysdba for 12c I use the following: export TMP=/tmp export TMPDIR=$TMP export ORACLE_HOSTNAME=oracledev export ORACLE_UNQNAME=cdb1 export ORACLE_BASE=/oracle12c/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/db_1 export ORACLE_SID=cdb1 export PATH=/usr/sbin:$PATH export PATH=$ORACLE_HOME/bin:$PATH export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib My question is it possible to marge those parameters together without making a conflict, if yes please advise

I'm trying to setup data guard broker in my production instance. I have duplicated the primary database to standby database successfully. I've added primary database to data broker configuration and when I'm trying to add standby database following error is getting DGMGRL> ADD DATABASE 'orcl_standby' AS CONNECT IDENTIFIER IS orcl_standby maintained as physical; ERROR: ORA - 12154 : TNS: could not resolve the connect identifier specified Tnsping from primary db to standby db and vice-versa is successful. I'm able to connect primary db from standby db using sqlplus sys/password@orcl as sysdba but when trying to connect to standby db from primary db, error is getting like > ORA - 12154 : TNS: could not resolve the connect identifier specified. Tried out all possible solutions. Below is my tnsnames.ora file ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.25.42)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) (SID = orcl) (UR = A) ) ) ORACLR_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) (CONNECT_DATA = (SID = CLRExtProc) (PRESENTATION = RO) ) ) ORCL_STANDBY= (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.25.44)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl_standby) (SID = orcl_standby) (UR = A) ) ) Any help will be appreciated

I am new to creating an Oracle database using the command line. I am logged in as superuser; now I would like to create a new blank database, "mynewdatabase". $ . oraenv ORACLE_SID = [oracle] ? mynewdatabase Is there a step-by-step example of how I can create a new database instance. Most of the examples I have found on the Internet show examples with a Windows environment and not Linux. Once I know how to create the new database environment, I can use ... sqlplus / as sysdba and begin working some database magic. Any assistance would be greatly appreciated!

I am using Oracle 11g. I would like to know that whether it is possible to import from incremental level 0 & 1 backups to a new host using RMAN. If yes, how can I do that? For level 1 I am using differential method.

I'm trying to build an oracle database server but it's setting up the users tablespace as a smallfile. How can I either install brand new database with users being bigfile or changing the existing USERS tablespace to bigfile?

I am using the Preprocessor feature in Oracle 11g r2 to load several data files in a table. However, the raw files seem to have certain rows that shouldn't be imported with the rest. For example: col1 col2 col3 col4 A 1 2 3 A 2 3 4 B ab bc cd So, I want to only load the rows with col1 = 'A' and load the rows with col1 = 'B' into another table. How can I do this inside the processor?

According to Document 745809.1, Installing Csscan. While running the "Csminst.sql" we are getting below error

SQL> @$ORACLE_HOME/rdbms/admin/csminst.sql

create user csmig identified by csmig password expire account lock

*

ERROR at line 1:
ORA-28003: password verification for the specified password failed

I want to recreate the table below in SQL server I have tried using SSIS but it does not know how to handle the lOV Data type how can I recreate this table in SQL server and migrate the data to it. create Table Listing ( NAME VARCHAR2(30), VALUES NUMBER, LIST_OF_VALUES LOV, id VARCHAR2(30) ); I am new at trying to converting this type of data or data type, in general, your help would be appreciated.

I need to create an alert on my application table when it reaches 98% of its threshold. I wrote an SP which checks the rows and logs the information into a log file which was monitored by alert system and that raised alarms successfully. But now my DBA team is saying they cannot grant the **application DB user** read/write privileges on UTL_FILE in production. And OEM (*Oracle Enterprise Manager*) can't be used at application level. - What are my options? - How can I get an alert on table size? ***Reference:** UTL_FILE * > With the UTL_FILE package, PL/SQL programs can read and write operating system text files. UTL_FILE provides a restricted version of operating system stream file I/O.