Tag Info

Hot answers tagged

11

Partitioned views are a (very) old technique for partitioning data that are very rarely used today. Oracle added the ability to partition tables back in Oracle 8, which provides much more functionality than partitioned views, at which point partitioned views became obsolete. The only reason to consider using partitioned views would be if you can't afford a ...


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


9

There is likely a more streamlined set of steps, but here is a start. Delete the data out of any materialized views or home grown replication methods using triggers or the like. Delete the data out of any database audit tables. Change the database to noarchivelog. Create a new undo tablespace. Switch log files until all are archived. Shutdown the ...


8

Good question. I'll give a simplified answer. Oracle supports two character sets simultaneously, by way of different datatypes and parameters. A "normal" database-wide characterset and a "national" characterset. Now, the "normal" characterset affects the way that VARCHAR2, CHAR and CLOB data is stored. The "national" characterset affects the way that ...


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


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

I see two problems with your date filter: You're applying TO_DATE on a DATE datatype. It is not only superfluous, but it actually causes unexpected behaviour. Here's how Oracle analyses the expression TO_DATE(data_inicio,'yyyy/mm/dd'): Oracle searches for a function named TO_DATE that has two arguments (DATE, VARCHAR2) Since no such function exists, ...


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

The reason for this behaviour is that rows where LD is NULL cannot be found in the index. Therefore Oracle has to scan the full table. If the table is created with LD as a NOT NULL column then the optimizer uses this information and does an INDEX FAST FULL SCAN. If you add a "CHECK(LD is not null)" constraint to the table that has not NOT NULL defined for ...


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


6

The only reason why I might do that is if I needed to address an object that might not exist at compile time -- for example if I had code to create new external tables as required. As this implies, the dynamic SQL statement is not parsed when the PL/SQL compiles, so you have no idea whether it is correct or not, and dependencies are not stored in the ...


6

You can meet all your requirements with Standard Database Auditing or with Fine-Grained Auditing. For the standard auditing to also capture the SQL statements, you would set AUDIT_TRAIL initialization parameter to DB,EXTENDED or XML,EXTENDED (See "Settings for the AUDIT_TRAIL Initialization Parameter" in Database Security Guide). The audit records would ...


6

In Oracle Database, you can use Database Resource Manager (DRM) to manage memory, CPU, time and other resources. Usually your workflow is as follows: Create a resource plan Create the resource consumer groups Create the resource consumer group mappings Create the resource-plan directives You create a plan with the DBMS_RESOURCE_MANAGER.CREATE_PLAN ...


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


6

There's a nice technology available in Oracle called External Tables. In your scenario, you could access your external plain-text data using External Tables from within the database and update your existing data in database with SQL statements you love and are used to – for example, INSERT, MERGE etc. In most cases, using Oracle supplied utilities is the ...


6

Databases use server resources: CPU, RAM, disk I/O. Unless your server is physically partitioned (e.g. Solaris Zones) or you are using VMs it is a really bad idea to host multiple databases on the same server. You are just creating an environment where contention will be rife and tuning will be even trickier than normal. Oracle RDBMS has always been ...


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

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


5

If you didn't enable auditing in your database, you usually cannot spot who and when queried and/or modified the database, at least as easily as with auditing enabled. You can use various types of auditing for these tasks: Standard auditing Fine-Grained Auditing (FGA) Triggers With standard auditing, you can audit object and system privileges, such as ...


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

Oracle is compiling all objects, so as far as Oracle is concerned all code in all objects is new code. As a consequence, Oracle has no way of knowing that your function Str2Number still returns the same values that were used when the index was created. Instead you should only recompile invalid objects, exec dbms_utility.compile_schema(user, false); will ...


5

You've missed one place to get an overview of Oracle: the Concepts Guide. It covers all the major topics (including backup and recovery, which is quite important and doesn't appear in the list of links you've posted). Whats the next step? Create the Schema or Tablespace? Both! They're orthogonal. Users are logical entities that access your database. ...



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