Andrea Girardi - It's my blog!

Tag: Oracle

Import oracle expdata file to different user

To import an Oracle exported file with *nix command line, use this command:

imp \'/ as sysdba\'  file=expdat.dmp fromuser=ORIGINALUSER touser=DESTINATIONUSER

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)

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>

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

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;

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

Copyright © 2017 Andrea Girardi – It's my blog!

Theme by Anders NorenUp ↑