sqlplus user/pwd@//host:1521/sid
${JAVA_HOME}/bin/java -Xbootclasspath/a:${JAVA_HOME}/lib/tools.jar -jar HotThread.jar
export CVSROOT=:ext:<your_cvs_account>@<cvs_server_name>:<cvs_root_dir>
export CVS_RSH=ssh
export CVS_SERVER=/usr/local/bin/cvs (or where your cvs is installed on server side)
You may put them in .profile file under your home. cd ~/.ssh ssh-keygen –t dsaJust hit “Return” key when prompted with “Enter passphrase”. You’ll then find two new files created: id_dsa and id_dsa.pub.
cat id_dsa.pubCopy the content (you'll paste it on server side in next step).
cd .ssh
vi authorized_keys
Add a new line at the end, and paste the content (copied from previous step). Save and quit. ssh <cvs_server>It should let you in without asking for password.
cvs co <cvs_path>
The requested list key 'sortTypeList' could not be resolved as a collection/array/map/enumeration/iterator type.After some investigation, I figured out the reason being that there is a piece of code in the action method to prepare this list to be dispalyed on the jsp page as a drop down list:
sortTypeList=new ArrayList(); sortTypeList.add(new KeyValueVO("id", getText("label.id"))); sortTypeList.add(new KeyValueVO("name", getText("label.name")));
public void prepare();
prepareSort() { // will run before sort() ...
prepareSearch() { // will run before search() ...
The prepare methods are invoked by the interceptor below:<interceptor-ref name="prepare"/>So, you'll need to make sure it is on the interceptor stack and it appears before interceptor "validation".
<interceptor-ref name="prepare"/>
...
<interceptor-ref name="validation"/>
The preparable interceptor supports a parameter: alwaysInvokePrepare. By default it's true, meaning the prepare methods will run.
CREATE TABLE interval_tab (
id NUMBER,
code VARCHAR2(10),
description VARCHAR2(50),
created_date DATE
)
PARTITION BY RANGE (created_date)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
SUBPARTITION BY LIST( code )
SUBPARTITION TEMPLATE
( SUBPARTITION CD_01 VALUES ('ONE'),
SUBPARTITION CD_02 VALUES ('TWO'),
SUBPARTITION CD_03 VALUES ('THREE')
)
(
PARTITION part_01 values LESS THAN (TO_DATE('01-NOV-2007','DD-MON-YYYY'))
);
INSERT INTO interval_tab VALUES (1, 'ONE', 'One', TO_DATE('16-OCT-2007', 'DD-MON-YYYY'));
INSERT INTO interval_tab VALUES (2, 'TWO', 'Two', TO_DATE('31-OCT-2007', 'DD-MON-YYYY'));
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');
COLUMN partition_name FORMAT A20
COLUMN subpartition_name FORMAT A20
COLUMN high_value FORMAT A10
SELECT partition_name, subpartition_name, high_value, num_rows
FROM user_tab_subpartitions
where table_name = 'INTERVAL_TAB'
ORDER BY table_name, partition_name, subpartition_name;
PARTITION_NAME SUBPARTITION_NAME HIGH_VALUE NUM_ROWS
-------------------- -------------------- ---------- ----------
PART_01 PART_01_CD_01 'ONE' 1
PART_01 PART_01_CD_02 'TWO' 1
PART_01 PART_01_CD_03 'THREE' 0
INSERT INTO interval_tab VALUES (3, 'THREE', 'Three', TO_DATE('01-NOV-2007', 'DD-MON-YYYY'));
INSERT INTO interval_tab VALUES (4, 'TWO', 'TWO', TO_DATE('30-NOV-2007', 'DD-MON-YYYY'));
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');
SELECT partition_name, subpartition_name, high_value, num_rows
FROM user_tab_subpartitions
where table_name = 'INTERVAL_TAB'
ORDER BY table_name, partition_name, subpartition_name;
PARTITION_NAME SUBPARTITION_NAME HIGH_VALUE NUM_ROWS
-------------------- -------------------- ---------- ----------
PART_01 PART_01_CD_01 'ONE' 1
PART_01 PART_01_CD_02 'TWO' 1
PART_01 PART_01_CD_03 'THREE' 0
SYS_P40 SYS_SUBP37 'ONE' 0
SYS_P40 SYS_SUBP38 'TWO' 1
SYS_P40 SYS_SUBP39 'THREE' 1
<jdbc-data-source ...
...
<password-encrypted>{3DES}xxxxxxxxxxxxxxx</password-encrypted>
...
</jdbc-data-source>
Dim con As ODBCConnection
Dim qry As ODBCQuery
Dim result As ODBCResultSet
Set qry = New ODBCQuery
Set result = New ODBCResultSet
con.ConnectTo("")
Set qry.Connection = con
for each document loop
qry.SQL = "insert into person (fname,lname) values( '" & v_fname &
"','" & v_lname & "' )"
Set result.Query = qry
end loop
...
The high-lite is the trouble. Whenever this was called, I noticed so many queries on Oracle data dictionary views. It seems to me that Notes was parsing the SQL, and the second statement sometimes took about 2 seconds.
qry.SQL = "insert into person (fname,lname) values( ?fname?, ?lname? )"
Set result.Query = qry
for each document loop
Call result.SetParameter( fname, "'" & v_fname & "'" )
Call result.SetParameter( lname, "'" & v_lname & "'" )
end loop
set serveroutput on
declare
type MONTH_TYPE is table of varchar(20) index by binary_integer;
month_table MONTH_TYPE;
begin
month_table(1) := 'Jan';
month_table(2) := 'Feb';
if month_table(3) is null then
dbms_output.put_line( 'March is not defined.' );
end if;
end;
/
What you'll get? You may think the print line.ERROR at line 1: ORA-01403: no data found ORA-06512: at line 9Well, associative array is working the same way as table (is that why it's defined as TABLE of ...), and month_table is similar to select value from month_table into v..., so need to have an exception handling.
set serveroutput on
declare
type MONTH_TYPE is table of varchar(20) index by binary_integer;
month_table MONTH_TYPE;
begin
month_table(1) := 'Jan';
month_table(2) := 'Feb';
if month_table(3) is null then
dbms_output.put_line( 'March is not defined.' );
end if;
exception
when NO_DATA_FOUND then
dbms_output.put_line( 'March is not found.' );
end;
/
Then, you'll get: March is not found.
declare
-- main variables
...
-- long op info
v_rindex PLS_INTEGER;
v_slno PLS_INTEGER;
v_totalwork NUMBER;
v_sofar NUMBER;
v_obj PLS_INTEGER;
v_op_name varchar(100) := 'My work';
v_units varchar(100) := 'rows processed';
begin
-- Calculate total work (number of rows to be processed, etc.)
select count(*) into v_totalwork from ...;
v_sofar := 0;
v_rindex := DBMS_APPLICATION_INFO.set_session_longops_nohint;
for ... -- A loop to process your work
loop
... -- do your work here.
-- log longops view.
v_sofar := v_sofar + 1;
if mod( v_sofar, 500 ) = 0 then -- log your operation every 500 rounds
DBMS_APPLICATION_INFO.set_session_longops(rindex => v_rindex,
slno => v_slno,
op_name => v_op_name,
target => v_obj,
context => 0,
sofar => v_sofar,
totalwork => v_totalwork,
target_desc => 'Some description here',
units => v_units);
end if;
end loop;
-- mark the end
DBMS_APPLICATION_INFO.set_session_longops(rindex => v_rindex,
slno => v_slno,
op_name => v_op_name,
target => v_obj,
context => 0,
sofar => v_sofar,
totalwork => v_totalwork,
target_desc => 'Some description here',
units => v_units);
end;
/
select sid, serial#, opname, sofar, totalwork, start_time, last_update_time from v$session_longops where opname = 'My Work';
select segment_name, sum(bytes)/1024/1024 MB_Bytes, sum(blocks) blocks, sum(extents) extents from user_segments where segment_name = 'BAD_TABLE' group by segment_name; SEGMENT_NAME MB_BYTES BLOCKS EXTENTS ------------------- ---------- ---------- ---------- BAD_TABLE 220 28160 99OK, move it to another tablespace:
alter table BAD_TABLE move tablespace tb_another;Run that check again:
select segment_name, sum(bytes)/1024/1024 MB_Bytes, sum(blocks) blocks, sum(extents) extents from user_segments where segment_name = 'BAD_TABLE' group by segment_name; SEGMENT_NAME MB_BYTES BLOCKS EXTENTS ---------------------- ---------- ---------- ---------- BAD_TABLE 32 4096 47It's much smaller. Well, you may want to move it back to its original tablespace.
ALTER function dbo.sp_getmonthend ( @inputDate DATETIME )
/*
This function returns the month end of a specific date.
*/
RETURNS DATETIME
BEGIN
DECLARE @outputDate DATETIME
select @outputDate = CAST(YEAR(@inputDate) AS VARCHAR(4)) + '-' +
CAST(MONTH(@inputDate) AS VARCHAR(2)) + '-01'
select @outputDate = dateadd( day, -1, dateadd( month, 1, @outputDate ) )
return @outputDate
END
Since there is 1st in every month, we forward 1 month from 1st of the month and backward a day.
create table tmp_test
( id number(5) not null,
test_type char(1),
comments varchar2(100),
constraint pk_tmp_test primary key (id)
);
create table tmp_test_type
( test_type char(1),
description varchar2(100),
constraint pk_tmp_test_type primary key (test_type) );
alter table tmp_test add constraint fk1_tmp_test foreign key ( test_type )
references tmp_test_type;
insert into tmp_test values( 1, 'A', 'Just a test' );
ORA-02291: integrity constraint (EAS_DEMO.FK1_TMP_TEST) violated - parent key not found
insert into tmp_test_type values( 'A', 'Test type A' );
drop table tmp_test;
drop table tmp_test_type;
CREATE TABLE DUP_TEST
(
COMPANY_ID VARCHAR(8),
COMPANY_NAME VARCHAR(80),
ADDRESS VARCHAR(80)
);
-- Create test data, dup by company id
INSERT INTO DUP_TEST VALUES ('1', 'Company One', 'Address1');
INSERT INTO DUP_TEST VALUES ('1', 'Company One', 'Address1');
INSERT INTO DUP_TEST VALUES ('2', 'Company Two', 'Address2');
INSERT INTO DUP_TEST VALUES ('2', 'Company Two', 'Address');
INSERT INTO DUP_TEST VALUES ('3', 'Company Three', 'Address3');
set serveroutput on
declare
rows_deleted integer := 0;
begin
for cur in ( select company_id, count(*) cnt from DUP_TEST
group by company_id having count(*) > 1 )
loop
delete from DUP_TEST
where company_id = cur.company_id and rownum < cur.cnt;
rows_deleted := rows_deleted + SQL%ROWCOUNT;
end loop;
dbms_output.put_line( 'records deleted: ' rows_deleted );
end;
/
SQL> select * from dup_test;
COMPANY_ COMPANY_NAME ADDRESS
-------- -------------------- --------------------
1 Company One Address1
2 Company Two Address
3 Company Three Address3
It's a little bit diffcult to delete duplicate rows in SQL Server. I'll show one solution using a cursor. Consider the performance when you have large amount of data to search or delete.declare cur cursor for select COMPANY_ID from DUP_TESTIt goes through the full table and search duplicates for each row, if found, delete the current of cursor.
declare @CompanyId int
open cur
fetch cur into @CompanyId
while @@fetch_status = 0
begin
if ( select count(*) from DUP_TEST where COMPANY_ID = @CompanyId ) > 1
begin
delete from DUP_TEST where current of cur
end
fetch cur into @CompanyId
end
close cur
deallocate cur
go
master..xp_cmdshell 'echo EmpID,LastName,FirstName > C:\temp\Employees.csv'It creates a file c:\temp\Employees.csv with 3 columns.
INSERT INTO OPENROWSET('Microsoft.Jet.OleDB.4.0',Open the file c:\temp\Employees.csv, you'll see there are 9 rows appended.
'Text;Database=C:\temp',
[Employees#csv])
select EmployeeID, LastName, FirstName from NOrthwind..Employees
(9 row(s) affected)
select * from OPENROWSET('Microsoft.Jet.OleDB.4.0',At the end, I'll show another way to read the data in csv file:
'Text;Database=C:\temp', [Employees#csv])
1 Davolio Nancy
...
9 Dodsworth Anne
select * from OpenRowset('MSDASQL',It seems you cannot append to csv using MSDASQL.
'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=C:\temp;',
'select top 3 * from "Employees.csv"')
Now sa and analyst need to be dropped from the linked server.
First find and drop the logins associated with the linked server.
user master
go
select s.srvid, s.srvname, l.name login_name_associated
from dbo.sysxlogins x, dbo.syslogins l, dbo.sysservers s
where l.sid = x.sid and s.srvid = x.srvid and s.srvname = 'ora_test'
go
srvid srvname login_name_associated
----- --------- ------------------------
1 ora_test sa
1 ora_test analyst
sp_droplinkedsrvlogin @rmtsrvname = 'ora_test', @locallogin = 'analyst'Then the linked server is ready to be dropped.
go
sp_droplinkedsrvlogin @rmtsrvname = 'ora_test', @locallogin = 'sa'
go
sp_dropserver @server = 'ora_test'The logins have to be dropped from linked server first, otherwise an error message will show:
go
There are still remote logins for the server 'ora_test'.
SQL> select salary from employees where employee_id = 100;Here shows the way to query the "before" data of yesterday:
SALARY
----------
24000
SQL> update employees set salary = salary * 1.05 where employee_id = 100;
1 row updated.
SQL> commit;
SQL> select salary from employees where employee_id = 100;
SALARY
----------
25200
SQL> select salary from employees AS OF TIMESTAMP sysdate - 1 where employee_id = 100;To show ten (10) minutes ago:
SALARY
----------
24000
select salary from employees AS OF TIMESTAMP sysdate - 10/60/24 where employee_id = 100;Or you can use function SYSTIMESTAMP instead of sysdate.
select salary from employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY) where employee_id = 100;To recover the change:
select salary from employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' MINUTE) where employee_id = 100;
SQL>update employees e1 set salary =
( select salary from employees AS OF TIMESTAMP sysdate - 1 e2
where e2.employee_id = e1.employee_id )
where employee_id = 100;
1 row updated.
SQL> select salary from employees where employee_id = 100;You may sometimes want to save the before data to a temporary table, then compare and reccover.
SALARY
----------
24000
create table tmp_employees nologging as select * from employees AS OF TIMESTAMP sysdate - 1;The SELECT ... AS OF will not work after the table structure changes or table truncation.
SQL> truncate table tmp_objects;
Table truncated.
SQL> select count(*) from tmp_objects as of timestamp sysdate - 1/24/60;
ORA-01466: unable to read data - table definition has changed
TEST =Replace ORACLE_HOST and ORACLE_SID with your oracle server name and SID or service name.
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ORACLE_HOST)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORACLE_SID)
)
)
p_addlinkedserver @server = 'ora_test'Where ora_test is the linked server you're going to created (or you can name it whatever you want), test is the local service name of SQL*Net. Keep product and provider Oracle and MSDAORA respectively.
, @srvproduct = 'Oracle'
, @provider = 'MSDAORA'
, @datasrc = 'test'
sp_addlinkedsrvlogin @rmtsrvname = 'ora_test'The example above gives analyst (a SQL Server login) permission to access hr schema in Oracle.
, @useself = false
, @locallogin = 'analyst'
, @rmtuser = 'hr'
, @rmtpassword = 'hr'
select * from ora_test..HR.EMPLOYEESYou'll see:
100 Steven King SKING 515.123.4567 1987-06-17 00:00:00.000 AD_PRES 24000.00 NULL NULL 90The object names in Oracle are in upper case. So you'll need to spell the schema and object names in upper case unless they're defined in lower or mixed case. In the example above, if you want select * from ora_test..HR.employees, you'll be informed that such table does not exist. Because SQL Server will translate the SQL to: select * from ora_test..HR."employees".
...