Oracle
Oracle SQLPlus Note
@ and @@
The @ (at symbol) is equivalent to the START command and is used to run SQL*Plus command scripts. A single @ symbol runs a script in the current directory (or one specified with a full or relative path, or one that is found in you SQLPATH or ORACLE_PATH). @@ will start a sqlplus […]
Oracle: Alter Password Policy
Whenever an Oracle user is created, the default profile is DEFAULT is none is specified. To set the login attempts to unlimited, run the following to change the DEFAULT profile.
alter profile DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;
Make the password unavailable for 90 days
ALTER PROFILE new_profile
LIMIT PASSWORD_REUSE_TIME 90
PASSWORD_REUSE_MAX UNLIMITED;
Set default […]
New JDBC URL Syntax for Oracle 10g JDBC Driver
This is something you might have overlooked if you are using Oracle JDBC Driver.
Prior to Oracle 10g, the syntax for the JDBC URL looks like below
jdbc:oracle:<driver_type>:@hostname:<;listener-port>:<SID>
E.g.
jdbc:oracle:thin:@db.oracle.com:1521:mydb
However, there is a special THIN style service name syntax supported with only THIN JDBC driver that was introduced in 10g JDBC driver
jdbc:oracle:thin:@//host_name:port_number/service_name
Here is an example,
jdbc:oracle:thin:scott/tiger@//myhost:1521/myservicename
The format […]
Oracle: Oops, my spfile is corrupted !!
I was trying to restart my Oracle 10g database server. Oops, the database server can be be started because the spfile is corrupted. Someone must have edited it using a text editor…
I cannnot use the alter system command to regenerate the spfile since the database is not running. What can I do ??
After some trials […]
Oracle Compress Table Problem ?
An interesting finding…
create table test_table
(
col_1 number
)
compress;
alter table test_table add (col_2 number);
insert into test_table values(1,2);
After this, try to drop one column
alter table test_table drop column col_2;
ORA-39726: unsupported add/drop column operation on compressed tables
This is expected as the table is still compressed.
alter table test_table nocompress;
alter table test_table drop column col_2;
ORA-39726: unsupported add/drop column operation on compressed tables
Even […]
Oracle AWR and ADDM for Performance Tuning
Oracle AWR (Advanced Workload Repository) and ADDM (Automated Database Diagnostic Monitor) which are available since Oracle 10g are useful features for performance tuning and troubleshooting.
For developer like me who uses Oracle but is not a Oracle dba both AWR and ADDM are useful when I need to troubleshoot for performance related issues related to my […]
Oracle - Understanding Your Database Update Statement
Run the following SQLs to create the table and index
create table mytable
( col1 number(12) primary key,
col2 varchar2(100),
col3 varchar2(100),
col4 varchar2(100)
);
create or replace function myfunc(
col2 in varchar2
) return number deterministic
as
begin
dbms_output.put_line(’Testing function’);
return 1;
end;
/
create index myidx on mytable(myfunc(col2));
insert into mytable values(1,’test’,'test’,'test’);
The function based index myfunc is created on mytable using col2. A record with […]
Oracle Queries for Performance Monitoring and Tuning
Here are some Oracle queries I collected from various websites
Resources in High Demand
select active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history
where active_session_history.sample_time
between sysdate - 60/2880 and sysdate
group by active_session_history.event
order by 2
Locked Objects
SELECT o.owner, o.object_name, o.object_type,
o.last_ddl_time, o.status, l.session_id,
l.oracle_username, l.locked_mode
FROM dba_objects o, gv$locked_object l
WHERE o.object_id = l.object_id;
Long Running Operations
select * from (
select opname, target, sofar, totalwork,
units, elapsed_seconds, message
from v$session_longops […]
TOra - An Toad Alternative for Oracle
I bet that everyone will know Toad if they use Oracle. Sometimes I think it is Oracle strategy not to have a user friendly tool so that we will need to pay for its support or hire a DBA
Anyway, I found that most people working on Oracle, whether they are developers or DBAs, […]
Dynamically Import Data from Different Oracle Databases from Web Application
Download Source
Sometimes back when I was involved in a project I faced the challenge to import data from different Oracle databases synchronously within my Java web application. Performance is the key issue. The import has to be completed in a short time frame according to the SLA.
Of course I can dynamically create JDBC connection to […]