On this page... (hide)
1. Auto commit
When using the command line sqlplus interface, updates aren't commited until you quit sqlplus, or set auto commit. Below is how to set auto commit.
Create a file in your home directory called login.sql it'll run it after you log in with sqlplus.
Enter set autocommit on at the sqlplus command line interface.
You can also add the following to your login.sql file.
set sqlprompt "&&_USER@&&_CONNECT_IDENTIFIER&&_PRIVILEGE SQL>" define _editor=vi ALTER SESSION SET NLS_DATE_FORMAT = 'SYYYY-MM-DD HH24:MI:SS';
2. Get all columns from a table
$table_name and $user_name are variables.
select dbms_metadata.get_ddl('TABLE','$table_name','$user_name') from dual
3. Installing Oracle Express Edition on Ubuntu
3.1 Add swap space - Oracle needs at least 1GB
$ dd if=/dev/zero of=/1gb.swap bs=1M count=1024 $ mkswap /1gb.swap
edit /etc/fstab and add
/1gb.swap none swap sw 0 0
3.2 Reboot
3.3 Add Oracle repository to APT
Add to /etc/apt/sources.list
deb http://oss.oracle.com/debian unstable main non-free
$ wget http://oss.oracle.com/el4/RPM-GPG-KEY-oracle -O- | apt-key add - $ apt-get update
3.4 Install Oracle-XE server
$ apt-get install oracle-xe $ /etc/init.d/oracle-xe configure
- use default ports
- set system password
3.5 Add environment variables for Oracle
edit /etc/bash.bashrc, add the following to the bottom
ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server PATH=$PATH:$ORACLE_HOME/bin export ORACLE_HOME export ORACLE_SID=XE export PATH
- Logout from root, logout from user, log back in
3.6 Connect to Oracle with sqlplus and modify web access restricton to allow non-localhost
$ sqlplus system@XE
- enter password you set during configure
- do the follow command...
SQL> EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);
3.7 Test that the web interface works
- get IP of VM with
ifconfig - with browser, go to
http::/xxx.xxx.xxx.xxx:8080/apex » - login with system user/pass
Thanks Chad
4. Drop a tablespace and all tables
SQL> drop tablespace tbs_data including contents and datafiles;
http://devtime.blogspot.com/2006/04/tablespaces-in-oracle-part-2.html »
6. [Click to add or edit comments])
Please prepend comments below including a date