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

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


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

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


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


6

As Dba's answer already shows, account status information is accessible via the dba_users view. Connected with a user having the appropriate grants, this can also be used to identify "inactive users": SELECT username, account_status, created, lock_date, expiry_date FROM dba_users WHERE account_status != 'OPEN'; tells you for each locked/expired account ...


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

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


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

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

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

DROP and TRUNCATE are Data Definition Language commands and cannot be rolled back. However, in Oracle you can use Tablespace Point in Time Recovery, Flashback Database, or Flashback Drop to recover your table. If the Recycle Bin is enabled in your database you can use the following command to recover your table (if it's still in the Recycle Bin). SQL> ...


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

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

You generally don't want to rely on implicit conversions of strings to dates. That only leads to pain and suffering since different users will have different date formats. Either use ANSI date literals or use the to_date function with an explicit format mask UPDATE bb_product SET salestart = date '2012-06-01', saleend = date '2012-06-15', ...


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