Hot answers tagged oracle
8
Good question. I'll give a simplified answer.
Oracle supports two character sets simultaneously, by way of different datatypes and parameters. A "normal" database-wide characterset and a "national" characterset.
Now, the "normal" characterset affects the way that VARCHAR2, CHAR and CLOB data is stored. The "national" characterset affects the way that ...
7
I see two problems with your date filter:
You're applying TO_DATE on a DATE datatype. It is not only superfluous, but it actually causes unexpected behaviour.
Here's how Oracle analyses the expression TO_DATE(data_inicio,'yyyy/mm/dd'):
Oracle searches for a function named TO_DATE that has two arguments (DATE, VARCHAR2)
Since no such function exists, ...
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 ...
5
Use expdp and impdp, in combination with the remap_schema option.
For example...
Firstly, make sure the target user/schema is already created. It may also temporarily need the IMP_FULL_DATABASE role, but I haven't tested this.
Create a directory object to hold the dump files and logs (make sure this exists on your filesystem):
create directory DUMPDIR as ...
5
Oracle is compiling all objects, so as far as Oracle is concerned all code in all objects is new code.
As a consequence, Oracle has no way of knowing that your function Str2Number still returns the same values that were used when the index was created.
Instead you should only recompile invalid objects,
exec dbms_utility.compile_schema(user, false);
will ...
5
You've missed one place to get an overview of Oracle: the Concepts Guide. It covers all the major topics (including backup and recovery, which is quite important and doesn't appear in the list of links you've posted).
Whats the next step? Create the Schema or Tablespace?
Both! They're orthogonal. Users are logical entities that access your database. ...
5
Since the data is stored in a TIMESTAMP column, you cannot automatically convert it. You'll have to write some code that tells Oracle what time zone to treat the data as being stored in the GMT time zone and tell it that you want it converted to the session's time zone. You would get the automatic behavior you are looking for if you used the TIMESTAMP WITH ...
5
The only reason why I might do that is if I needed to address an object that might not exist at compile time -- for example if I had code to create new external tables as required.
As this implies, the dynamic SQL statement is not parsed when the PL/SQL compiles, so you have no idea whether it is correct or not, and dependencies are not stored in the ...
5
This is not my forte, but I am sure that you need a GROUP BY in there.
Also, out the ORDER BY line at the end of your statement. Something like this :
SELECT
I.CARD_BIN BIN
FROM
GE_ICA_BIN I
LEFT OUTER JOIN GE_PAYEE_VALIDATION P ON I.CARD_BIN = P.CARD_BIN
LEFT OUTER JOIN GE_PAYEE PP ON PP.PAYEE_CODE = P.PAYEE_CODE
group by I.CARD_BIN
HAVING COUNT(*) ...
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 ...
4
If my source database uses SPFILE then do I have create a PFILE from the SPFILE?
Yes, you need to create a temporary PFILE to use while duplicating the database. You will later switch the new instance to use the SPFILE.
Use
CREATE PFILE = 'path/to/pfile' FROM SPFILE;
You only need to create directories that are referenced in the PFILE or SPFILE.
...
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 ...
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 ...
3
Asuming this is to transport data to an other system. It that case this will work:
set colsep ";"
set linesize 9999
set trimspool on
set heading off
set pagesize 0
set wrap off
set feedback off
set newpage 0
set arraysize 5000
spool you csv_file.csv
select rows from your tables;
spool off
If you don't want a header line, change to heading off
If this is ...
3
For simple SQL statements, such as the one you're describing, there is indeed an overall hit on performance in an Oracle DB. Since the DBMS must soft parse the sql each time (which also takes up a little bit of time), it then needs to write to SGA and might end up taking away resources from already parsed execution plans from static queries, since the SGA is ...
3
11g has the pivot construct, which appears to meet your needs:
with pivot_data as (
select buildnumber, testname, status
from testresults)
select * from pivot_data
pivot (min(status)
for buildnumber
in ('1' as build_1, '2' as build_2, '3' as build_3)
)
order by 1;
Note the min(status), the pivot requires an aggregate function, ...
3
Here are some issues to consider:
Are you using an appropriate level of estimation in the statistics gathering? People often manually set the estimation to a value without considering whether they can get a similar level of accuracy with a lower amount. A good test is to gather statistics at different estimation percents, starting very low and increasing: ...
3
select cid,
pdate,
itemcode,
itemname
from (
select sales.*,
rank() over (partition by cid order by pdate) as rnk
from sales
) t
where rnk = 1
order by cid;
Alternatively
select s1.cid,
s1.pdate,
s1.itemcode,
s1.itemname
from sales s1
where pdate = (select min(s2.pdate)
from sales s2
...
3
Oracle will provide a read-consistent view of the data. Assuming that you're using the default transaction isolation level of read committed, the data that a query will return is fixed at the point in time that the cursor is opened (whether the cursor is implicit or explicit is irrelevant).
The only exception to this is that PL/SQL that is called by the ...
3
You have a few options, some of which will make your database unsupportable by Oracle (if you care!) as you need to fiddle with SYS owned objects.
The first option is to revoke select privs from everyone:
revoke select on all_users from public;
Then grant it back to certain users, plus all other internal Oracle users.
The 2nd option is to look at the ...
2
The whole point behind partitioning in Oracle is that it is transparent to the queries. And if your query only needs data from one partition, then Oracle will (if it can) only read that single partition.
So if you have a table partitioned on, for example, year so that each year's data is stored in a separate partition, a query such as:
select ...
2
Seems like you have media corruption. I would consult Automatic Diagnostic Repository (ADR) contents if there are open failures in your database. You can do that via Enterprise Manager or using RMAN command-line utility:
[oracle@oca ~]$ rman target=/
RMAN> list faliure;
If there are failures with status OPEN listed, you can ask Data Recovery Advisor ...
2
Consider a parent/child table such as client/order.
You can't delete a client that has an order. Say client 123 has an order A123. Fred does a delete for that order but does not commit. Then "Jane" tries to delete client 123.
Since Fred's statement can potentially rollback, the client can't be deleted because it isn't allowed to the leave the order ...
2
Delete is a DML command and stores the data in redo log till the delete operation is committed.
This means that if data to be removed by delete is slightly large[even though search time is less] it will take longer time as it will move data to redo log.
So may be the instance when your operation took longer large no. of rows were being deleted to many ...
2
Depends how much data you need on the second database. Solutions are not to save query results on a second DB but duplicate data for querying on another DB. Basically the following techonologies:
Oracle Streams: it is a replication from one database to another one. Can be done by table or by schema.
Oracle Active Dataguard: it is basically a standby DB ...
2
According the ANSI, DB2, and SQL/DS Data Types:
SQL statements that create tables and clusters can also use ANSI data types and data types from the IBM products SQL/DS and DB2. Oracle recognizes the ANSI or IBM data type name that differs from the Oracle Database data type name. It converts the data type to the equivalent Oracle data type, records the ...
2
You need to change port 1158 to 1521 in your connection properties.
Oracle's listener for database connections runs on port 1521 by default.
Port 1158 is the web listener for Database Control, a webgui for managing the database.
In addition, the servicename should be something like "database.domain.com", or you can try using SID and specifying ORCL.
2
Normally it should happen quickly.
There could be large transactions that take a while to rollback, or a long running transaction that takes ages to end.
If the time is spent for the rollback, you could crash the instance with a good and solid kill -9 on the server procs and use parallel instance recovery. Normally this should not happen but parallel ...
2
It might be that your account has been assigned the profile with PASSWORD_LIFE_TIME set to UNLIMITED, and thus your password will never expire. You can query the USER_PASSWORD_LIMITS view to determine what password limits are currently in effect for your account:
SQL> select * from user_password_limits;
RESOURCE_NAME LIMIT
...
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.
Only top voted, non community-wiki answers of a minimum length are eligible
