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

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


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


6

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


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

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

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

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

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.


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

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


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

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

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

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


4

You could save multiple connections to the same db but with different credentials. Give them useful names like user@thisdb and you'll be able to view that in SQL Developer at a glance in the top right corner of the worksheet. And my favorite part: if you need to run the same SQL as multiple users or in different environments or both, you'll find that the ...


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

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


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

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

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

Based on your description of this behaviour and your other comment, I guess your database runs on Windows. The command you used should suffice and delete the datafiles. Except on Windows. On Windows, the datafiles are not always deleted, it is a known limitation. Before dropping tablespaces or datafiles, take them offline: alter tablespace ... offline; ...


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.


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

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

SQL Profiles are basically a kind of statistics rather than a database object like an index, but they can be transported to another database - see this SF question for how


3

Ok, you have the database software installed. Here is the 11.2 Installation Quick Guide for Windows Here is the 11.2 documentation home. Now you need to install a database instance and start a listener. Then you need to create a connection in SQL Developer to the database instance. Some essential tools during this are SQLPlus on the server. Enterprise ...



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