To import an Oracle exported file with *nix command line, use this command:
imp \'/ as sysdba\' file=expdat.dmp fromuser=ORIGINALUSER touser=DESTINATIONUSER |
Oracle
June 27, 2013
Import oracle expdata file to different user
November 26, 2012
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
If the database is running, probably the database hasn’t registered yet with the listener. This occurs when the database or listener just starts up. To fix, connect as sysdba:
SHOW parameter LOCAL listener |
If something or nothing appears, reset it with:
ALTER system SET local_listener='(address=(protocol=tcp)(host=localhost)(port=1521))'; ALTER system register; |
At this point test if it’s working fine:
tnsping xe TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 26-NOV-2012 17:04:16 Copyright (c) 1997, 2011, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE))) OK (0 msec) |
October 25, 2012
Listener refused the connection with the following error:12505
No listener.ora file is required for LISTENER to operate. Try to move, rename, remove, or delete listener.ora file & then restart the listener.
Every Oracle DB tries once every 60 seconds to register itself with LISTENER as shown below:
tail -f /u01/app/oracle/product/11.2.0/xe/log/diag/tnslsnr/XXXXX/listener/alert/log.xml <msg time='2012-10-25T01:15:50.522-07:00' org_id='oracle' comp_id='tnslsnr' type='UNKNOWN' level='16' host_id='host.domain' host_addr='XX.XX.XX.XX'> <txt>25-OCT-2012 01:15:50 * service_update * XE * 0 </txt> </msg> |
September 20, 2012
Row count for all tables in schema
Counting all of the rows in a schema can require code that actually counts the table rows so, how do you count up all of the rows for all tables in a schema?
For Oracle with system user:
SELECT TABLE_NAME, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = UPPER('{schema}'); |
For mySql:
SELECT TABLE_NAME, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '{schema}'; |
June 19, 2012
Oracle admin on Ubuntu server
To be able to admin Oracle instance from sqlplus command line on Ubuntu server, for first you need to set the enviroment variables:
source /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh |
At this point, is possible to login as sysdba using:
sqlplus / as sysdba |
if you get insufficient privileges error, you need to add your user to dba group:
usermod [username] -G oracle, dba |
a this point is possible to startup and shutdown the Oracle instance on sqlplus prompt using:
SHUTDOWNN IMMEDIATE; |
May 16, 2012
Oracle 11gR2 Express Edition on Linux Ubuntu 11.10 howto
To install Orace 11gR2 Express Edition, please take a look to this tutorial (tutorial? THE TUTORIAL!)
https://forums.oracle.com/forums/thread.jspa?threadID=2301639
