Tag Info

Hot answers tagged

20

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


14

Commants to the local instance execute on return. Multi-line commands to the server execute on semicolon Special commands as detailed in the SQL*Plus manual are the only ones that do not accept semi-colons. Wheras SQL Commands must end with a ; in order to be parsed by the server.


12

Several ways. You can set the EDITOR environment variable before running SQL*Plus (Assuming Unix), to allow use of an external text editor (vi, by way of example): export EDITOR=vi Then type ed in SQL*Plus to edit the previous query in the vi editor. Of course, you may prefer nano, pico, emacs, vim etc etc. If you don't wish to set an environment ...


11

The difference is that -- and /* */ can be used in a PL/SQL block, while REM[ARK] cannot. The following will work in SQL*Plus: REM comment -- comment /* comment */ begin DBMS_OUTPUT.PUT_LINE('Test'); --comment DBMS_OUTPUT.PUT_LINE('Test'); /* comment */ end; / These will not: begin DBMS_OUTPUT.PUT_LINE('Test'); REM comment end; / begin ...


11

but for example no "arrow-up" key for the previous history entry is available. You're talking about sqlplusw the "GUI" tool - the oldschool sqlplus does support arrow-up-bring-previous-statements feature. What is a good replacement / extension for sqlplus Oracle's SQL Developer, a free GUI-based tool, is pretty neat. If you're a vim addict, ...


9

Its an interesting question, to be sure. Most people who are familiar with Oracle development wouldn't give it a thought but when you come down to it, its sometimes confusing to define the demarcation between SQL and PL/SQL. By looking at the definition of the acronyms, you start to get an idea of what areas of functionality each covers: SQL - Structured ...


9

If it is wrapped in BEGIN ... END DECLARE ... END CREATE OR REPLACE ... END Is a one-liner prefixed EXECUTE Then it is PL/SQL. What does this mean under the hood? SQL gets "compiled" to a query plan and executed, immediately returning a result set in the event of SELECT or the number of rows affected in other cases, or an error. PL/SQL however is more ...


9

There is nothing like PostgreSQL's set search_path in Oracle. The closest thing I can think of would be a logon trigger for the user that run's an ALTER SESSION SET CURRENT_SCHEMA ... CREATE OR REPLACE TRIGGER LOGON_TRG AFTER LOGON ON SCHEMA BEGIN EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = foobar'; EXCEPTION when others then ...


9

Metalink bug 9103343 states: This is expected behaviour. SQL*Plus is written in oci and oci has a default prefetch value of 1 row. However prefetch upon a fetch (as opposed to upon an execute) only takes place when you are not performing an array fetch, so when arraysize is 1. Regardless of arraysize the first fetch in the trace is always ...


8

you need a / after a PL/SQL block in SQL*Plus: SQL> begin 2 null; 3 end; 4 -- here you need a / 5 / PL/SQL procedure successfully completed This is so SQL*Plus knows you are done with your statement (which could include intermediate, non-terminating ;). SQL types may include PL/SQL code, therefore the SQL*Plus devs decided that you ...


8

A very easy tweak is to add rlwrap.


8

AVG and other aggregate functions work on sets of data. The WHERE cause does not have access to the entire set, only to data for the row it is operating on. If you created your own AVG function (as a normal function and not a custom aggregate function) it would only be passed one ID value when called from the WHERE clause not the entire set of ID values. ...


7

When your OS user is from a DBA group, you can connect AS SYSDBA with OS authentication: Two special operating system groups control database administrator connections when using operating system authentication. These groups are generically referred to as OSDBA and OSOPER. The groups are created and assigned specific names as part of the database ...


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


6

The second statement is creating an object table. It is almost never the case that you really want to use an object table. That was something that was introduced in the 8i time frame when Oracle was making the database object oriented. While a lot of the object oriented PL/SQL enhancements have been useful, using object types in SQL is not something ...


6

You can use easy connect for this: sqlplus usr/pass@hostname.network/remote_service_name If your listener is on a non-default port use ...@hostname.network:port/.... Edit: Actually it seems you have to supply a service name, not a SID; they may be the same but if not you'll need to obtain that from the server.


6

When you're entering an SQL statement into SQL*Plus, it needs to know when you're done with it, especially if the command spans multple rows. Therefore, it requires a terminal character character which can be set with the set sqlterminator. By default, this character is the semicolon: SQL> select * 2 from 3 dual; D - X Now, changing this ...


6

Not in a supported fashion, no. Why would you care what the USER_ID of a new user was? That would seem to raise a pile of red flags in my mind.


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

These variables are a feature of SQL*Plus. psql, the equivalent program in the PostgreSQL world, also has variables. Use \set variable 'value' and insert into mytable(mycolumn) values (:variable);


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


5

Create a copy of the tnsnames.ora file in a directory that you can write to, modify the file accordingly, then set the TNS_ADMIN environment variable to the location of that directory. eg: cp $ORACLE_HOME/network/admin/tnsnames.ora /tmp/tnsnames.ora # edit the /tmp/tnsnames.ora file to add your entries # Set the $THS_ADMIN environment variable so that ...


5

An alternative technique may be to use an external table. There is no need to take your CSV (or whatever) file and load it in a separate step. Simply declare your external table in the correct format (this is almost identical to a SQL*Loader parfile, but wrapped in a CREATE TABLE statement) and you can issue a SELECT directly against it, once the file is in ...


5

You should be able to run it via the HOST command: SQL> host /path/to/ora/bin/sqlldr parfile=...


5

SQL Tools is an alternative I have tried. It didn't suit my needs, but it might yours. SQL Tools++ is built on SQL Tools.


5

Well, the short answer is "'Cause Larry says so." ;) but you can do it with a nested select: select * from tab1 where id > (select avg(id) from tab1) HTH


5

Each site I've consulted with that had Oracle swore by Toad as an interface for queries. Not the DBAdmins, but mostly the developers.


5

This seems to be a quirk of SQL*Plus and arraysize rather than pipelined functions - the following demonstrates the same effect: create or replace function pause return integer as begin dbms_lock.sleep(2); return 1; end; / select pause from dual connect by level<10; You can (sort of) workaround the issue by selecting the rows twice and discarding ...


5

I don't think you can. The only thing I know that sort of does that (but is slightly annoying IMO) is the PAUSE option which waits for you to press enter between pages. The annoying part is that it pauses before the first row... You can Ctrl C to interrupt when you've seen enough. SQL> set pause on SQL> set pages 10 pause 'press enter to continue' ...


4

SQL Pal came very close to replacing SQLPlus for me.



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