New answers tagged

0

If you want to create database without dbca, there is simple way to generate a shell script that is foolproof and you can modify your required information e.g datafiles location, sid names and all. Try use dbca to generate script, it will be plan shell and sql script. It will not be having code used by dbca. you can edit this script and run on your oracle ...


0

The below query list all the datafiles and you can see the datafiles related to pdb with pdb name in their path. Select name from v$datafile


0

You could do both a union of the tables and an intersection of the tables and then get the difference between these. create table table1 as (select 1 id, 'Aron' name, 23 age from dual); insert into table1 values (2,'Bary',30); create table table2 as (select 1 id, 'Aron' name, 21 age from dual); insert into table2 values (2,'Bary',30); ( select * from ...


0

I've never used it, but http://blog.gitora.com/ is another option.


2

I'm sure this has already been asked before, but .. from the Oracle documentation Further down the page, when you look for what each constraint state is: VALIDATE | NOVALIDATE The behavior of VALIDATE and NOVALIDATE always depends on whether the constraint is enabled or disabled, either explicitly or by default. Therefore they are described in the ...


7

For single node Oracle RDBMS installations, an SCN is never duplicated. With older versions of Oracle RAC, Oracle 9i for example, it was possible that two transactions, running on separate RAC nodes, could end up with the same SCN. Duplicates happened only in rare situations where high transaction rates on the RAC nodes was coupled with a large value for ...


1

It only allows access to data dictionary views, so the only security implication is that the users(s) would be able to see which objects existed in the other schemas. They would not, however, be able to actually view the data in the other schemas. I said there was only one security implication, but another is that they would be able to view the source of ...


0

$ sqlplus / as sysdba SQL> startup force mount exclusive restrict SQL> drop database;


0

We use SDO_RELATE function which is very fast against SDO_GEOM_RELATE. But SDO_REALTE has no parameter as buffer. We use SDO_GEOM.SDO_BUFFER for giving 2cm buffer to the target object. Now it takes one hour for intersection all records with each other. Here is working code; BEGIN for curs in (select * from ORAHAN t) loop for curs2 in (select * ...


1

JOIN is not that effective, because you need to access the table 4 times. With PIVOT you can do it with 1 table access. create table list_of_stuff ( list_id number(5,0), item varchar2(40 char) ); insert all into list_of_stuff values (47, 'Baseball') into list_of_stuff values (47, 'Hat') into list_of_stuff values (47, 'Gloves') into ...


0

If this were me, I'd import the data into staging tables in SQL, then run comparisons and update what's needed. As far as physically connecting to Oracle, you should be able to use the OLE Provider for Oracle in SSIS.


0

Yes, you need to catalog the backups at their new location. You should run crosscheck also on the backups, so the entries pointing to the old location become EXPIRED.


0

This is always a tricky one! Luckily, there's a solution! I had this problem before and solved it using a "shadow" or "search" column. Here in Ireland, it's very common to have names with many different variations depending on accent or capitalisation. Indeed, the same person can have different spellings for various reasons. What we did was to have a "...


1

Martin Smith addressed a possible solution if you don't need to union before you minus (which I'm not sure will work for what you intend).. If you need to UNION first before you MINUS, while still preserving duplicate rows, then I think you'll have to do something along these lines: SELECT * FROM ( SELECT 1 as col1 FROM DUAL UNION ALL SELECT 1 as ...


5

In Oracle all set operators currently have equal precedence and are evaluated from top to bottom. SELECT 1 FROM DUAL UNION ALL SELECT 1 FROM DUAL; Returns two rows with both columns containing 1. But minus operates similarly to union (as opposed to union all) and removes duplicates so distinct-ifying the result. One way of getting the result you ...


1

I think this will give you desired resultset : select id,grp,itdesc, MAX(CASE WHEN su=1 THEN DEN END) AS DEN, MAX(CASE WHEN su=1 THEN NUM END) AS NUM, MAX(CASE WHEN su=1 THEN SU END) AS SU , -- or just "1 AS SU" MAX(CASE WHEN cs=1 THEN NUM END) AS CS, MAX(CASE WHEN sw=1 THEN NUM WHEN sw=0 THEN 0 END) AS SW FROM tab GROUP BY id,grp,itdesc


0

As already said, this is really easy to script e.g using sqlplus + shell scripts. Since you mentioned Enterprise Manager. Yes, it is possible through EM, but you need to set the preferred credentials for the databases and hosts. Once you have done that, you can execute an SQL on databases, or a group of databases. For example, I created a group called ...


0

linked server is Oracle, the first thing that I tried was to check if the Server hosting the SQL Server instance, was able to connect to the listener on the Oracle Database Server using tnsping. tnsping searched to the path C:\product\11.1.0\db_1\bin initially, this will initially use the used parameter file C:\product\11.1.0\db_1\network\admin\sqlnet.ora ...


0

Please take a look at http://www.orafaq.com/wiki/NESTED_TABLE. When you include the nested table it is like a join, so if you want a single row per customer, you will need to do some sort of unpivot (note that there could be more than two phone numbers in the nested table — Oracle needs to know how many columns to return. This will return all the rows: ...


0

Thanks to @ Justin Cave I got the answer. The problem was that another table (Department) was referencing the Store table as well. so I added in the trigger body: DELETE FROM Department WHERE Department.storeCode = :oldRow.stCode;


2

There is no 100% reliable way to map rows to transactions, unless you add custom columns. Using ORA_ROWSCN, even with ROWDEPENDENCIES enabled, is not always accurate. Jack's test is correct, and shows that a single transaction will save all rows with the same SCN. However, it is also possible for a different transaction to create a row with the same SCN. ...


2

You can use the Multitenant option, create a container database, create 2 pluggable databases called db1 and db2, then import the first dump file to db1, the other dump file to db2. You can switch between pluggable databases with as: alter session set container=db1; alter session set container=db2 But this requires at least version 12c, and it is an ...


0

This error is produced for various reasons stated below: Too high activity - Re-running the job during a less busy period can fix this ORA-00060 deadlock error. Poor application design - As noted in the trace file: "The following deadlock is not an ORACLE error. It is a deadlock due to user error in the design of an application or from issuing ...


-1

My first guess is, have you take the database offline yet? I believe you need to do that before making file changes.


-2

Datapump command like impdp can be used for importing the 10g DB contents into the 11g DB. Update: The above datapump command is an example of logical backup which can be done when a database is in running mode. Below are the common steps followed during a logical backup and restore: Export(Backup) of full DB is taken in old server: expdp system/...


0

Sure you can try, here is my quick test (on 11.2.0.4): SQL> select pool, bytes from v$sgastat where name = 'free memory'; POOL BYTES ------------ ---------- shared pool 503941528 large pool 6422528 SQL> drop diskgroup o6cb_fra including contents; Diskgroup dropped. SQL> select pool, bytes from v$sgastat where name = 'free ...


0

15018, 00000, "diskgroup cannot be created" Cause: The specified diskgroup could not be created. Action: Check for additional errors reported. 04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")" Cause: More shared memory is needed than was allocated in the shared pool or Streams pool. ...


-2

I just pressed left and then enter. It worked for me. This is the fix that helped me but I believe it should be fixed permanently.


1

I do not believe it is possible to use the LIKE wildcard in an IN statement. To achieve what you want to do, you need to take out all your LIKE wildcards and put them in a separate OR clause. WHERE string_col IN ('string1', 'string2') OR string_col LIKE '%string3%' OR string_col LIKE '%string4%' But this seems like a REALLY bad idea ... I think you ...


2

Apparently Oracle requires that an aggregate function stand alone (as opposed to being part of an expression) when there is no GROUP BY. So, since each operand in your case is the result of an aggregate function, both aggregations should be done in subqueries, as suggested by Balasz Papp. Or you can add GROUP BY () to your initial query: SELECT COUNT(T1....


3

Aggregate functions, but not on the same object, in a subquery, with addition. Use this: SELECT (SELECT COUNT(T1.FIELD) FROM T1) + (SELECT COUNT(T2.FIELD) FROM T2) FROM DUAL;


2

A default value is not a constraint in Oracle. You simply alter the column as: SQL> create table tab1 (col1 number, col2 number); Table created. SQL> alter table tab1 modify (col2 default 1); Table altered. SQL> select * from user_constraints; no rows selected SQL> insert into tab1 (col1) values (0); 1 row created. SQL> select * from ...


1

First, I assume that the fact that you're using join_date with an underscore in your code while the type uses joindate is a typo. Otherwise, you'd get a compilation error. You want to calculate the difference between join_date and the current date. My guess is that you want to use months_between to calculate the number of months between the two dates and ...


0

You can't use utl_file, since it has a maximum line length of 32767. It's possible that sqlplus has a similar limit. If you are tying to move data to a different Oracle database you can use data pump. You can eve use datapump on a materialized view. If you want to migrate to a non Oracle database you can use Perl or heterogeneous services to create a ...


1

As I already said, ORA-03113 is a generic error. When that error occurs, the server process serving your session died unexpectedly because of some other error in the background. These errors are displayed in the database alert log. In your case: ORA-07445: [kkqtutlSetViewCols()+250] [ACCESS_VIOLATION] [ADDR:0x8] [PC:0x3DC175C] [UNABLE_TO_READ] [] You ...


2

Listener is a process running in the Oracle Server machine. The responsibility of the listener is to listen for the incoming database connection request and spawning server process to handle the request(in Dedicated Server mode) or handing over to dispatcher process(in Shared-Server mode). In RAC architecture we have SCAN listener to redirect connection ...


0

Your code page is 936 (Chinese). When you start SQL"Plus then this code page is inherited from CMD. Usually you have two possibilities: Set NLS_LANG according to your code page (either as Environment Variable or in Registry). Example set NLS_LANG=.WE8MSWIN1252 for a PC in Central Europe, the language and territory information do not matter for character ...


0

I want to share another alternative: Create a new column having the data of all columns used to find: ALTER TABLE employees ADD criterio varchar2(100); update employees set criterio = nickname || ' ' || name || ' ' || lastname; Create the index using the new column Oracle Text: create index employees_criterio on employees(criterio) indextype is ctxsys....


4

You can find this information in DBA_HIST_RESOURCE_LIMIT. Using this view requires the Diagnostic Pack option. For example, sessions in the last 1 day: select s.instance_number, s.end_interval_time, rn.current_utilization from dba_hist_snapshot s join dba_hist_resource_limit rn on (s.snap_id = rn.snap_id and s.instance_number = rn.instance_number)...


3

You can find this in v$session: select count(*) from v$session; It shows you how many sessions are there at this very moment. If you need it for statistical/historical reasons then you need to create a procedure, using this SQL, that runs every x-time and store the result in a table. You can also install the Oracle Diagnostics Pack. This has the Active ...


0

By relying on ResultSet's ability to load limited data in memory we are able to come up with a solution in Java using Dynamic Comparator. Solution is to take first record from each resultSet and sort it in java and return first element from sorted data. Detailed Solution: First we built a program which can give us a dymanic Comparator based on the criteria ...


0

CTAS (Create Table AS) will work as long as you don't need triggers or indexes or constraints. EXPDP/IMPDP with REMAP_SCHEMA will bring along all of these other objects. Use CONTENT=METADATA_ONLY if you don't want to include the data.


3

One method is to use the MULTI_COLUMN_DATASTORE. Below is an example: drop table t1 purge; create table t1 (column1 varchar2(100), column2 varchar2(100)); insert into t1 values ('HELLO', 'WORLD'); insert into t1 values ('SAY', 'HELLO'); insert into t1 values ('SOMETHING', 'ELSE'); insert into t1 values ('WORLD', 'CUP'); commit; Create the datastore with ...


0

I recently stumble upon another tool capable of this migration: dbMigration .NET. Here are some databases supported: Oracle Postgresql SQlite SQL Server ... (see complete list on the site) It can be installed with chocolatey.


1

If you are trying generate SQL to create empty tables for all tables then the SQL you have written might be okay. There is couple of errors which I have corrected as: select 'CREATE TABLE INTDTA.' ||table_name|| ' AS SELECT * FROM ZRTDTA.' || table_name || ' where 1=2;' from all_tables where owner='ZRTDTA'; If you want to use single quotation mark(') ...


2

Hi and welcome to the forum. This issue is often resolved by certain patches. Check out: https://community.oracle.com/thread/2564826?start=0&tstart=0 Ensure you're fully patched and up to date on both sides.


2

The default password for the HR, sys and system accounts is oracle. The Unix password for the oracle user is also oracle.


0

Most likely you corrupted your database and you should consider to make a full restore from backup. ALTER DATABASE CHARACTER SET ... was supported till Oracle version 9 and worked only at certain conditions. Never use clause INTERNAL_USE or make any updates on Oracle system tables unless you are advised by Oracle Support. If you like to change the ...


4

there's a space missing before to in 'to myuser'. BEGIN FOR X in (select * from all_tables where owner in ('TESTDTA', 'TESTCTL')) LOOP EXECUTE IMMEDIATE 'GRANT SELECT, INSERT, UPDATE, DELETE ON ' ||X.owner||'.'||X.table_name|| ' to TESTUSER'; END LOOP; END; /


1

I'd start investigation with the following: Check physical servers parameters like CPU, physical memory, hard drive speed, network , etc, and make sure they are comparable (ideally identical) . Check Oracle memory settings (memory_target) on both servers Make sure dbms_stats procedures were executed on new server after copying database (at least ...



Top 50 recent answers are included