Tag Info

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.


7

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

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

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

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


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

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

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


3

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


3

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


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

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

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


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


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


2

From what you are describing it seems like you are attempting to display all records from Station_Series along with matching records from Control_Point. If that is what you are attempting, perhaps the following makes sense? SELECT CP.ID "Control Point ID" , CP.Station "Station" , CP.Station_Series "Station_Series" , ...


2

SQL Server uses autocommit mode by default. This cannot be changed permanently. There are two ways implicit transactions (non-autocommit) can be turned on: At the server level such that new sessions use it by default, using sp_configure 'user options' -- this may or may not work depending on how SQL Developer was implemented. For a session, using SET ...


2

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


2

In SQL*Plus and SQL Developer you need a / at the end of a PL/SQL block. variable pStartDateBegin VARCHAR2(10); variable pEndDateFinish VARCHAR2(10); begin select '01-01-2000', '30-11-2011' into :pStartDateBegin,:pEndDateFinish from dual; end; / [...]


2

Look at these lines : CREATE TABLE DEPARTMAN ( DEPARTMAN_ID NUMBER(*, 20) NOT NULL, DEPARTMAN_AD VARCHAR2(40) NOT NULL, DEPARTMAN_SOYAD VARCHAR2(40) NOT NULL), CONSTRAINT DEPARTMAN_PK PRIMARY KEY (DEPARTMAN_ID) ); You have an extra left parenthesis before the CONSTRAINT. After you remove it, it should look like this: CREATE TABLE DEPARTMAN ( ...


2

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


2

Sounds like the network buffer is being filled but not flushed. SQL*Net performance in this area is often managed by setting tcp.nodelay=yes in sqlnet.ora; also look at the send_buf_size and recv_buf_size parameters. SQL*Dev working and not SQL*Plus are what pointed me to this type of troubleshooting approach. SQL*Developer uses jdbc behind the scenes to ...


2

The problem was solved by opening the "Firewall" under Security Groups in the AWS Dashboard to incoming connections from my computer's IP.


2

As the documentation says implicitly, you can create synonyms in the current DB only. So the first @PACOMNET in the synonym name is unnecessary (and causing the error). You should instead connect to the PACOMNET database and run CREATE SYNONYM B2H.BILLING_USER_ACTIVITY FOR B2H.BILLING_USER_ACTIVITY@PACOMNET.US.ORACLE.COM;


2

I was able to move the Java code to Oracle. I am writing this for people who are facing similar issue. Log on to server where Oracle database is installed through Putty (command prompt in case of windows) Place the Java file in server using WinSCP. Find the Java compiler present in oracle home using command find / -name javac Compile the Java file using ...


2

Since SQLDeveloper is a Java program using a jdbc driver, the restrictions are made by the version of the jdbc driver. The v10 and v11 drivers can connect to v9 without any problems. For older version databases you might need to get the v8 or v9 jdbc drivers.



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