New answers tagged oracle
0
You could execute each script using a separate call to SQLPlus and have them run in the background.
If you just need to remove errant parameters you could search for them in the scripts and not run those scripts that contain un-supplied parameters.
0
You might use oracle datamodeler (it is free and multiplatform). You can run it againt the source database and design the current model. Then you can automatically extract DDL and run them against Oracle or SQLServer. It should works but due to differences across database you may have to manually review the DDLs.
0
Tom Kyte (of http://asktom.oracle.com fame) maintains links to several different methods of "unloading" a table (or query) to CSV.
That said, I've very confused about how you're able to use SQL*Developer, but not expdp; both are Oracle client-side applications.
0
Oracle object names are all uppercase by default. The linked-server query must exactly match the case of all referenced Oracle metadata, such as schema and table names.
1
You should use export/import. On your side do:
expdp system/systempawwdord SCHEMAS=YOURSCHEMA
This will produce a file. Take it manually to you customer using CD, DVD, Pendrive. On customer site do:
impdp system/systempassword SCHEMAS=YOURSCHEMA
If the customer is using a different Oracle version, this is not a problem. You probably need to manually ...
2
RMAN only works at the block level & has no idea about the contents of a given block, and therefore cannot do this.
You need to use expdp with the query parameter:
expdp phil/phil directory=myexportdir dumpfile=yourtable.dmp query=yourtable:\"where groupid in (1,2,3)\" tables=yourtable
Obviously, this isn't incremental. There's no really easy way of ...
0
Thank you all for your comments. You deserve the credit for this answer. To summarize:
The Oracle driver for LinqPad that I'm using (dotConnect Direct Mode based on OCI 8) wants the statement to be formatted like this (yes, wihout any semicolons):
UPDATE schedule_amounts
SET ANNUAL_RATE = 44000
WHERE SCHEDULE_ID = 'LCSD'
and SCHEDULE_NO = 2014
...
1
An Oracle database uses two basic kinds of disk files - .dbf (data) and .ctl (control) files. You can also have .idx files (for indices). In most environments I've been in, these are located in /u01, /u02, /u03, and on, for however many /u0X's your DBAs had created.
On the other hand, many Oracle installations don't use filesystem files, they use raw disk ...
1
The latest full client will work fine with 10g.
I have experienced similar problems using anything other than the full client, even though according to Oracle the others should work.
Just make sure that you completely uninstall all elements of the previous installs as having multiple clients/versions can create another set of problems.
1
Here's an example of how you can do this in shell script (bash or ksh for instance). Here's simple SQL script to test, it turns off (most?) of the usually undesired output from SQL*Plus:
set feedback off
set echo off
set heading off
set pages 0
whenever sqlerror exit 4
whenever oserror exit 8
select status from v$instance;
exit
You can run it like this, ...
1
It's a bit scary that you're working on a RAC database but are getting confused between databases and instances.
The database is the set of files that store the data plus the supporting configuration files, and the instance is the set of memory allocations and processes that access them. A RAC system is distinguished by having multiple instances accessing a ...
1
The best source for Oracle internals is generally the Oracle Concepts Guide.
In this case they have a pretty good description, including how it works as part of transaction control.
http://docs.oracle.com/cd/E11882_01/server.112/e25789/transact.htm#CNCPT039
1
Log file sync occurs when a commit is made and the redo buffer needs to be flushed to disk. The session has to wait for that to happen.
An increase in the number of log file syncs generally means that one of your developers has gone commit-happy, and is committing far too frequently -- every row, for example.
Here you probably have a process that performs ...
4
The short answer: you cannot add column to a table at a specific position.
When you add new columns they always go after the existing columns. You have to re-create your table with new definition to place a column to a position you need.
You can however create views with columns positioned arbitrarily based on your existing table, and then query or update ...
1
Edit your /etc/oratab file to read:
INFO:/u01/app/oracle/product/11.2.0/dbhome_1:Y
I guess it's just one of those things you need to know to look for!
0
You can do this directly from SQL Server Management Studio.
Within SSMS, just right click the database, select Tasks -> Import Data, select an Oracle typed data source and set up your connection to the Oracle database.
Here's a really good description of the full procedure, including setting up an Oracle client on the machine doing the migration.
...
1
According to the Glossary from the Oracle Documentation
System Change Number. A database ordering primitive. The value of an SCN is the logical point in time at which changes are made to a database.
There is lots of info you can easily Google
http://oracledbascriptsfromajith.blogspot.com/2009/05/understanding-scnsystem-change-number.html
...
0
If you need to run this as schema1, you could just write into your script to temporarily log in as schema2, use the USER_SOURCE solution, generate your script, log in as schema3, then run the script.
0
Answer from below thread solved my problem
https://forums.oracle.com/forums/thread.jspa?threadID=2208254
apparently '=' is not needed in responsefile argument in netca cmd
/u01/app/oracle/product/11.2.0/db_1/bin/netca /silent /responsefile /oracle_binaries/database/response/netca.rsp
5
Note: Deletes perform normal DML. That is, they take locks on rows, they generate redo (lots of it), and they require segments in the UNDO tablespace. Deletes clear records out of blocks carefully. If a mistake is made a rollback can be issued to restore the records prior to a commit. A delete does not relinquish segment space thus a table in which all ...
1
@haki has identified why this error occurs. You can't use an alias (KODE) defined in the SELECT list, in the definition of another column in that list. You have to use the full reference instead (ANGGARAN.SIMPEG_PEGAWAI.ID_PEGAWAI).
But the solution provided, does not work I think because of the double nesting. You can reference a column in a inline ...
0
you cant use alias from the wraping query in the a sub query
change it this way
SELECT
ANGGARAN.SIMPEG_PEGAWAI.ID_PEGAWAI AS KODE,
ANGGARAN.SIMPEG_PEGAWAI.NAMA,
ANGGARAN.SIMPEG_PEGAWAI.NIP,
ANGGARAN.SIMPEG_ESELON_JABATAN.JABATAN,
ANGGARAN.SIMPEG_KODE_GOLONGAN_PANGKAT.GOLONGAN,
ANGGARAN.SIMPEG_KODE_GOLONGAN_PANGKAT.PANGKAT,
(SELECT *
FROM ...
1
It looks like a new schema in the existing database is the best option. Forget about having more instances on one server, this does not give the optimal performance and utilization. One of the biggest advantages of having schema's for different applications in one database is that Oracle Resource Control can be used to control the workload. In the ideal ...
0
Try using these 3 options :
oracle.install.option=INSTALL_DB_SWONLY \
oracle.install.db.InstallEdition=EE \
oracle.install.db.isCustomInstall=true
1
You could use Oracle Data Pump Export and unload data compressing the resulting dump file(s). Below is an example of unloading the tables EMPLOYEES and DEPARTMENTS owned by the user HR:
expdp hr parfile=hrexp.par
The parameter file hrexp.par contains the following parameters:
content=data_only
compression=all
include=table:"IN ('EMPLOYEES', ...
1
When using one database with two schemas you also share SGA structures(like library cache) and also possibly TEMP tablespace and also backups. In rare cases one application can have negative impact on other's performance. Also if you are asked to revert one schema it will be much harder to "restore" just one schema. The same also applies to patching, it's ...
0
I discovered the solution myself. I found that apparently when I had installed Oracle Client, it had installed on my external drive E the first time and became Oracle Home #1 in the Windows registry. I don't believe it was "looking" at the Oracle client I had installed on the C drive even though it was first in the path. I uninstalled the Oracle clients on ...
2
No, you don't have to reinstall the Oracle software. According to the section "Oracle Database 11g Release 2 (11.2.0.1) New Features" in the Database Installation Guide 11g Release 2:
Oracle Universal Installer no longer provides the custom installation
option of individual components. Use the chopt tool, a command-line
utility that is located in ...
0
Use the UNPIVOT clause (11g):
SQL> SELECT d.*, e.table_column, e.table_data
2 FROM departments d
3 LEFT JOIN (SELECT *
4 FROM (SELECT to_char(employee_id) employee_id,
5 first_name, last_name, to_char(salary) salary,
6 e.department_id
7 ...
4
Toad cannot directly read Oracle data files, no. You would need to recover the database on a local server. Once you do that, you could connect to Oracle database on the local server from Toad and run whatever queries you'd like.
Assuming that what you've been given is a consistent cold backup of your database, you'd need, at a minimum, exactly the same ...
1
The safest and simplest way to add a tns entry to an oracle client is to use the wizard.
run netca on the command line and fill in the necessary fields step by step. if you need to add parameters edit what the wizard created.
2
There are various methods of achieving this ,one of them as suggested by Yasir.
Please follow the below steps:
Close toad/sqlplus
Create a folder TNS_ADMIN
Add tnsnames.ora in TNS_ADMIN
Add TNS_ADMIN in enviornment variable.
Open Toad/Sqlplus and login
I think this will be very clear ,cheer
1
Just put your Oracle Net files (tnsnames.ora, sqlnet.ora etc.) to $ORACLE_HOME/network/admin directory.
Local Naming Parameters (tnsnames.ora):
By default, the tnsnames.ora file is located in the
ORACLE_HOME/network/admin directory.
6
You can meet all your requirements with Standard Database Auditing or with Fine-Grained Auditing.
For the standard auditing to also capture the SQL statements, you would set AUDIT_TRAIL initialization parameter to DB,EXTENDED or XML,EXTENDED (See "Settings for the AUDIT_TRAIL Initialization Parameter" in Database Security Guide). The audit records would ...
0
I'm currently working on the same problem. I'm still in the process of doing this. What I can tell you, is, that not /usr/bin/sh fails but OMS can not establish the ssh session . Please check this in the log file. After I logged in with my AD-Account, the deployment started to work, but lastly failed because the AD-Account has not enough permissions to get ...
2
If you've no need to store the date component, then an interval day(0) to second is the most practical approach. The biggest advantage comes if you need to do any calculations finding how long has elapsed between two times as you don't need to do any extra processing out of the box.
For example:
create table times ( t1 interval day(0) to second, t2 ...
1
The other option is Backup-Based Database Duplication where you would use UNTIL clause to specify the SCN or timestamp until which you want to recover your duplicate database. You would need to configure and start the auxiliary instance, and then issue in RMAN the command similar to the following:
DUPLICATE DATABASE prod TO dupdb
UNTIL TIME ...
0
I did some testing using oracle 11g and got this:
SQL*Plus: Release 11.2.0.1.0 Production on Mon May 13 22:40:32 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Sessie: 44,8525
...
0
Try storing as number. Worst case is 3 bytes for HHMM, and 4 for HHMMSS. 01:00 would take 1 byte.
2
Oracle Data Pump Network-Mode Import may be one of possible solutions. You can invoke Data Pump Import in network mode using database link (NETWORK_LINK parameter) and specifying which version of data you want to import with FLASHBACK_SCN or FLASHBACK_TIME parameters.
First, create a database link to you production database on your development database:
...
0
Well, as it turns out I needed to use a synchronous process to assign an id to each record after the initial insert was complete.
As was pointed out to me, what I was really looking for was a commit sequence. Unfortunately even timestamp would not provide that because it is generated at insert time, not at commit time. So there could be situations where ...
1
Replace
select portfoliono into mportfoliono from sipholder where accno=:new.accno;
with
mportfoliono := new.portfoliono;
no need to use a SELECT for values of the updated row.
0
So after working through the info that Phil gave me.. and doing some research after the fact..
It appeared that the Set Until with a future date screwed things up by "removing, or de-cataloging my backups". Even if that's possible (I don't know enough about Oracle to say) it wasn't the case.
What had happened was that my initial Full Backups were gone due ...
2
You cannot create a foreign key that references an Oracle data dictionary table. So assuming the "users on that database" are Oracle database users that exist in dba_users, you can't reference dba_users in your constraint.
You can, however, create your own copy of the dba_users table and create a job (using dbms_scheduler or dbms_job or some external ...
0
ALTER USER <user> QUOTA 100M ON <user>;
GRANT UNLIMITED TABLESPACE TO <user>
0
WEST_EUROPEAN would seem to be the obvious choice, but there is no default that lets you avoid setting it on an individual language basis.
2
You'd need to add IS_SPECIFIED (or ISSPECIFIED) to the where clause, as some hidden parameters may be set by... other things.
A parameter can be removed from an spfile by issuing:
ALTER SYSTEM RESET "_some_hidden_parameter" scope = spfile;
You will likely have to stop and start the instance(s) to have the changes take effect.
2
Create a pfile from the spfile:
CREATE PFILE FROM SPFILE;
Edit the generated pfile and remove the parameters, then recreate the spfile from the edited pfile. Bounce the database & all should be well.
The database might need to be down when you recreate the spfile from the pfile.
1
@elimerv,
Are you sure you are not having something else in place of "single-quote" that is expected there? Also, you don't tell what kind of data does the "pm" table has in the pm_id column (BTW, your latest SQL doesn't appear to be correct as your sample_data subquery has an "id" column whereas your main query refers to "pm_id" column).
This is what I get ...
Top 50 recent answers are included
