Tag Info

New answers tagged

0

I ve solved the problem by running the setup "as administrator"


0

There are two ways: first - using regexp_replace like here - Eliminate duplicates in ListAgg (Oracle), second - use distinct inside a subquery: select fk_proddes_id,fk_id_cod_deseu, listagg(to_char(cod_eliminare), ',') within group (order by cod_eliminare desc) from (select distinct fk_proddes_id,fk_id_cod_deseu, cod_eliminare from ...


1

Sounds like the data you're exporting isn't utf-8 encoded, or is being chopped up / mangled in transit. This: Ã9ÃýÃ0Ã looks like what happens when you decode utf-8 as iso-8859-15, cp1252, or related 1-byte encodings. But it's not valid utf-8 when demangled. Perhaps you've cut it part way through a string, rather than copying from the beginning of the value? ...


0

The sub-select SELECT Phase.* will return more than one column but it is in the SELECT section of the outer query where only one column is allowed. Move this nested select into the WHERE clause as a join.


3

This online compression is extra cost option. And moreover usually online compression has worse compression ratio, then offline one. It's because the 1st one work on row level while the latter one works on block level. So if you really want to spare some space in the database and your old data are mostly read-only you should use: ALTER TABLE table_name MOVE ...


0

Or you could simply lock the account used by your user ALTER USER username ACCOUNT LOCK;


0

You can rebuild the indexes after or use the WITH UPDATE INDEX when executing your statement. E.g. ALTER TABLE table_name MOVE COMPRESS FOR ALL OPERATIONS WITH UPDATE INDEX; Note. Your statement will not compress existing data: only future data inserted.


0

select rap.DENUMIRE,a3.ID, op.FK_ID, count(*) over (partition by op.FK_ID) AS count from ANEXA3_TABEL1_ROWS a3 inner join RAPORTORI_AMB rap on rap.id = ch.FK_ID_RAPORTOR inner join ANEXA3_TABEL1_OP op on op.FK_ID = a3.ID


0

Tring ESF Database Migration Toolkit, you can use it to move data from Oracle to SQL Server or back without any scripts.


0

From the command line from the command line enter lsnrctl and then stop (stops the listener, only connections from the server possible) connect to sqlplus as sysdba shutdown immediate ( makes sure all sessions are terminated) startup restrict (only allows users with dba privilege to log on) as Mindaugas Riauba mentions disable all jobs likely to change ...


0

If you need to do it within Oracle database then comment command is your answer. But there are couple of disadvantages of documenting schema that way: certain privileges are required (not alwasy possible) in some cases you can't interfere with (3rd party/legacy) database you can't use long descriptions (limit to 4000 characters) you can't use or formatted ...


0

Is hard to say which method is faster, but I would say from my experience use source backup is faster than import. It seems the issue you see with the file header when you use the source files comes from the file format of AIX which is big endian, while on Linux is small endian. The files need to be converted. This can be done using RMAN: e.g. RMAN> convert ...


2

Yes! This is called a 10053 trace in Oracle parlance. It's more formally known as "Tracing of Optimizer Computations". Check out MOS doc 225598.1, or Jonathon Lewis' blog post here: https://jonathanlewis.wordpress.com/2014/05/23/10053-trace/ There's loads of hits for "10053 trace" so if you can't find what you need, it's probably out there in search-land. ...


2

There is absolutely nothing about this on the Internet - even the Oracle docs regarding the %_ASSEMBLIES views don't actually state what they show. However, if you go digging around in $ORACLE_HOME/rdbms/admin you'll see the following reference to the data dictionary views (which sit on assembly$: remark remark FAMILY "ASSEMBLIES" remark remark Views for ...


0

I think this page has what you are looking for - Assembly: Assembly is Microsoft's term for the module that is created when a DLL or .EXE is complied by a .NET compiler. If I understand it correctly it is the .NET equivalent for Java's "class files". Starting 11g, you can develop and deploy .NET procedures and functions in Oracle database. Here ...


2

Sounds like a data pump using a network link would do the job. It's always preferable to run it during quieter hours but if your source database is not under powered then you might be able to run it during regular hours without your user's noticing any difference in performance. From the article create a database link from the destination to the source ...


3

Use this one: DROP INDEX SYSTEM."IX_isbn_send_h.D_STAT"; Note, when you use quotes, then names are case-sensitive in Oracle.


0

I found out what the problem is, in my XE 10 additional tablespaces were created. When the import into 11 discovers those tablespaces do not exist yet, it tries to recreate them with the statement with which they were created in the first place, in XE 10. That explains where the wrong paths are coming from. I just copy & pasted the create statements ...


0

When you use sqlplus a549189/myPassword@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=identityprovisionreport.fmr.com)(PORT=1524))(CONNECT_DATA=(SID=AARCER1)))" then you don't need any tnsnames.ora file. Your entry in tnsnames.ora file should be like this: AARCER1 = (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST= identityprovisionreport.fmr.com)(PORT=1524)) ...


-2

You can do it by increasing column size, then drop . (Because drop column statement try to validate the data, then drop ) like .. SQL> ALTER TABLE OWNER.CONFIG MODIFY(DTE VARCHAR2(500 BYTE)); SQL> ALTER TABLE OWNER.CONFIG DROP COLUMN DTE ; example:- SQL> select * from v$version ; BANNER ...


1

This is not going to work. Oracle binaries of a certain version correlates to the Oracle Database dictionary of the same version. RMAN is a backup and recovery tool. It is NOT an upgrade tool. You need to restore and recover you database on 11g binaries. From there you can upgrade your database to 12c. You can have many versions of Oracle Database ...


1

The question would have been a better candidate for stackoverflow. Anyways, the problem with your SQL is that the GROUP BY should only be on the author name and not on author name + book title. From the GROUP BY on author name, get the max(number_of_pages) and wrap it inside another SELECT to to get the books by that author that have that those number of ...


0

This should give you a more nuanced view of the limits and usage select resource_name, current_utilization, max_utilization, limit_value from v$resource_limit where resource_name in ('sessions', 'processes'); From here


1

select c.classid, c.classname, count(case when s.status = 'failed' then 1 end) as no_of_failed, count(case when s.status = 'passed' then 1 end) as no_of_passed from class c left join students s on c.classid = s.classid where c.schedule = 'MWF' group by c.classiid, c.classname; This exploits the fact that aggregate functions will ...


0

I succeeded to connect after these instructions in cmd: SQL> connect sys/manager as sysdba Connected. SQL> shutdown immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. ..... ORA-01172:... ORA-01151:.... SQL> recover database; Media recovery complete. SQL> alter database ...


0

Restart your database and that may clear up your issue. Any more relevant advice will require alert logs or trace files from the database.


2

There is a 3rd option, using window functions, either max(): with latest as (select *, max(inserted_day) over (partition by hostname) as max_inserted_day from age_data ) select * from latest l where inserted_day = max_inserted_day ; or rank(): with latest as (select *, rank() over (partition by hostname order ...


3

You can't restrict by IP address. All you can really do is create a new user and only grant them SELECT on that one table. create user newuser identified by 'p4ssword'; grant connect, resource to newuser; grant select on yourtable to newuser; It is actually possible to check the IP address a user is logging in from using a login trigger, and deny the ...


0

This Oracle document describes how to do a Windows->Linux migration. You'll need to follow the same approach, but swap the source and target architectures. Obviously you'll have to create the Oracle DB service on Windows too.


-1

You can read the files with a hex editor, but you can't restore Oracle data files backed up from a Linux server to a Windows server. The endian is different. Depending on how your database is setup you can use data pump with transportable table spaces to migrate an Oracle database from Linux to Windows. Transporting Tablespaces Between Databases


0

RMAN can be used for cross-platform tasks but you cannot restore your classic full backup from Linux to Windows


3

This is a bug, fixed in the July 2014 CPU - See Oracle Support document 1666884.1. Easily reproduced with the following: create view v1 as ( select 'view' as vvv from dual ); create view v2 as ( select * from v1 ); with v1 as ( select 'cte' as vvv from dual) select * from v2;


0

You could use what your query returns to get the extra row that have CONNECT_BY_ISCYCLE=1 WITH a AS ( SELECT EMP_ID, FIRST_NAME, MANAGER_EMP_ID, CONNECT_BY_ISCYCLE FROM MYTABLE CONNECT BY NOCYCLE PRIOR MANAGER_EMP_ID = EMP_ID START WITH EMP_ID = '12345' ) SELECT EMP_ID, FIRST_NAME FROM a UNION ALL SELECT m.EMP_ID, m.FIRST_NAME FROM MYTABLE ...


2

Your query doesn't ask for the first ten rows. For that you need to write something like: SELECT salute, gender, fid FROM fisgen WHERE ROWNUM <= 10; The hint first_rows(10) is only needed to instruct the optimizer that it should optimize for retrieving the first 10 rows as fast as possible rather than optimizing for whatever percentage of the table the ...


0

How did you stage your duplicate area? Are you duplicating to the same host? All involved files must be accessible by both target and auxiliary instances. Please check out the docs and see if there is any step missing: http://docs.oracle.com/cd/B28359_01/backup.111/b28270/rcmdupdb.htm#BRADV010 Also verify wether you can "ls -l ...


0

You can just join the table on itself using a table alias; This does the trick: Select u1.* from users u1 inner join users u2 on u1.weight = u2.weight and u1.height = u2.height where u2.name = 'x'


0

Try this Select * from your_table where height = '6,2' and weight = 190; or Select * from your_table where height = (select height from your_table where person = x) and weight = (select weight from your table where person = x);


0

The score can only be accessed with a CONTENT index using a query like this SELECT ID, TEXT,SCORE(1) FROM t WHERE CONTAINS(text, 'my query',1) > 0 ORDER BY SCORE(99) DESC; By Oracle 11 everything you can do with a CTXCAT index you can do more with a CONTENT index. Why not use that? Edit: The op asks if CONTENT indexes must be synced. Yes, this is ...


3

Not an Oracle/SQL*Plus problem. The default size of commandline window screen buffer is 300 (lines), and the maximum is 9999 (lines), so it is unable to display 157998 rows. Better spool the output to a file: SQL> spool output.txt SQL> select ... SQL> spool off After that you can view the result in the file output.txt


1

Try $ sqlplus / as sysdba | tee sqlplus.log


0

I want to propose my findings as an answer. I have tested this procedures and they seem to work fine. NOTE: The whole experiment was done on Windows Server 2012 R2, however based on my readings I would say most of it applies to Linux too. Shutdown Shutdown the Oracle Database Instance to ensure that everything close cleanly ...


-1

It seems that once the shutdown immediate fails, the connection is dropped, although it doesn't show it. Exiting sqlplus, reconnecting and immediately issuing shutdown abort got things unstuck for me. From https://community.oracle.com/thread/459659


0

Based on Phil's test case I backed up the the production database using exp. I dropped the editioned user, recreated them and imported the data with no issues. The user had views and packages which were versioned but as long as you set the edition to the most current one you are good to go. This query showed that the recreated user was not using ...


0

I know it's sometime after the original posting, but this was the first spot I found after Googling for an answer to the same problem and thought someone else who landed here might be happy to find a succinct answer that doesn't rely on overly complicated queries or regexes. This will give you the desired result: with nums as ( select distinct num2 ...


2

When the O7_DICTIONARY_ACCESSIBILITY parameter is set to false, users with SELECT ANY TABLE privilege can not access tables such as SYS.USER$, unless they have direct privileges to them.


0

It registers them automatically. Very easy to test... Listener status shows no PDB: $ lsnrctl status LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 13-MAR-2015 09:41:15 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521))) STATUS of the LISTENER ...


2

Oracle Golden Gate has always supported replication between Oracle and SQL Server. For a project I recently confirmed that Golden Gate for Oracle 10 is still available if you push a bit. If you are using an Oracle version that is out of support then you need to update for a number of reasons. Synchronization with Azure is detailed here.


1

Most likely ,(SELECT NVL (I.sap_xref_no, part_doc_nbr) FROM eissap.tbitem i WHERE i.item IN (SELECT comp_part FROM eissap.tbpart_config_comp_config WHERE item = comp_part)) AS sap_xref_no, returns more than one row (exactly as error says). When subquery is used in SELECT clause of ...


0

After more than 6 hours to fix this, I found this post which help me to understand the problem. The problem was that I tried to install Oracle 11g XE with my user account connected on the enterprise domain. The installation process, then, wasn't able to connect to the database. I suspect that it try to connect to it from the enterprise domain, and not from ...


0

I think you want an output parameter of the type sys_refcursor. Currently your cursor is assigned to a local variable, which gets destroyed when the procedure terminates. CREATE OR REPLACE PROCEDURE FEEDERS_TO_MAKERS_MCB( OpNo CTS_OPERATIONS.OPERATIONNUM%Type ,BName CTS_OPERATIONS.BLENDNAME%Type ,p_recordset OUT SYS_REFCURSOR ) AS BEGIN ...



Top 50 recent answers are included