Hot answers tagged

14

How do you log onto courgette? Would that username identify you? You can check that by running select sys_context('userenv', 'os_user') from dual; The USERENV namespace can retrieve a lot of different information about the user and their environment. Find out more.


8

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

There are many ways to export data from Oracle and automate the functionality. Be sure to understand exactly what the data export is being used for, though. If it is for interop between systems, then export in a format your receiving system can understand. If it is for backup purposes, go for the exp/expdp (data pump) method because a database backup needs ...


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


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

There are a number of things that can cause the actual plan to differ from the estimated plan (and if you want to get really deep in the weeds, there are lots of things that can cause different methods of producing the actual plan to produce different results but I'll ignore that). The simplest (and most common) revolve around bind variables. If I do an ...


5

The reason you are having problems with dbms_metadata.get_ddl is that it outputs CLOBs which can be up to 4GB in size. By default, SQL*Plus and Oracle SQL Developer truncate long text so they don't trash the client with large gobs of text. It's very easy to override this behavior in SQL*Plus with a few SET commands and get clean DDL. The script you need ...


5

Unless I am missing something, your query would be something like this: select created, count(*) CreatedCount from yourtable group by created order by created; See SQL Fiddle with Demo Or if you have a time associated with the date, you can use TRUNC: select trunc(created), count(*) CreatedCount from yourtable group by trunc(created) order by ...


5

The Oracle client (aka driver) knows who you are in your operating system (because that code runs on your computer). This information is transmitted as part of the login process. Depending on your application and driver type (OCI/JDBC) it even transmits information like your computer's name. If you can, run a SELECT * FROM v$session WHERE sid = ...


5

define my_schema_name = 'SCHEMA_1'; CREATE OR REPLACE PROCEDURE "&my_schema_name".updateDBUSER( p_userid IN DBUSER.USER_ID%TYPE, p_username IN DBUSER.USERNAME%TYPE) IS BEGIN UPDATE DBUSER SET USERNAME = p_username where USER_ID = p_userid; COMMIT; END;


4

You have to use Service Names to connect to Pluggable Databases in 12c. It's just one of those things that can trip you up, I guess! SQL Plus connecting OK probably threw you.


4

1158 is the default port for Enterprise Manager Database Console, not the database listener, and you can not log in there using SQL*Net or JDBC. The default port for the database listener is 1521, try connecting using that port. You can check the listener port by: lsnrctl status


4

This can't be done without some kind of workaround, so here's one for you. You can recompile the PL/SQL after creation & raise an exception if the recompilation fails. This will cause SQL*Plus to exit on failure. For example: test.sql: create or replace procedure foo as begin this is an error; end; / exec execute immediate 'alter procedure foo ...


4

Asuming this is to transport data to an other system. It that case this will work: set colsep ";" set linesize 9999 set trimspool on set heading off set pagesize 0 set wrap off set feedback off set newpage 0 set arraysize 5000 spool you csv_file.csv select rows from your tables; spool off If you don't want a header line, change to heading off If this is ...


4

Oracle 11g doesn't support the LIMIT clause, though the impending 12c release is rumored to support it. Anyway, you can do this using an analytic windowing function: select * from ( select salary, row_number() over (order by salary desc) as rn from emp ) where rn = 4; You can also do this using rownum, but I find the above way to look cleaner. ...


4

Well, if sqlplus is screwing your dbms_metadata.get_ddl output, why not select the output in a CLOB and write the CLOB to filesystem. e.g. DECLARE data CLOB; objType varchar2(30) := 'TABLE'; objSchema varchar2(30) := 'SCOTT'; objName varchar2(30) := 'EMP'; fname varchar2(256) := objType || '_' || objSchema || '_' || objName || '.sql'; ...


4

Autotrace in SQL Developer gets the plan from v$sql_plan, and also gets the stats from your session, does a delta of session stats before and after running the query. Explain Plan asks the database what it THINKS the plan will be for your query. Your co-worker is right, they can differ wildly, and you're better served to use AutoTrace or our new feature in ...


4

The preferences dictate when the code assist features kick in. In 4.0.3 we filter the advice for the automatic bits when there are more than 10 suggested identifiers. So typing select * from on a large schema, you'd likely not see any suggested table or views names, unless you invoked the helper directly via ctrl+spacebar. As you type more, the more ...


4

Use quotation marks: CREATE DATABASE LINK my_link CONNECT TO daniel identified by "*password" USING 'hostname:port/servicename';


4

Most likely, it will finish a DDL command behind the scenes, but I wouldn't bet my job on it. What if your CTAS action runs out of tablespace? You would never see the error. If you have an unrealiable Internet connection, then the best solution is probably Simple/basic solution Use VNC or RDP to "jump" to a desktop or server that is in your datacenter. ...


4

One way to do this is to ensure that all tables of properties, or metadata, have a date_created and date_last_modified date. Then you can filter where the date created is in a time period and export the changes as inserts or updates. What I do is harder. All changes to development properties are saved as a script of inserts/updates and I use a project/bug ...


3

If you will have access to server's filesystem, or you can ask for this, you can use crontab utility. To verify whether you have access to cron, type in the following crontab -e If you will have an OS user,and if you can use the crontab utility to schedule a job, you can try something like this: */30 * * * * ~/bin/dump.bsh 1>~/bin/log/dump.log ...


3

What Office Excel version? What Oracle Database version? SQL Developer version? As long as I remember, you can connect excel with an oracle database since many years ago, and handle data like another excel datasheet (with its limitations, of course). I'm sure they've improved this feature. ...


3

When you process a script in SQL Developer using the "Run Script" button (not the "Run Statement" button), it uses SQL*Plus internally to execute your script. Therefore using a SQL*Plus control setting which stops processing on errors should work. Try adding to the top of your script line like this: WHENEVER SQLERROR EXIT FAILURE


3

I followed these instructions to add the jTDS Java library to Oracle SQL Developer. Basically it's about downloading the jTDS zip, unzipping it in some place you can reach later and then add the jTDS.jar as a JDBC third party driver in SQL Developer ((In the main menu) Tools > Preferences > (On the tree menu in the new window) Database > Third party JDBC ...


3

The green bug you're seeing (look closely) means the package have been compiled with DEBUG option. It is also seen on bare procedures and functions. It is a coincidence they don't expand - maybe a bug (duh) in SQLdeveloper :) On mine, these expand without problem.


3

Try this: COTT3 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = COTT3) ) ) If that doesn't work, replace "SERVICE_NAME" with "SID". Put this in $ORACLE_HOME/network/admin/listener.ora: LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = ...


3

The solution is very simple. The string of the query is just too long. I'm trying to optimize a stored procedure using dynamic sql with a parameter to decide if the sql is executed or output. I just copied the output into a fresh sql developer pane and tried to use the tuning advisor. The problem is that the generated output has a lot of trailing blanks, ...


3

As far as I know, Import/Export and Data Pump are command-line-only types of tools. But if you really wanted to use that functionality from within SQL Developer (or SQL Plus), Data Pump uses the built-in DBMS_DATAPUMP and DBMS_METADATA packages. You should be able to call those packages directly, so I would start there. There's documentation available in ...


3

tnsnames.ora should be located in TNS_ADMIN, which is defined as ORACLE_HOME/network/admin/. Go to a command prompt (assuming Windows from your mention of "c:\") and execute the following: echo %TNS_ADMIN% echo %ORACLE_HOME% Verify the directories returned, make sure that your tnsnames.ora file is in there.



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