Oracle

From Guides

Contents 1 Create User 2 List All Users/Schema 3 Drop User 4 Oracle Statements 5 Export / Import 6 Change XDB Ports 7 Connection Problems

Create User

Create a user and grant some privileges:

create user FOO identified by FOO default tablespace users temporary tablespace temp;
grant resource, connect to FOO;

List All Users/Schema

SELECT username FROM all_users ORDER BY username;

Drop User

Drop a user and all it's assets:

drop user FOO cascade;

Oracle Statements

Disable user input when hitting ampersand (&) character in sql script. By default you will be prompted for input when hitting a ampersand character.

SET DEFINE OFF

Export / Import

Export:

exp username/password file="path to file"

Import:

imp username/password file="path to file" log="path to log" fromuser=x touser=y 

Since Oracle 10 you can also use the faster expdp and impdp. (dp stands for data pump) Note that you cannot import dumps that were exported with expdp with the imp command and vice versa.

Change XDB Ports

call dbms_xdb.cfg_update(updateXML( 
dbms_xdb.cfg_get(), 
/xdbconfig/sysconfig/protocolconfig/httpconfig/http-port/text()',
8081));

call dbms_xdb.cfg_update(updateXML(
dbms_xdb.cfg_get(),
'/xdbconfig/sysconfig/protocolconfig/ftpconfig/ftp-port/text()'.
2111));

commit;

EXEC dbms_xdb.cfg_refresh;

Connection Problems

You restarted your server but now you cannot connect to your db.

Possible problem: TNS Listener not running.

Use this command to see if listener is running:

LSNRCTL STATUS

If the Listener is not running:

LSNRCTL START LISTENER