Tag Info

Hot answers tagged

5

If all else fails read the docco. Try here for starters (v. good diagram). In Oracle, a schema is a database. Also see here - particularly this "One characteristic of an RDBMS is the independence of physical data storage from logical data structures. In Oracle Database, a database schema is a collection of logical data structures, or schema objects [i.e. ...


5

You simply don't use begin in sqlplus if you're just going to issue a series of SQL queries. You're in a transaction already as soon as you issue some SQL. You can't really be outside of a transaction anyway for practical purposes (sure, if you've just logged in, or just committed and haven't started anything else, well, you're not in a transaction). A few ...


5

My strong advice (regardless of the RDBMS): store the files separately. This makes a much smaller database, important when you backup, migrate, replicate etc. Additionally, you can separate db and files on different disks, having so much more control over storage locations (e.g. disk subsystems with different fail-over strategies). This means however, you ...


5

Yes, that's fine: All software downloads are free, and most come with a Developer License that allows you to use full versions of the products at no charge while developing and prototyping your applications, or for strictly self-educational purposes. The full Developer License can also be found on OTN, including: Oracle grants You a nonexclusive, ...


5

A Fast Method A probably faster way to do this is the following: Provide the data in a database table Execute a MERGE statement that merges the data from this table into the source table To provide the data in a database table you can use sql loader or [external tables]. http://docs.oracle.com/database/121/SQLRF/statements_9016.htm#SQLRF01606 create ...


4

Unfortunately the answers are as follows: Is there a unit for cost in an Oracle execution plan? Not really. I mean if the cost of an operation is 50 then can I map this number to CPU cycles or utilisation percentage? Nope. What does this number stand for? It's defined like this: (see the glossary) A numeric internal measure that ...


3

The correct answer for licensing questions is: contact your oracle sales representative. With Oracle you license cpu's. If your test and prod run on the same cpu's, they don't need a new license. As long as it all runs on the same cpu's, it does not matter if you have multiple databases (and instances) or run it all in one single database. Normally we don't ...


3

You're issuing a "recover database" command, which will recover the database as far as possible. If that includes the archivelogs/redo logs which contain your "truncate" command, then this will also be re-played and the table truncated again. Try doing the restore on it's own, or recovering to a specific time using RECOVER UNTIL... then opening the DB ...


3

Use this on Windows (three nested double quotes): exp system/password_for_system@dev file=c:\fooadmin.dmp full=yes imp """sys/password12345@dev as sysdba""" FROMUSER=FOOADMIN TOUSER=FOOADMIN file=c:\fooadmin.dmp In general imp would work as intended with sys account, it is just not recommended. But exp with sys account could possibly produce inconsistent ...


3

Following query gives details of all locks. SELECT B.Owner, B.Object_Name, A.Oracle_Username, A.OS_User_Name FROM V$Locked_Object A, All_Objects B WHERE A.Object_ID = B.Object_ID


3

Short answer: CREATE TABLE must be explictly granted to the object owner for DBMS_DATAPUMP to be called from within a package or stored procedure. Long Answer: In an effort to solve this question, I interrogated the data dictionary to get all of the object and system privileges granted by the IMP_FULL_DATABASE and EXP_FULL_DATABASE roles (and all roles ...


3

I found a solution on my own! It seems that there is a bug in the Oracle installer - it doesn't like it if you start your system password with a number. I completely uninstalled Oracle using the deinstall utility and then reinstalled Oracle, this time specifying a system password beginning with a letter. This solved the problem! I hope this helps ...


3

On your Windows client, you need to specify the TNS alias @ORCL, or else it assumes you're trying to connect to a local database running on your Windows client, which there isn't one. Also, system as sysdba generally isn't used, not even sure if that makes sense. SYS as sysdba and just plain system are used. Enter user-name: system@ORCL Enter password: ...


3

The documentation clearly describes the precedence for NLS settings: http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch3globenv.htm#NLSPG188 1 (highest) Explicitly set in SQL functions 2 Set by an ALTER SESSION statement 3 Set as an environment variable 4 Specified in the initialization parameter file 5 Default ...


3

Regular trigger will see changes made in other tables within the same transaction. However, if you create the trigger (or other programmable object) with PRAGMA AUTONOMOUS_TRANSACTION, it will be executed within the scope of new transaction and won't be able to see "parent" transaction changes.


3

Whether the data can be manipulated by a user depends on the rights he has. Rights to database objects like tables and views are given either directly to a user or via a role. Roles have the advantage that you can assign them to new users easily without having to deal with the details. I would suggest at least two roles: An admin role and a user role. ...


3

Maybe those files are not cataloged by RMAN? I would CATALOG them and then DELETE OBSOLETE once again.


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 ...


3

Yes, it is possible. https://docs.oracle.com/database/121/NTDBI/intro.htm#NTDBI2648 Oracle Database supports multiple Oracle homes. You can install this release or previous releases of the software more than once on the same system, in different Oracle home directories. This allows flexibility in deployment and maintenance of the database ...


2

Got a workaround from Oracle: /*+ opt_param('_simple_view_merging', 'false') */ or alter session set "_simple_view_merging"=false; So the following query works: SELECT COUNT(patientId) n FROM ( SELECT /*+ opt_param('_simple_view_merging', 'false') */ DISTINCT pat.patientId, pat.bval1, pat.bval2 FROM pat LEFT OUTER JOIN (SELECT /*+ CARDINALITY(tab ...


2

You seem to mixup the terminology for database and schema in an Oracle database. In Oracle up to 11g, a database is just a collection of files. Those files are logfiles, controlfiles, datafiles and tempfiles. Tablespaces are created using datafiles and you create tables and indexes inside a tablespace. If you want to create tables, or other schema objects, ...


2

Generate the statements you need select 'DELETE FROM '||table_name||' ;' from user_tables;


2

Why using original Export and Import utilities (imp and exp respectively), which are deprecated, while since 10g Oracle provides a new, more efficient facility called Data Pump? You can use Data Pump Import with REMAP_TABLESPACE parameter to specify the new tablespace name for the objects being imported: $ impdp hr REMAP_TABLESPACE=tbs_1:tbs_6 ...


2

It means that possibly some other DBA issued the command below (alter system with optional comment clause). Then the comment is put into spfile this way. But as I read metalink note: 257643.1 it might some outcome of some changes in automatic memory management. See alter system command: alter system set sga_max_size=2G scope=spfile sid=* ...


2

Try something like: select AccountNumber, SnapshotTime, Ticket, ... from ( select AccountNumber, SnapshotTime, Ticket, ... , row_number() over (partition by AccountNumber, Ticket order by SnapshotTime desc) as rn from OpenTrades ) T where rn = 1; I just reread the post and realized that I gave a solution for ...


2

If someone is INSERT/DELETE/UPDATE on a table at the same moment your expdp is working on that table, it won't affect that object. You will get a consistent snap of the table at the moment that expdp got to that table. Basically expdp is consistent WITHIN a table, but not BETWEEN/AMONG tables, unless ... Since expdp processes your tables serially, they ...


2

Views starting with VW_DTP showing up in the execution plan is the result of one of the cost-based query transformations available since 11.2, called distinct placement. It happens with statements using the DISTINCT clause, just like yours. It is an absolutely acceptable an quite obvious execution plan to join the tables and filter the rows, and after that, ...


2

It sounds like you're dumping 30 million rows into an existing table, then immediately trying to update those rows. I have a couple of thoughts, which may or may not work for you (since I can't see your code currently): 1) Is there a way to combine the inserts with the updates? It seems suspect to me that you're creating 30 million rows, then immediately ...


2

You're missing a RETURN statement in your PL/SQL. Check out the answer here. There are any number of sites which can provide a quick response to this query. I recommend that you download SQLDeveloper and look at some of the system supplied stuff and check out the docco, starting here. SQLDeveloper is really great - and it's free!


2

3<4, I opt for option 2 approach. This is also what Oracle instructs us to do in for example this document: http://docs.oracle.com/cd/E11882_01/server.112/e23633/upgrade.htm#UPGRD12394 After you have installed the software for Oracle Database 11g Release 2 (11.2) and any required patches, Oracle recommends that you analyze your database before ...



Only top voted, non community-wiki answers of a minimum length are eligible