Tag Info

Hot answers tagged

6

Well it does now - Oracle 12c introduced IDENTITY columns, see: Identity Columns in Oracle Database 12c Release 1 (12.1) e.g. CREATE TABLE identity_test_tab ( id NUMBER GENERATED ALWAYS AS IDENTITY, description VARCHAR2(30) );


6

Sargable (or sometimes sargeable). It's not really a word, it's made up of Search ARGument, and when a WHERE clause is sargable, that mean's it's possible for it to use an index. It doesn't mean it will use the index, and it doesn't mean it will seek, either. A lot of factors go into the optimizer's choice, and the rules can clearly differ between different ...


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


4

You could use a row-level security policy with a statement_type limited to update (or more likely update and delete, and maybe insert too). See the DMBS_RLS.ADD_POLICY docs for details. Dummy scenario: a list of tasks, only task owners can modify their task. create table owners(owner_id int primary key , owner_name varchar2(10)); create ...


4

There is only one REMAP_TABLESPACE parameter in de command. It would be seperated with ",". REMAP_TABLESPACE=OLD_SCHEMA_DAT:NEW_SCHEMA_DAT,OLD_SCHEMA_IDX:NEW_SCHEMA_IDX Oracle doc impdp 11.2 http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_import.htm#SUTIL929


3

The syntax for the alter tablespace statement only supports one tablespace at a time, you can't list more than one as you're trying to. If you're scripting that command already, simply have your script issue multiple alter tablespace statements rather than one. If you're generating your list of tablespaces from dba_tablespaces, you can dynamically alter ...


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

No, your case does not block on Oracle, nor should it. Concurrency is one the the major reasons to use an RDBMS instead of say, Excel. But just to make it interesting, if you insert the same value in both sessions, it will then block. Session #1 CREATE TABLE t1 (c1 NUMBER PRIMARY KEY); INSERT INTO t1 VALUES (1); -- do not commit transaction Session #2 ...


2

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


2

This can be achieved with the expdp CONTENT flag: expdp username/password DIRECTORY=directory_object_name \ DUMPFILE=dumpfile_name \ TABLES=table_names|TABLESPACES=tablespace_names|FULL=y \ CONTENT=metadata_only


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

As per the comment, perhaps something like this: CREATE OR REPLACE PROCEDURE MYDB.GET_DEAL_LIST ( PersonID NUMBER, TableName NVARCHAR2, BuyerOrSeller INTEGER, a_CURSOR OUT sys_refcursor ) AS v_stmt_str VARCHAR2(200); BEGIN if BuyerOrSeller = 1 then v_stmt_str := 'SELECT TBLDEALS.*,t.* FROM TBLDEALS inner JOIN ' || tablename || ...


2

It can make sense to insert data in order. There's lots of caveats to this though. If the data isn't frequently updated and if you're using certain types of tables or indexes (e.g. IOT, clustered indexes). Being in order means that if you're doing a range scan of the ordered column (e.g. BETWEEN x AND Y) then the data is more likely to be in a ...


2

Have you considered Oracle's BFILE ? Sounds like it's a perfect fit for you; http://www.orafaq.com/wiki/BFILE Here's some further reading material on the subject ; http://docs.oracle.com/cd/B10501_01/appdev.920/a96591/adl12bfl.htm


2

Using PIVOT: WITH combined AS ( SELECT o.customer_no, EXTRACT(month FROM o.entry_date) AS order_month, EXTRACT(year FROM o.entry_date) AS order_year, t.order_final_tot FROM orders o JOIN order_totals t ON (o.order_no = t.order_no) ) SELECT * FROM combined PIVOT ( SUM(order_final_tot) AS ...


1

All users have a profile assigned. You can alter the user to have the default profile by: ALTER USER username PROFILE DEFAULT;


1

First, let me state that I think that running anything as the non-oracle user on your filesystem may result in significant database issues! (I'll explain why in a minute) Aside from the pathing issue, which you solved, I think that Colin is correct. You (or your admin) does need to give read on the binary: [root@db01 ~]# useradd captjackvane [root@db01 ~]# ...


1

In Oracle you can have your own aggregation function. To write one, we need to implement an interface which looks like this: create /*or replace*/ type mult_agg_type as object ( totalAggValue number, static function ODCIAggregateInitialize(sctx IN OUT mult_agg_type) return number, member function ODCIAggregateIterate(self IN OUT ...


1

Based on the documentation: An image copy is an exact copy of a single data file, archived redo log file, or control file. Image copies are not stored in an RMAN-specific format. They are identical to the results of copying a file with operating system commands. RMAN can use image copies during RMAN restore and recover operations, and you can ...


1

You can specify your queries per table with the QUERY parameter with Data Pump export. For example: QUERY=employees:"WHERE department_id > 10 AND salary > 10000" With the above, only those rows are exported from the employees table, that have department_id > 10 AND salary > 10000. More information in the official documentation: ...


1

You can check object privileges on tables through the view dba_tab_privs. Here's a query: SELECT grantee, privilege FROM dba_tab_privs WHERE table_name='XXXXX'; You can also learn more about privileges and stuff from this really cool website: http://www.dba-oracle.com/forensics/t_forensics_object_privileges.htm


1

I suspect you're using multiple sessions and the other session cannot "see" the work performed because it is currently uncommitted. The default Oracle transaction Isolation Level is Read Committed. This means that a session will not be able to see the work a given session has done until it has explicitly committed the transaction. The Oracle Server ...


1

You're unfortunately hitting bug 12999577 (Oracle support account required) or something close to it. And the fix for that bug introduced another bug 17564992. The workaround indicated in the first bug report is to switch the conditions around, i.e. express your where clause as 0 < ( ... ). The workaround indicated in the second is to set either of ...


1

Beware the OS authentication is done by client machine, not by the database server. I think that within reasonable pessimism to Oracle tools you could expect that it boils down to this: database server receives a TCP connection from whichever IP address that might pass the network path, and that connection just claims "I promise I've done OS authentication ...


1

I completely agree with dartonw's comments about reading through the guide. That's the only real way to start understanding the concept of indexing as it relates to Oracle and the Optimizer. One thing I will add to it though is this - Philosophically, as a DBA, I rarely decide what columns to index on my own. What I mean by this is that I go back to the ...


1

$ORACLE_HOME/network/admin is the directory wwhere tnsnames.ora file is created by DBCA and/or NetCA. An OracleHome path like /u01/app ... is an OFA compliant path that the installler uses as default if not overriden by installer.


1

The oracle-rdbms-server-12cR1-preinstall package includes a bash script that creates the oracle user, if not already existing, using the useradd command. In this case in log file /var/log/oracle-rdbms-server-12cR1-preinstall/results/orakernel.log you should see this entry: Please set password for oracle user You can run the following command as root ...


1

I figured it out. Instead of having the environment variables set for the user, they had to be asserted when starting the JVM for the application, just like you have to set the LD_LIBRARY_PATH and CLASSPATH for the JDBC Driver. If everything is set when starting up the JVM, and you have a sqlnet.ora file in the directory you've set TNS_ADMIN, the file will ...


1

You can't use contains because there is no Oracle Text index on the database source. You could, I suppose, write a query that copied the data from dba_source to a custom table, create an Oracle Text index on that table, and search that table using the contains function. It would generally make more sense, though, to just query dba_source with a like query ...


1

If you actually have different databases (and it sounds like you do), you can't grant permissions for users in one database to access data in another database. You'd need to create a database link in the EmployeeDB database that connects to the ContractorDB database. That database link can either use a fixed username and password in the ContractorDB ...



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