Hot answers tagged

42

One of the biggest benefit of using a materialized view is that Oracle takes care of keeping the data in sync. If you have a separate aggregate table, you are responsible for keeping the data synchronized. That generally requires a reasonable amount of code and a decent amount of testing and most organizations manage to make mistakes that leave holes that ...


40

sqlplus user/pass@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=hostname.network)(Port=1521))(CONNECT_DATA=(SID=remote_SID))) Maybe, and this might be dependant on the command line environment you're using, you need to quote the string, something like sqlplus ...


39

I've swapped between working on Oracle and SQL Server over the past few years, and wrote a blurb on going the other way here. There are a number of idiomatic and architectural differences, and various bits of terminology get used differently by the vendor and developer/DBA communities surrounding each product. Physical architecture SQL Server organises ...


35

Oracle and SQL Server have a number of architectural and idiomatic differences, and several key bits of terminology are used differently in the documentation. It's quite a few years since I did this, but some of the major idiomatic differences are: Oracle has no direct equivalent to tempdb. Global temp tables are persistent entities and you do not create ...


34

With GROUP BY b,a the tuples (null, 1), (1,1), (2,1) and (17,1) would end up in four different groups. With GROUP BY coalesce(b,a) the tuples (null,1), (1,1), (2,1) and (17,1) would end up in the same group. If you want the "conditional" grouping, then yes, the version with coalesce is probably what you want.


32

Use a common table expression (CTE) and a windowing/ranking/partitioning function like ROW_NUMBER. This query will create an in-memory table called ORDERED and add an additional column of rn which is a sequence of numbers from 1 to N. The PARTITION BY indicates it should restart at 1 every time the value of Val changes and we want to order rows by the ...


30

SYS: automatically created when Oracle database is installed automatically granted the DBA role has a default password: CHANGE_ON_INSTALL (make sure you change it) owns the base tables and views for the database data dictionary the default schema when you connect as SYSDBA Tables in the SYS schema are manipulated only by the database. They should never ...


27

Strictly, yes, the FROM clause of a SELECT statement is not optional. The syntax for SQL-99 details the basic SELECT statment, and the FROM clause doesn't have any square brackets around it. That indicates the standard considers it non-optional: SELECT [ DISTINCT | ALL ] {Column expression [ AS name ]} [ ,... ] | * FROM <Table reference> [ ...


26

In Oracle, users and schemas are essentially the same thing. You can consider that a user is the account you use to connect to a database, and a schema is the set of objects (tables, views, etc.) that belong to that account. See this post on Stack Overflow: difference between a User and a Schema in Oracle? for more details and extra links. You create users ...


25

From Wikipedia: The DUAL table is a special one-row table present by default in all Oracle database installations. It is suitable for use in selecting a pseudocolumn such as SYSDATE or USER. The table has a single VARCHAR2(1) column called DUMMY that has a value of 'X'. Thus, the dual table is a way to perform operations against what amounts to be an ...


25

It would seem that Oracle at one time had plans to give a different definition to VARCHAR than to VARCHAR2. It has told customers this and recommends against using VARCHAR. Whatever their plans were, as of 11.2.0.2 VARCHAR is identical to VARCHAR2. Here is what the SQL Language Reference 11g Release 2 says: Do not use the VARCHAR data type. Use the ...


23

Currently, the two are synonymous. VARCHAR is an ANSI standard data type but Oracle's implementation of the VARCHAR data type violates the ANSI standard by considering the empty string to be NULL (Oracle's implementation predates the ANSI standard). As Leigh points out, Oracle has stated that the semantics of the VARCHAR data type may change in the future ...


21

First impressions Depending on your performance requirements, 100TB is a fairly aggressive data volume. If you want Oracle, you should check out their Exadata systems. Also, take a look at the offerings from Netezza or Teradata. With that volume of selects you might want to look at an OLAP based front end or at least fairly aggressive use of ...


20

Oracle's Data Modeller can reverse-engineer from an existing database. They were charging for it at one time, but it is now free to use.


20

List all users who have been assigned a particular role -- Change 'DBA' to the required role select * from dba_role_privs where granted_role = 'DBA' List all roles given to a user -- Change 'PHIL@ to the required user select * from dba_role_privs where grantee = 'PHIL'; List all privileges given to a user select lpad(' ', 2*level) || granted_role ...


20

The answer to your question can be found in the SQL Language Reference (excerpt follows). Creating Basic Tablespaces: Examples This statement creates a tablespace named tbs_01 with one data file: CREATE TABLESPACE tbs_01 DATAFILE 'tbs_f2.dbf' SIZE 40M ONLINE; It sound like you are new to Oracle databases. Oracle provides a wealth of ...


20

These views are helpful for working with DB links in Oracle: DBA_DB_LINKS - All DB links defined in the database ALL_DB_LINKS - All DB links the current user has access to USER_DB_LINKS - All DB links owned by current user See more in Oracle documentation


20

When you create a new user, you can optionally specify the default tablespace and default temporary tablespace for any objects created by that user. For example: CREATE USER phil IDENTIFIED BY l33t DEFAULT TABLESPACE philtablespace TEMPORARY TABLESPACE philtemp; If you omit the clauses when creating the user, the user will inherit the ...


19

You can use regular expressions and regexp_replace to remove the duplicates after concatenation with listagg: SELECT Num1, RTRIM( REGEXP_REPLACE( (listagg(Num2,'-') WITHIN GROUP (ORDER BY Num2) OVER ()), '([^-]*)(-\1)+($|-)', '\1\3'), '-') Num2s FROM ListAggTest; This could be tidier if ...


18

In general, procedures should not commit. Those sorts of transaction control decisions should be left to higher-level code that knows when a logical transaction is actually complete. If you commit inside of a stored procedure, you are limiting its reusability because a caller that wants the changes the procedure makes to be part of a larger transaction ...


18

Assuming you can't change your structure, you can use a CASE statement in your ORDER BY: ORDER BY case cat when 'mgr' then 1 when 'dev' then 2 else 3 If you can change your structure, however, you can create a Category table including the category code and a ranking, join your sample table to the Category table and sort by this ranking.


18

An alternative approach to the co-related subquery (suggested by Kerri) would be to use the MERGE statement, which might be more efficient than the subselect (which can only be verified by looking at the execution plan of both statements). MERGE INTO table_b USING ( SELECT id, field_2 FROM table_a ) ta ON (ta.id = table_b.id) WHEN MATCHED ...


18

You might prefer something like this: select * from foobar where (subject,term) in ( ('STAT','111') ,('STAT','222') ,('ENGLISH','555') ,('COMM','444') ,('COMM','333') ,('STAT','222') ,('STAT','666') ...


17

No, partitioning allows some table scans to be restricted to a particular partition. Indexes tend to be useless if you will be returning more that 2 to 4 percent of the table's data. If your selection criteria allows the query to be localized to particular partition, then the other partitions won't need to be scanned. It might be possible for the ...


17

It's simple. MySQL has a single daemon that runs the database server. Within the server you can create any number of databases - these databases have no direct mapping to users. Oracle has a single database. When you create a user in an Oracle database, it also creates a Schema with the same name as the user that created it. This is equivalent to a ...


17

You need to recreate the control file This post by Kaunain Ahmed describes the necessary steps: do: alter database backup controlfile to trace; extract the "create controlfile" command from the background-dump-destination tracefile. shutdown the DB. Change the DB-Name in your init.ora and change the init.ora Change the SID in the ...


17

bilinkc's solution works fine, but I thought I'd toss mine out as well. It has the same cost, but might be faster (or slower, I haven't tested it). The difference is that it uses the First_Value instead of Row_Number. Since we are only interested in the first value, in my mind it is more straightforward. SELECT ID, Val, Kind FROM ( SELECT ...


17

I think this is what you're after: V_COUNT := MY_ARRAY.COUNT;


17

Is there an equivalent to MySQL SHOW DATABASES statement? There is no such thing. You can query listeners on a machine (lsnrctl status) to see what services are registered there, but that doesn't map one-to-one to database (and there could be multiple listeners on the same machine). Otherwise, the tools commonly used connect to one database instance, ...


17

I suspect they're part of a connection pool & therefore haven't idled out because they're being used frequently. INACTIVE in v$session merely means there isn't a SQL statement being executed at the exact moment you check v$session. If they're part of a connection pool they're doing their job properly by being logged in for long periods of time. The ...



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