Oracle DBA tips
http://www.clusterbuilder.com/vmware/
SQL
To list all the tables
SELECT table_name
FROM all_tables;
SELECT SYSDATE FROM DUAL;
Default passwords:
SCOTT/TIGER, ADAMS/WOOD, JONES/STEEL, CLARK/CLOTH and BLAKE/PAPER.
10G onwards, by default the above users are disabled. The "emp" table is in scott's shema. To enable user scot;
01. sqlplus
SQL> connect sys/oracle as sysdba;
Connected.
SQL> alter user scott account unlock;
User altered.
Now exit the SQL* plus command prompt and launch it once again with scott/tiger.
It will say password expired and you may have to provide new password.
SQL Plus
SQL Plus
To save settings of a SQLplus session (like "set linesize") as default
Check out ORACLE_HOME/sqlplus/admin. There's a file in there called "glogin.sql". Make changes to that, such as pagesize
and linesize, and every time you then log onto SQL*Plus, those settings will take effect.
You can over-ride the glogin.sql settings by creating a login.sql, too. If it's in the directory where you launch SQL*Plus from, then login.sql takes precedence over glogin.sql.
To edit the buffer file afiedt.buf
SQL> edit
To rerun the last command you entered which is the in the buffer file afiedt.buf
SQL> / <return>
To run an SQL query that is in a file named myquery.sql
SQL> start myquery
(or)
SQL> @ myquery.sql
To install a procedure named proc1 in the database from a file named mydbproc.sql (Make sure the first line of the file begins create or replace procedure proc1. Make sure the last line of the script contains a forward slash / on a line by itself with no spaces)
SQL> start mydbproc
To see compilation errors, at the SQL> prompt type show errors
If there were no errors, proc1 is now installed in the database and available to be run
To run proc1 which you just installed, at the SQL> prompt type exec proc1;
The SET command
You can control various features of SQLPLUS using the SET command at the SQL> prompt. These commands do not need a semicolon but SQLPLUS is kind enough to let you use one.
To repeat the column headers every 100 lines rather than every 14
SQL> set pagesize 100
To make the screen 100 characters wide rather than 80
SQL> set linesize 100
To show one screen (pagesize lines) at a time (shows you one screen (pagesize lines) at a time)
SQL> set pause on
To show the sql commands on the screen when they are executed from a file using the start command
SQL> set echo
To display XXX instead of blanks for a null field
SQL> set null XXX
To make DBMS_OUTPUT commands actually work
SQL> set serveroutput on
Other SQLPLUS Commands
To send a copy of the terminal activity to myfile.lst
SQL> spool myfile.1st
To write the contents of file myfile.sql to SQL buffer
SQL> Get myfile.sql
To display the basic information about mytable's fields
SQL> describe mytable
To go out temporarily to Unix shell from sqlplus (To get back to SQLPLUS, type exit)
SQL> !* (exclamation point)
To Shell out to the operating system, run the ls command and return to SQLPLUS
SQL> !ls
DBA
export ORACLE_HOME=/products/oracle/ora9i
export ORACLE_SID=testdb
sqlplus '/ as sysdba'
Tables
To get the list of accessible tables
SELECT table_name FROM all_tables;
OR
A user grants access to his table to another user and then that other user creates a synonym in his schema. Then simply type
SELECT SYNONYM_NAME FROM USER_SYNONYMS;
To get the list of tables that you possess
SELECT TABLE_NAME FROM USER_TABLES ;
To get the list of views that you possess
SELECT VIEW_NAME FROM USER_VIEWS ;
Users
To list the users
SQL> select username, user_id from dba_users;
To list the users who own objects
SQL> select distinct(owner) from dba_objects;
To list the counts of object types by owner
Sql> select owner, object_type, count(*)
from dba_objects
group by owner, object_type
http://www.cuddletech.com/articles/oracle/
9i Install
Pre-Requisites
rpm -Uvh xorg-x11-deprecated-libs*.rpm
rpm -Uvh xorg-x11-libs-*.rpm
rpm -Uvh xorg-x11-xfs-*.rpm
rpm -Uvh alsa-lib-devel-*.rpm
rpm -Uvh fontconfig-devel*.rpm
rpm -Uvh freetype-devel*.rpm
rpm -Uvh libjpeg-devel*.rpm
rpm -Uvh libtiff-devel*.rpm
rpm -Uvh libungif-devel*.rpm
rpm -Uvh xorg-x11-6*.rpm
rpm -Uvh xorg-x11-deprecated-libs-devel*.rpm
rpm -Uvh xorg-x11-devel-*.rpm
rpm -Uvh audiofile-devel*.rpm
rpm -Uvh esound-devel*.rpm
rpm -Uvh libaio-0*.rpm
rpm -Uvh libaio-devel*.rpm
rpm -Uvh openmotif21-*.rpm
rpm -Uvh glib-devel*
rpm -Uvh ORBit-devel-*
rpm -Uvh gtk+-devel-*
rpm -Uvh imlib-devel-1*
rpm -Uvh gnome-libs-devel-*
Search for patch 4198954 and download it from http://metalink.oracle.com
p4198954_21_LINUX.zip
yum -u install compat-gcc-32 compat-gcc-32-c++ compat-libstdc++-33 compat-libstdc++-296 compat-libgcc-296
rpm -Uvh compat-libcwait-2.0-2.i386.rpm
rpm -Uvh compat-oracle-rhel4-1.0-5.i386.rpm
Create Oracle User, groups and directory
# groupadd -g 1001 oinstall
# groupadd -g 1002 dba
# useradd -g oinstall -G dba -u 1001 oracle
# mkdir -p /u01/apps/oracle/product/920
# chown -R oracle:oinstall /u01/apps/oracle
Add the following lines to $HOME/.bash_profile or $HOME/.bashrc
ORACLE_BASE=/u01/apps/oracle
ORACLE_HOME=$ORACLE_BASE/product/920
ORACLE_SID=TESTDB
LD_ASSUME_KERNEL=2.4.19
export ORACLE_SID ORACLE_BASE ORACLE_HOME LD_ASSUME_KERNEL
PATH=$ORACLE_HOME/bin:$PATH:.
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib
EDITOR=/bin/vi
export PATH LD_LIBRARY_PATH EDITOR
Setting Kernel parameters
Add the following lines to /etc/sysctl.conf
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
To take effect the new kernel parametes immediately
# /sbin/sysctl -p
9i Installation problem:
After installing, In the configuration tools, it gave the following error
jre was not found in /u01/apps/oracle/product/920/oracle.swd.jre/bin/i386/native_threads/jre.
solution:
Go to /u01/apps/oracle/product/920/oracle.swd.jre/bin/i386/native_threads directory
run "ln -s java jre"
http://www.dizwell.com/oracle/albums/linora9/album/prelim1.html
http://www.puschitz.com/InstallingOracle9i.shtml
http://download-uk.oracle.com/docs/html/B13670_06/toc.htm#BABIJIFJ
Keine Kommentare:
Kommentar veröffentlichen