Eric's Tech Notes: oracle

How is Oracle laid out?

An Oracle database "instance" is a set of running Oracle server processes, uniquely identified from any others that may be running in parallel on the same machine by a "SID".

Data in Oracle databases is organized into "tablespaces". A tablespace consists of tables, indexes, views, users, etc..

What useful tools come with Oracle Database (RDBMS) for UNIX?

Database Creation Assistant - a graphical Java-based database instance setup tool
```
	$ dbca
```
Oracle SQL Developer - a friendly graphical tool that allows you to export DDL (SQL commands) of an entire database, edit table definitions, and much more..
SQL*PLUS - The command-line tool for entering SQL commands
```
	$ sqlplus
```
SQL*Loader - the Oracle SQL Loader is a "bulk loader" tool that can be used for loading non-SQL data (like .csv files) into a pre-existing Oracle table.
```
	$ sqlldr
```

Why do I get shared memory errors when I try to start Oracle on Solaris?

Oracle depends on System V-style Shared Memory. You will need to add the following lines to /etc/system:

	* To make Oracle 10g happy:
	set shmsys:shminfo_shmmax=4294967295
	set shmsys:shminfo_shmmin=1
	set shmsys:shminfo_shmmni=100
	set shmsys:shminfo_shmseg=10
	set semsys:seminfo_semmns=2000
	set semsys:seminfo_semmsl=1000
	set semsys:seminfo_semmni=100
	set semsys:seminfo_semopm=100
	set semsys:seminfo_semvmx=32767
	set noexec_user_stack=1

And reboot...

	# /usr/sbin/shutdown -y -g0 -i6

How do I re-link Oracle on a different platform?

	$ORACLE_HOME/rdbms/lib$ make -f ins_rdbms.mk install

What does "dual" mean to Oracle?

"dual" is a pseudo-table used for expression evaluations, like:
```
	SQL> SELECT 2 * 2 FROM dual;

	       2*2
	----------
	         4

	SQL> 
```

How do I start the Oracle database manually?

	ora$ bin/sqlplus /nolog
		conn system/manager as sysdba or connect /as sysdba
		startup
	ora$ lsnrctl start
	ora$ emctl start dbconsole

How do I automate the Oracle database startup at boot time?

Here's the secret missing Oracle /etc/init.d/oracle startup script:

	#!/bin/sh
	#
	#	/etc/init.d/oracle
	#       Oracle startup script
	#
	
	# For Debugging:
	#set -x

	ORACLE_SID=mysid
	ORAHOME=/opt/ora
	ORACLE_HOME=$ORAHOME
	export ORACLE_SID ORAHOME ORACLE_HOME

	PATH=$ORAHOME/bin:/usr/bin

	case "$1" in
	        start)
        	        echo "Starting Oracle..."
			sqlplus /nolog <<EOF 
	connect sys/kent as SYSDBA
	startup
	EOF
	                lsnrctl start
        	        isqlplusctl start
                	emctl start dbconsole
	                onsctl start

        	        ;;
	        stop)
        	        echo "Stopping Oracle..."
                	onsctl stop
	                emctl stop dbconsole
        	        isqlplusctl stop
                	lsnrctl stop

	                sqlplus /nolog <<EOF 
	connect sys/kent as SYSDBA
	shutdown abort
	quit
	EOF
	                ;;
	        *)
        	        echo "oracle [start | stop]"
                	;;
	esac

How do I create a new Oracle database?

What you're really creating is a new tablespace (and, if needed, a new Oracle database instance):

	SQL> CREATE tablespace mydata
		datafile '/opt/oracle/oradata/mydata/mydata.dbf'
		size 32m autoextend on next 32m maxsize 2048m extent management local;

	--or, a more detailed example--

	SQL> CREATE TABLESPACE ts_something
	  logging
	  datafile '/dbf1/ts_sth.dbf' 
	  size 32m 
	  autoextend on 
	  next 32m maxsize 2048m
	  extent management local;

How do I get a list of Oracle users?

	SQL> SELECT name,ctime,ptime from sys.user$;

How do I add an Oracle user?

	SQL> CREATE USER bob 
		IDENTIFIED BY toughpassword	-- Assign password
		DEFAULT	TABLESACE  mydata	-- Assign space for table and index segments
		TEMPORARY TABLESPACE temp;	-- Assign sort space

	-- After creating a new user, assign the required privileges:
	SQL> GRANT CONNECT, RESOURCE TO bob;
	SQL> GRANT DBA TO bob;	-- Make user a DB Administrator

	-- Remember to give the user some space quota on its tablespaces:
	SQL> ALTER USER bob QUOTA UNLIMITED ON tools;

How do I lock/unlock an Oracle user account?

	SQL> ALTER USER bob ACCOUNT LOCK;   -- lock a user account
	SQL> ALTER USER bob ACCOUNT UNLOCK; -- unlocks a locked users account

How do I delete an Oracle user?

	SQL> DROP USER bob CASCADE;		-- Remove user

How do I see the names of all the tables within the current user's schema?

	SQL> SELECT table_name FROM user_tables;

To show all of the tables that the current user has access to, use:

	SQL> SELECT table_name FROM all_tables;

--or--

	SQL> SELECT tablespace_name,table_name,num_rows from all_tables;

Handy commands to get a list of tables:

	SQL> SELECT table_name FROM user_tables;

	--or--

	SQL> SELECT table_name,TABLESPACE_NAME,PCT_USED FROM user_tables;

How do I see how big my Oracle tables are?

	SQL> SELECT table_name,num_rows,blocks FROM user_tables;

Biggest oracle tables, by rows?

	SQL> SELECT table_name,num_rows,blocks FROM user_tables ORDER BY num_rows;

How do I view a table's schema/definition ("DDL")?

	SQL> DESCRIBE table

		-or, for more detail-

	SQL> SELECT dbms_metadata.get_ddl('TABLE','JUST_A_TABLE') from dual;

	SQL> SELECT table_name,tablespace_name,num_rows from all_tables where tablespace_name = 'DATA' and table_name like '%people%';

Dumping SQL ("DDL") descriptions of schema elements:

	SQL> SELECT dbms_metadata.get_ddl('TABLESPACE', 'DATA') FROM dual;

How do I get a list of all VIEWs?

	SQL> SELECT view_name,text FROM all_views;

How do I write an Oracle stored procedure (PL/SQL)?

	SQL> CREATE OR REPLACE PROCEDURE myproc
	IS
	BEGIN
	  DBMS_OUTPUT.PUT_LINE('Hello World!');
	END;
	/

How do I find Oracle stored procedures that won't compile?

	SQL> SELECT object_name,status,owner FROM all_objects
	     WHERE object_type='PROCEDURE' AND status='INVALID';

How do I run a stored procedure?
```
	SQL> EXECUTE myproc;
```

Why don't I see the output of my Oracle stored procedure?

	Enlarge the SQL*Plus buffer size and enable server message output:

	SQL> SET ARRAYSIZE 1
	SQL> SET MAXDATA 60000

	SQL> SET SERVEROUTPUT ON

View PL/SQL compilation errors:
```
	SQL> SHOW ERRORS PROCEDURE myproc
```

How do I see the errors in an Oracle stored procedure?

	SQL> show errors procedure myproc;

	Errors for PROCEDURE myproc:

	19/8     PLS-00341: declaration of cursor 'mything' is incomplete or
	         malformed

	SQL> SELECT text FROM user_source WHERE name='myproc' and line=19;

How do I recompile a stored procedure?
```
	SQL> ALTER PROCEDURE myproc COMPILE;
```

How do I see a stored procedure's source code?

	SQL> SET HEADING OFF
	SQL> SELECT text from user_source where name='myproc' ORDER BY line;

	--or, generate the DDL code with--

	SQL> SELECT dbms_metadata.get_ddl('PROCEDURE','myproc') FROM dual;

How do I use a cursor in PL/SQL?

	Loop through all the rows in a query with a cursor:

	DECLARE
	   CURSOR dept_cur IS
	   SELECT deptno
	     FROM dept
	    ORDER BY deptno;
	   -- Employee cursor all employees for a dept number
	   CURSOR emp_cur (v_dept_no DEPT.DEPTNO%TYPE) IS
	   SELECT ename
	     FROM emp
	    WHERE deptno = v_dept_no;
	BEGIN
	   FOR dept_rec IN dept_cur LOOP
	      dbms_output.put_line('Employees in Department '||TO_CHAR(dept_rec.deptno));
	      FOR emp_rec in emp_cur(dept_rec.deptno) LOOP
	         dbms_output.put_line('...Employee is '||emp_rec.ename);
	      END LOOP;
	  END LOOP;
	END;
	/

Other PL/SQL topics: variables, parameters, commit/rollback

Write a stored PL/SQL function:

	CREATE OR REPLACE FUNCTION whoami RETURN VARCHAR2 IS
	BEGIN
	   RETURN user;
	END whoami;

How do I show Oracle system parameters:
```
	SQL> SHOW parameters;
```

How do I increase the value of OPEN_CURSORS ?

	SQL> SHOW parameter open_cursors;

	--or--

	SQL> SELECT value from v$parameter where name = lower('open_cursors');

How do I change an Oracle parameter?

	SQL> ALTER SYSTEM set open_cursors=1000;

How do I change a session parameter?

	SQL> ALTER SESSION set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

How do I activate statistics reporting in Oracle?

	Activate for all SQL queries:

	SQL> SET AUTOTRACE ON | OFF;

How do I see who's using Oracle?

	-- View all connected ORACLE user sessions:
	SQL> SET LINE 200

	SQL> SELECT machine,username,sid,serial# FROM v$session
	WHERE username IS NOT NULL;

	-- or, for more detail --

	SQL> SELECT a.sid,a.serial#,b.spid,b.pid,a.username,a.osuser,a.machine
	FROM v$session a, v$process b
	WHERE a.username IS NOT NULL AND a.paddr=b.addr
	AND MACHINE='myserver.mycompany.com' ORDER BY sid,serial#;

	To see the SID and serial# of all Oracle sessions:
 
	SQL> SELECT a.sid, a.serial#, b.spid, b.pid, a.username,
	   a.osuser, a.machine
	FROM v$session a, v$process b
	WHERE a.username IS NOT NULL AND a.paddr=b.addr;

	(SPID is the UNIX process pid)