Tag Info

Hot answers tagged

13

What you are asking is impossible, unless you brute-force the password hash for the user. How do you expect a system to be secure if passwords are stored in plain text?


10

If you're really only looking to learn about the development side and have no interest in the administration or installation side at the moment, a quicker route might be to download a prebuilt developer VM image for Virtualbox. That can get you up and running very quickly, and you can connect to the DB running inside the VM from outside, so you can continue ...


10

Oracle's official documentation library has some great resources, including: The Database Concepts guide, which is essential reading for beginner and Oracle ninja alike. The 2 Day DBA The 2 Day Developer's Guide


9

If I saw a data model where a table had 2.1 million rows, one of the columns had only 5 distinct values, and I knew that people wanted to get a listing of those distinct values on a somewhat regular basis, I would strongly suspect that the data model was missing a lookup/ dimension table. Rather than trying to tune your current query, I would bet that the ...


8

Of course you ought to study the license, but as I recall it is free to download and use for non-production purposes. It includes all of the Option packs, and is not in any sense cripled, but patches will not be available as they would be if you had a support account. Oracle XE is free to use in production environments, but has limited functionality and ...


7

This is a known bug (description is not public unfortunately): Bug:4396234 ET10.2OREDEF: NULLABLE COL OF *_TAB_COLUMNS TABLE NOT UPDATED AFTER ONLINE REDEF The NOT NULL constraints are copied as NOVALIDATE, and you have to set them to VALIDATE state manually, e.g: ALTER TABLE t84_redefenition ENABLE VALIDATE CONSTRAINT constraint_name; The primary key ...


7

Refer the the Concepts Guide - Overview of Views for this sort of question: Overview of Views A view is a logical representation of one or more tables. In essence, a view is a stored query. [...] Characteristics of Views Unlike a table, a view is not allocated storage space, nor does a view contain data. Rather, a view is defined by a ...


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


7

figured this out, here is a solution in case someone needs to do something similar... using double quotes "" around characters will display them as they are... select to_char(sysdate,'"Today is the" Ddspth "of" fmMONTH,Yyyysp') as Today from dual will display: "Today is the" Eighteenth "of" MARCH, Two Thousand Fourteen


7

It seems the CBO does not consider a skip scan at all with dynamic sampling, is this true? Actually this is really easy to verify, you can do this by enabling 10053 trace. You will see that the optimizer does not even consider skip scan at all. The reason for this, is the "_optimizer_skip_scan_guess" parameter. The default value for this parameter is ...


7

You created your column with double quotes, so now Oracle requires you to always refer to this column using identifiers and respecting the case. If you remove the double quotes, Oracle always transforms the name to upper case, so without them it is case insensitive. This will work if you created the column without identifiers: where rulE_id where RULE_ID ...


6

I would just delete. Even if I had to only delete if there were say exactly 2 records, then I would add this condition to the where clause, like DELETE FROM table1 WHERE col1='somevalue' AND (SELECT COUNT(*) FROM table1 WHERE col1 = 'somevalue') = 2; In all cases when writing a delete statement consider that in general the where clause is usually either ...


6

Here is the query you require: SELECT TABLE_NAME FROM DBA_TABLES WHERE IOT_TYPE IS NOT NULL; The documentation states: If this is an index-organized table, then IOT_TYPE is IOT, IOT_OVERFLOW, or IOT_MAPPING. If this is not an index-organized table, then IOT_TYPE is NULL.


6

What you are experiencing is called caching. The database doesn't have to go to disk the 2nd time because it can either get the data from its own buffer cache, or the operating system/disk array can also provide the data faster from its own cache. In order to see whether Oracle fetched the data from disk, or used its cache you can enable autotrace in SQL ...


6

Creating a new instance The following command line call will create a new database + instance dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbName sid -sysPassword pass -systemPassword pass because I have a dedicated oracle user for my install I also had to use -serviceUserPassword. With regards to the passwords, despite being in ...


6

Thanks to @ThisSuitIsBlackNot for pointing me to the tracing functionaility in DBI, I was able to track this down to an error with not specifying port. I knew that I should be pointing at 1521 when setting up other connections in other programs, but DBI didn't require port to function, it was just 60 seconds of waiting for it to give results. DBI assumes ...


6

If Oracle only scans the index it cannot decide if there is a row with NULL in this column. So it makes a full table scan. A possible workaround: create a bitmap index on column4. This index contains the NULL columns, too. More details can be found in this post Why isn't oracle using an index for distinct query ?


6

I don't know of any built-in way to accomplish this but you could use a derived table or a Common Table Expression (CTE or as Oracle likes to call them: subquery_factoring_clause), if your aim is to pass the parameters only once in the USING: -- derived table OPEN myCursor FOR SELECT value1 AS Value1, myPackage.function1 (value1, my.id) AS ...


6

Easily solved: export ORACLE_HOME=/usr/lib/oracle/12.1/client64 export PATH=$ORACLE_HOME/bin:$PATH export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH Stick those lines in your ~/.bash_profile if you want them to persist for a given user, or in /etc/bashrc to persist for all users.


6

Row count isn't a great indicator of database size. I would not be worried about Oracle scaling to 2 billion rows. Relational databases can practically scale up into the many terabyte range. The ability to scale comes down to data model, hardware, and developer skill, user requirements, and budget. It's feasible to scale an Oracle (or SQL Server) data ...


5

The number of columns in a table has nothing to do with the number of columns in the projection of a SELECT statement. You can always add additional computed columns to the projection (at least practically... theoretically, you might eventually hit some sort of logical limit). The fact that you are querying dual here is irrelevant. You can do exactly ...


5

Analytics functions to the rescue In particular, lead/lag will be your friend. SELECT LAG(ACTION_DATE) OVER (PARTITION BY PERSON ORDER BY ACTION_DATE ASC) PREVIOUS_ACTION_DATE , LAG(VALUE) OVER (PARTITION BY PERSON ORDER BY ACTION_DATE ASC) PREVIOUS_VALUE , PERSON , ACTION_DATE , VALUE FROM SomeTabe SQLFiddle


5

This is what you're looking for, if I've read your question correctly: SQL> select segment_type, sum(bytes)/1024/1024 as size_in_mb, sum(blocks) as size_in_blocks 2 from dba_extents 3 group by segment_type; SEGMENT_TYPE SIZE_IN_MB SIZE_IN_BLOCKS ------------------ ---------- -------------- LOBINDEX 43.4375 5560 INDEX ...


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

I don't see any way to have intervals that are defined in a different calendar than your database-level NLS_CALENDAR. You could get the same effect by partitioning on a numeric representation of the (Persian) month each date falls in, using a virtual column: create table test_temp_times ( id number(18) not null, xdate date not null, str varchar2(20), ...


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


5

It's still in beta. It won't be released till the 12.1.0.2 patchset. As such, unless you're in the beta program, not much information is available yet. I think the NDA has been lifted, as I saw Tom Kyte speak about it at Collaborate 14 in Las Vegas a couple of weeks ago, but, you can't actually use it yet. Hope that helps.


5

snapshot is an old and deprecated term for a materialized view. They're exactly the same thing, snapshot is just an older term. A view is a stored query. It consumes (effectively) no space, just the space required to store the text of the query in the data dictionary. When you query the view, the stored query is executed. The results, therefore, will ...


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



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