Alex Poole
  • Member for 10 years, 11 months
  • Last seen more than a week ago
How to use sqlplus to connect to an Oracle Database located on another host without modifying my own tnsnames.ora
39 votes

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

View answer
Is Oracle free to download?
16 votes

If you're really only looking to learn about the development side and have no interest in the administration or installation side at the moment, a quicker route might be to download a prebuilt ...

View answer
How to configure an Oracle Listener without SID_LIST_LISTENER in listener.ora?
14 votes

Bit late to the party on this one... The database should register with the listener automatically, making the SID_LIST entries redundant, and this seems to be happening with your 192.168.111.111 ...

View answer
create incremental number in oracle sql query
Accepted answer
14 votes

Similar to Kerri's answer, but without the with (and inspired by an SO answer): SELECT 2007 + LEVEL AS YEARS FROM DUAL CONNECT BY LEVEL <= 4; YEARS ---------- 2008 2009 ...

View answer
For Oracle, we mount the instance to the database or mount the database to the instance?
Accepted answer
12 votes

We are mounting a database to an instance. See the concepts guide: The instance mounts a database to associate the database with that instance. One way I've seen it explained (probably here or ...

View answer
Oracle create user with privileges to grant references
9 votes

You don't need to grant SYSDBA privileges, and shouldn't unless really necessary. You should follow the principle of least privilege. From Oracle's security guidelines: Do not provide database ...

View answer
Truncate parent table in Oracle when child table is empty
8 votes

The restrictions on truncating tables include: You cannot truncate the parent table of an enabled foreign key constraint. You must disable the constraint before truncating the table. An ...

View answer
SYSDBA password in Oracle DB
Accepted answer
7 votes

SYSDBA is not a user, it is a system privilege. When you connect as / you are connecting to the SYS user. You don't need a password when you connect as you are - as long as it's a local connection ...

View answer
What is non-CDB @ Oracle ?
6 votes

A non-CDB is anything that isn't a CDB; that is, any database before 12c, or a 12c database created without the enable pluggable database clause. If you create a non-CDB it isn't Multitentant and is a ...

View answer
Virtual Oracle server for education?
6 votes

If you don't want to do an installation directly on you PC, Oracle provide pre-built VM images which contain an 11g database, SQL Developer and other tools; more info here. They are very quick and ...

View answer
ORA-12505: TNS:listener does not currently know of SID given in connect descriptor
6 votes

As the ORA-19815 message indicates, your 3GB recovery destination is full; the database is unable to archive any more redo log files. It has reached a point where it needs to switch a full redo log ...

View answer
Oracle partition by interval using persian calendar
Accepted answer
5 votes

I don't see any way to have intervals that are defined in a different calendar than your database-level NLS_CALENDAR. You could get the same effect by partitioning on a numeric representation of the (...

View answer
Sqlplus parameterized query via file
Accepted answer
5 votes

If you want the SQL in one file and the parameters in another, this is an option. Have query.sql assigning positional parameters to bind variables and then executing a query: variable p_owner ...

View answer
User can't stop Oracle listener (TNS-01190)
Accepted answer
4 votes

The description for the message you're seeing is: Cause: Most of the listener administrative commands are only intended to be issued by privileged users, for example DBAs or system ...

View answer
oracle 11g seems to die every 2 days, how can i debug what's happening?
4 votes

Oracle has an alert log which should tell you when and why it shut down. Depending on how the database was configured, that will be under a directory identified by either the diagnostic_dest or ...

View answer
How to set up tns listeners for Multiple Oracle Installations on One machine
4 votes

You need to have different keys for the IPC entries in the two configurations, e.g. KEY = EXTPROC10 and KEY = EXTPROC11. IPC uses memory and semaphores etc. that may be shared across the machine, so ...

View answer
SQL Data aggregation
Accepted answer
4 votes

You can use analytic functions to achieve this: select distinct pm.city cityid, to_char(trunc(pm.sampledate, 'MM'), 'YYYY-MM') as timepoint, count(distinct me.id) over (partition by ...

View answer
What does it mean by '4M blocks' or '4G blocks' in Oracle?
Accepted answer
4 votes

In this case 4G and 4M aren't quite referring to what I think you mean by the unit of digital storage; it isn't referring to 4 gigabytes and 4 megabytes. It's just a number, not a unit. Oracle has to ...

View answer
Is there a setting to suppress C-Style comments appearing as DOC in Oracle Output?
Accepted answer
4 votes

SET DOC[UMENT] OFF At least in 9i. I can't make these comments display at all in 10g, so I think they've made this option even more obsolete - it still controls explicit DOC blocks, but apparently ...

View answer
How can I select rows from a hierarchical query with the lowest level?
3 votes

You could put the hierarchical query in a subquery and user an analytic function and windowing to figure out the first match. If you table looked like: create table t42 (id number, parent_id number, ...

View answer
What is the maximum number of interval partitions in Oracle 11g?
3 votes

From the create table documentation: Notes on Partitioning in General The following notes pertain to all types of partitioning: You can specify up to a total of 1024K-1 partitions and ...

View answer
ORA-01435: user does not exist
Accepted answer
3 votes

DazzaL has highlighted the nub of the problem but to give a bit more background, on the assumption you're fairly new to Oracle... The create table syntax shows that your command is trying to create a ...

View answer
Tablespace and userid info
3 votes

Have a look at the DBMS_METADATA package. First set long to some big number so the output isn't truncated, and set the SQL terminator so you can see how statements are separated: SQL> set long ...

View answer
How do oracle date time work?
3 votes

BETWEEN is inclusive. From the documentation: And the value of expr1 BETWEEN expr2 AND expr3 is the value of the boolean expression: expr2 <= expr1 AND expr1 <= expr3 So your first ...

View answer
Oracle XE 11g (ORA-12514: TNS:listener does not currently know of service requested in connect descriptor )
Accepted answer
2 votes

In a comment you said your web.xml contains: <init-param> <param-name>DatabaseConnectionString</param-name> <!-- The string "${USER}" in the connection string will be replaced ...

View answer
sql query - table@string meaning
Accepted answer
2 votes

It's part of a distributed query. The string after the @ is a database link. You can see where the database link is pointing by querying the all_db_links view, or the user_ or dba_ equivalents ...

View answer
export SQL statements from Oracle 11g database via command line
2 votes

Depends what you mean by 'from the command line', but you can generate the DDL via the dbms_metadata package (see here for example). If you want the data as well then you'd then have to roll your own,...

View answer
ORA-01034: ORACLE not available
Accepted answer
2 votes

dbstart will only start databases that have the third parameter in the oratab file set to Y. Check the value in that file. You also don't need to pass the ORACLE_HOME as an argument; not sure if you ...

View answer
What is this database dump format?
Accepted answer
2 votes

As @a_horse_with_no_name says, it's a file created with the original export utility, i.e an exp command rather than expdp. You'll need to import it with the matching original import utility, using the ...

View answer
Oracle "Import from Export Files" screen refusing both SYSADMIN and Normal users
Accepted answer
2 votes

The SYSMAN account is for adminstering Enterprise Manager, rather then the database; see the predefined user accounts. I would shy away from modifying anything about any of those accounts, even ...

View answer