Tag Info

Hot answers tagged

8

You can do it in one go, sort of: SQL> MERGE INTO sale s 2 USING (SELECT ROWID rid, sale_at, product_id 3 FROM sale 4 WHERE product_id IN (SELECT parent_id FROM split) 5 UNION ALL 6 SELECT CAST (NULL AS ROWID) rid, sale_at, child_id FROM sale 7 JOIN split ON (parent_id = product_id)) m 8 ON ...


7

SQL Developer is using the so called "thin driver". For this the JDBC driver (a file named ojdbc6.jar) implements the complete Oracle network protocol which is usually handled by the Oracle client. It could use the another implementation: the "OCI" (or "thick") driver which would require an Oracle client to be installed. That would be necessary if e.g. ...


7

You created the table in the SYS schema (which you should never, ever do. Really, never). When you log in as teacher1 any statement looks for objects in that schema. But there is no TEACHER1.INSTRUCTORS table, because the real name is SYS.INSTRUCTORS (did I mention what a bad idea it is to create objects in the SYS schema?). You need to run select * from ...


7

I found the solution! It is so beautiful and I actually learned a LOT about Oracle. In one word: histograms. I started reading a lot about how Oracle's CBO works and I stumbled upon histograms. I didn't fully understand so I took a look at the USER_HISTOGRAMS table, and voilá. There were several rows for the sick table, and practically nothing for the ...


7

(This answers the other question about why the histograms are different.) Histograms are created by default based on column skew and whether the column was used in a relevant predicate. Copying the DDL and the data is not enough, the workload information is also important. According to the Performance Tuning Guide: When you drop a table, workload ...


6

The CBO will consider lots of different ways to execute your query, taking into account all the indexes and statistics on both tables and indexes. If you disable an index, obviously the CBO then cuts out all the plans it might have considered that involved that index. The question really boils down to: "why is the CBO choosing a poor plan", and the first ...


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

IMO, an external table is much easier to manage and flexible than a SQLLoader script. So if you're already doing a recurring SQLLoader load, I see no possibilities of evil in switching to external tables. By doing the external table, even though the base of the table resides in a text file outside the database, the data can be accessed from within the ...


5

Assuming you are licensed to use the AWR, you could query either the gv$active_session_history view for more recent data or dba_hist_active_sess_history for older data. gv$active_session_history will have a snapshot of what every active session was doing at the top of each second and will hold that data for a few hours. dba_hist_active_sess_history will ...


5

I emailed Jonathan Lewis about this and got a very helpful reply: The oddity in the calculation is a consequence of the limits on character-based histograms, see particularly: http://jonathanlewis.wordpress.com/2010/10/13/frequency-histogram-5/ http://jonathanlewis.wordpress.com/2010/10/19/frequency-histograms-6/ Looking at the example, the ...


5

Several things might be going on here and might be difficult to pin down but here's a few things. After Oracle crashes, it must do some type of recovery (as in media or crash recovery) to bring the database back to a consistent state. Depending on what you were doing (and whatever else was going on in the instance at the time of the crash) transactions may ...


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

The "why" goes back to the fact that when Oracle was originally built, output almost always went to some sort of fixed-width medium (either a terminal window or a physical printer). That meant that everyone's general preference that output widths be fixed since it made sure that everything was in alignment without the programmer or the application having to ...


4

create table usr_t_user_reg (username varchar2(30), db_instance varchar2(30), primary key(username,db_instance) ) / CREATE OR REPLACE VIEW usr_v_user_not_reg AS SELECT username "User", db_instance "Instance", ( CASE WHEN username IN ( SELECT username FROM sys.dba_users MINUS SELECT ...


4

Telling us that you are using a CASE statement but not showing us the actual code that you're using doesn't help us much. Assuming that "Result Table" is the desired output and that you want to use the old-style MAX(CASE syntax to do your pivot rather than using the newer PIVOT operator SELECT emp_id, emp_name, nvl( max( case when sal_paid = ...


4

If the table is in a single database, you don't want to replicate it. You don't want to create a second copy of the data. You simply want to give access to whatever schemas need access to the data. You may also want to create some synonyms so that you don't have to use fully qualified names. If userA owns table1 and you want that data to be visible to ...


4

This article explains how DDL statements work and how they need exclusive table locks, which plays out nicely when there are transactions running against the table. To summarize: If the table cannot be locked due to another transaction having a lock on the table ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired would be ...


3

The issue is you're restricting on the instance field, which is resulting in the roles only appearing in one column or the other. To get around this, remove the restrictions on db_instance and full outer join the roles from the second database to the roles in the first, like so: SELECT W.GRANTED_ROLE "GRANTED_ROLE_DB1", V.GRANTED_ROLE "GRANTED_ROLE_DB2" ...


3

If you want to implement some sort of dynamic list of columns for a PIVOT, then you can look at using a procedure that generates dynamic SQL. The basic syntax of the procedure will be: CREATE OR REPLACE procedure dynamic_pivot(p_cursor in out sys_refcursor) as sql_query varchar2(1000) := 'select USERNAME '; begin for x in (select distinct ...


3

This may or may not help you, but with PIVOT XML instead of the simple PIVOT you can do what you want. Adding the XML keyword to the PIVOT operator allows us to convert the generated pivot results to XML format. It also makes the PIVOT a little more flexible, allowing us to replace the hard coded IN clause with a subquery, or the ANY wildcard. ...


3

You can create two tablespaces, put all the data files for one tablespace on one drive and all the data files for the other tablespace on the other side, and then create the tables in whichever tablespace you would like. It seems unlikely, however, that you would really want to do this. It is very unusual to run a database on a server without any sort of ...


3

Index rebuild operation is DDL and thus may lock the underlying table preventing you from updating it. However queries by nature do not lock (unless FOR UPDATE clause is specified) and can read from objects even if they're locked, thus, generally, no problems will arise during the index rebuild while queries are running (maybe except for some performance ...


3

Two solutions: Install oracle manually the first time. Then zip all ORACLE_HOME and for future installation simply do: Unzip oracle_home into the new home Run $ORACLE_HOME/bin/relink all Run ./runInstaller -silent -attachHome ORACLE_HOME="<Oracle_Home_Location>" ORACLE_HOME_NAME="<Oracle_Home_Name>" Other solution (not my preferred but ...


3

Both functions TO_DATE and TO_CHAR take three arguments. The two last arguments take a default value if they are unspecified. The second argument is the format. It will default to your NLS_DATE_FORMAT session parameter value. Since you will potentially lose information during the conversion, there is no reason to assume that the two functions are the ...


3

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.


3

Does the SCHEMA.USR_ALL_USERS table actually need to take part in this query? It doesn't seem to: the same column that you are filtering the results on, USERNAME, is also used to join the other two tables. So, you could omit SCHEMA.USR_ALL_USERS and apply the filtering directly to SYS.DBA_ROLE_PRIVS and to SYS.DBA_ROLE_PRIVS@DB2_LINK. And now, having the ...


3

If your DBA's won't allow you to even explore the benefits in a proof of concept, time to move companies. External tables go back to version 10.1 at least, so they're not cutting edge technology. They're so widely (see Tom Kyte et al) held to be superior for many reasons (more flexible data transformations on import, MERGEing into other tables, multitable ...


3

You can use DROP TABLE <schema_owner>.<table_name> CASCADE CONSTRAINTS to drop the referential integrity too. Notice that there must be a reason to have a referential constraint in this table. Check it twice before drop. (I recommend to read documentation first http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9003.htm ) SYS is a ...


3

I would recommand always to use the appropriate datatypes, operators and functions and explicit conversion and not to use string literals and string processing and implicit conversion. Look at your statement select from_date from date_table where length(extract(year from from_date))='2' / The return type of the length statement is clearly a number. So ...


3

I am going to go out on a limb here and try and answer even though you haven't provided a lot of details. It looks like you want to create a comma-separated list of the columns and then the values below it but in sqlplus you are getting the column header as the actual SQL string. If I were to use the following in a SQL Fiddle I will get the same result: ...



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