Hot answers tagged

51

The query is syntactically correct SQL even if table_b does not have a name column. The reason is scope resolution. When the query is parsed, it is first checked whether table_b has a name column. Since it doesn't, then table_a is checked. It would throw an error only if neither of the tables had a name column. Finally the query is executed as: select a....


47

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 "user/pass@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=hostname.network)(Port=1521))(CONNECT_DATA=(...


43

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

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


37

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


35

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.


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

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


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


28

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

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


25

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


24

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


24

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


24

did you try sqlplus username/password@host:port/service sqlplus x/y@c:a/b modern versions of sqlplus (version 11 or newer) understand this syntax and you don't need a tnsnames.ora file.


23

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


21

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


21

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


21

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


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


21

You'll need to create a user (or Schema) first C:\>sqlplus system/password SQL> create user CLIENT_TEST identified by client_test_password; SQL> grant connect, unlimited tablespace, resource to CLIENT_TEST; SQL> exit Then you can use the fromuser= and touser= IMP switches to import the data into the new user/schema: C:\>imp system/...


20

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.


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

It is not enough to force sqlplus not to wrap lines. It is also necessary to tell the viewer that you use to view the spool file not tow wrap lines. If your viewer is lessthen -S the option you have to use according to http://superuser.com/questions/272818/how-to-turn-off-word-wrap-in-less . On Unix/Linux you can use head -1 output.txt to get the first line ...


19

You can use easy connect for this: sqlplus usr/pass@hostname.network/remote_service_name To enable easy connect on your machine, you need to add it to the NAMES.DIRECTORY_PATH in sqlnet.ora, e.g.: NAMES.DIRECTORY_PATH=(EZCONNECT) If your listener is on a non-default port use ...@hostname.network:port/.... Actually it seems you have to supply a ...


19

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


19

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


18

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


18

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



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