Tag Info

Hot answers tagged

8

The decisive factor is whether there is a unique constraint/index on b (key_column). If there is no unique constraint, there may be duplicate values in b.key_column, so the optimizer has to always make a plan that reads from the table (or an index if there is one on that column). If on the opposite, there is a unique constraint, the query is equivalent to ...


5

This is simply an aggregation problem. select student_id, count(case when type in ('Expulsion','Probation') and quarter=3-1 then 1 end) as previous, count(case when type in ('Expulsion','Probation') and quarter=3 then 1 end) as "CURRENT" from student_actions group by student_id; See http://sqlfiddle.com/#!4/f4263/10/0


4

There are two very detailled documents here and here. There is a step by step guide (with videos) here. It is no coincidence that two of these documents are from Oracle themselves, it being very much in their interest to get clients to move from SQL Server to their product. Your question is a bit too vague and general for this forum, but since you're new to ...


3

It's not very clear what you are after but here's a query under my understanding that you want to find the last timestamp where queues = 'ACS' and the last timestamp where queuesname = 'GHK' for every groupnumb: with grp as ( select groupnumb, max(timestamp) as timestamp from tableX where queues = 'ACS' group by groupnumb union all ...


3

When you do a SELECT ... FROM some_view, the first thing Oracle will do is substitute some_view with SELECT statement that defines the view. So in your case, SELECT some_column FROM dummy is, both in logical and optimizer terms, the same as SELECT some_column FROM ( SELECT a.some_column, b.other_column FROM a LEFT JOIN b USING (key_column) ); ...


3

Use DECODE: SELECT ID, PROGRAM_ID, DECODE(PROGRAM_ID, 666, 0, 667, 1, PROGRAM_ID) AS PATTERN FROM ( SELECT ID, PROGRAM_ID FROM YOURTABLE ); Documentation link to DECODE(). Using CASE, alternatively: SELECT ID, PROGRAM_ID, CASE PROGRAM_ID WHEN 666 THEN 0 WHEN 667 THEN 1 ELSE ...


3

Check the licensing again, these options became free about 2 years ago. http://docs.oracle.com/cd/E11882_01/license.112/e47877/options.htm#DBLIC143 Network encryption (native network encryption and SSL/TLS) and strong authentication services (Kerberos, PKI, and RADIUS) are no longer part of Oracle Advanced Security and are available in all licensed ...


3

The new Oracle 12c In-memory database feature is not the same as TimesTen. It implements a column-store based storage in memory in addition to the traditional row-based format on disk. So your data is held in BOTH formats simultaneously. You choose which tables you want to store in memory. This can allow immense improvement in mixed-workload databases. It ...


3

Though I have serious doubts in your description, let's first clean up some misconceptions. Oracle is what is called a Relational Database Management System or RDBMS (the company developing it is called Oracle For short, too). It is used – who would have guessed it – to store and retrieve data based on its relations. It requires an operating system to run. ...


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


3

USER_INDEXES (and ALL_INDEXES) contains this information: select index_name, table_name, index_type from user_indexes where ityp_owner = 'CTXSYS' and ityp_name = 'CONTEXT'


3

You have an error in your query. The CTE m will return not a single row, but as many rows as in FCS_FP_TOMB. Then you are putting all of those rows into the row generator CTE n, whereas a row generator construct like you're using should get a single row. What I would do is to define m to return a single row with the minimum and maximum values, and then ...


3

The short answer is that no, the same SQL doesn't automatically lead to the same execution plan. For example, one may have a small table and the optimizer may realise that there's no point in using an index since an FTS (Full Table Scan) would be cheaper than trawling through an index and then performing lookups. The "No" answer is the reason that Oracle ...


2

Look at: http://docs.oracle.com/database/121/DBSEG/authentication.htm#DBSEG99827 Operating system authentication is not supported in a multitenant environment. You can't connect with "sqlplus / as sysdba" to a PDB. It only works with the CDB. If you change environment variable ORACLE_SID to that of a PDB, you'll connect to the binaries to what he expects ...


2

Zgguy answered this in comments: Explicitly stating columns in the insert solved the problem, as it was trying to put columns in the wrong columns. INSERT INTO BRAND_RECORD (STRENGTH, GENERIC_NAME, INTERCHANGEABILITY_DATE)


2

Connor MacDonald blogged about this over in NUMBER data type... what harm can it do? as well as Ask Tom: "How do I determine how much storage will be required for NUMBER(p, s)?". In short, it does matter. Take this table: CREATE TABLE T ( x1 number, x2 number(6,3) ); x1 will be 21 bytes while x2 will be 2 bytes. By not specifying the data type, ...


2

Is it possible to execute stored procedure in another stored procedure in oracle Yes. Demo Let's say I have two procedures, P1, and P2, such that Procedure P1 takes an input as employee number, and gives the employee name as output. Procedure P2 will be called inside P1 which will take the output of procedure P1 as input, and give it as output. ...


2

The answer to your question is that it won't let you. Oracle is stricter than other RDBMSes and you'll get an ORA-00904: invalid identifier if you try a CREATE TABLE with a reserved word. However, you can force it to do so by surrounding the name in quotes, for example: CREATE TABLE a ( "date" date ); ... But in doing so, you're essentially admitting ...


2

Having clause can only exclude rows which exist in data - in your case only possibility to get count(*)=0 means having no matching rows - so nothing to return in the first place. You probably want to count existing matches in the left join - so COUNT(m.MATCH_ID) will only count rows where match_id is not null. Edit: One more thing I noticed - you have lot ...


2

You can use the VSIZE to get the number of bytes for the internal representation of a value. If you want the total space used by a column, you'll need to sum over all the rows (unless the type used has a fixed length). The DUMP function will also give you the size and raw representation of a value, but not in a format amenable to easy summation.


2

Set MEMORY_TARGET to at least the recommended value. SQL> CREATE PFILE FROM SPFILE; MANUALLY EDIT PFILE & CORRECT MEMORY_TARGET VALUE SQL> CREATE SPFILE FROM PFILE; SQL> STARTUP What OS are you using?


2

Most probably this is fake alarm. the space UNDO tablespace is allocated differently then on other tablespaces. Oracle simply does not clean out old versions of block unless necessary. There are no real data in this tablespace. You UNDO space was really full you would get ORA-01555 Snapshot too old, or other types of errors. Check the view v$undostat.


2

You are facing a bug. You need to gather more information using RDA in order to perform an in-depth analysis. These are known bugs related to your ORA-600 message. 10043021 11.2.0.4, 12.1.0.1 ORA-600 [kcbnew_3] after select / analyze on RECYCLEBIN object 17586955 11.2.0.4.4, 11.2.0.4.BP11 ORA-600 [ktspfmdb:objdchk_kcbnew_3] in RAC 12747437 ...


2

Well actually….no. The second Development Milestone Release of MySQL Cluster 7.2 introduces support for what we call “Multi-Site Clustering”. In this post, I’ll provide an overview of this new capability, and considerations you need to make when considering it as a deployment option to scale geographically dispersed database services. You can read more ...


2

Proper backup syntax should have been: backup database tag "first" plus archivelog tag "first"; Now if you look into your list backup output you can notice that only backupsets with archivelogs has tag FIRST. Backupset containing backup of datafiles has autogenerated tag TAG20150515T105436.


2

Yes, but do not simply delete it - deinstall it properly: http://docs.oracle.com/database/121/LADBI/remove_oracle_sw.htm#LADBI7824 Run $ORACLE_HOME/deinstall/deinstall and follow the instructions.


2

Output is from a 11.2.0.4.6 Enterprise Edition database on Oracle Linux 7.1 x86-64 platform. Lets start with question 2 and an easy example. DISTINCT and GROUP BY are handled differently: the optimizer is able to completely eliminate a DISTINCT under certain circumstances, but it can not do the same with GROUP BY. Here is an example: create table t4 as ...


2

After a pretty good amount of digging, I found this blog post on doing this for date ranges, and it turns out to work equally well on numeric ranges, with a little modification for handling the 0 and 1 endpoints. Taking the query from there and renaming the columns to be a bit more clear, we have SELECT * FROM (SELECT MAX(T_END) OVER (ORDER BY T_START) ...


1

As Justin said, all_tab_modifications is not a real-time accurate count of the number of modifications to a table since the last time statistics were gathered I recommend you to track this kind of events, see the following Link


1

The research I did made it seem like I need to either write my own aggregation or upgrade Oracle. I ended up doing neither, and exported the query through Tableau, formatting it in a sheet appropriately, then exporting that sheet to excel and making an aggregation column.



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