Oracle DBA Daily Experiences

Using batch file one can automate running of scripts in osql.

Script that the main script which loops through file names in scripts.txt and calls runone.bat
C:> type runscript.bat
FOR /f %%a in (scripts.txt) do call runone.bat %%a

Script that runs one SQL script at a time, it connects using trusted account and dumps the output to scriptname.txt.out. If the script generates an error it returns a status and prints a message that error was encountered.
c:> type runone.bat
osql -E -b -d databasename -i %1 -o %1.out
@if errorlevel 1 @echo “there was an error see %1.out”

Input file list of scripts
c:> type scripts.txt
script1.sql
script2.sql
script3.sql

Sample output:

c:> .\runscript.bat

c:> FOR /F %a in (scripts.txt) do call runone.bat %a

c:> call runone.bat script1.sql

c:> osql -E -b -d msdb -i script1.sql -o script1.sql.out

c:> pause
Press any key to continue . . .

c:> call runone.bat script2.sql

c:> osql -E -b -d msdb -i script2.sql -o script2.sql.out
“there was an error see script2.sql.out”

c:> pause
Press any key to continue . . .

c:> call runone.bat script3.sql

c:> osql -E -b -d msdb -i script3.sql -o script3.sql.out
“there was an error see script3.sql.out”

c:> pause
Press any key to continue . . .

http://msdn.microsoft.com/en-us/library/ms186256.aspx

STATIC – data is scrollable, it require space on tempdb and changes to data are not visible right away. It does not allow modification.

CREATE TABLE #temp (k1 INT IDENTITY, c1 int)

INSERT INTO #temp DEFAULT VALUES
INSERT INTO #temp DEFAULT VALUES
INSERT INTO #temp DEFAULT VALUES
INSERT INTO #temp DEFAULT VALUES

– SELECT * FROM #temp

DECLARE c CURSOR STATIC FOR SELECT k1, c1 FROM #temp

OPEN c
FETCH c
UPDATE #temp SET c1=2 WHERE k1 = 2
FETCH c
– FETCH PRIOR FROM c
SELECT * FROM #temp

CLOSE c
DEALLOCATE c

DROP TABLE #temp

Output:
k1 c1
———– ———–
1 NULL

k1 c1
———– ———–
2 NULL

k1 c1
———– ———–
1 NULL
2 2
3 NULL
4 NULL

CREATE TABLE #temp (k1 INT IDENTITY, c1 int)

INSERT INTO #temp DEFAULT VALUES
INSERT INTO #temp DEFAULT VALUES
INSERT INTO #temp DEFAULT VALUES
INSERT INTO #temp DEFAULT VALUES

SELECT * FROM #temp

– FORWARD_ONLY – default cursor returs rows sequentially and doesn’t require space on tempdb and changes to data is visible right away
– The cursor is not scrollable
– DECLARE c CURSOR FORWARD_ONLY FAST_FORWARD FOR SELECT k1, c1 FROM #temp
DECLARE c CURSOR FOR SELECT k1, c1 FROM #temp

OPEN c
FETCH c
UPDATE #temp SET c1=2 WHERE k1 = 2
FETCH c
SELECT * FROM #temp

CLOSE c
DEALLOCATE c

DROP TABLE #temp

Output:
k1 c1
———– ———–
1 NULL

k1 c1
———– ———–
2 2

k1 c1
———– ———–
1 NULL
2 2
3 NULL
4 NULL

Using msdb.dbo.sp_update_job one can disable or enable jobs, the job_id can be retrieved from msdb.dbo.sysjobs

To enable
EXEC msdb.dbo.sp_update_job @job_id, @enabled = 1

To disable
EXEC msdb.dbo.sp_update_job @job_id, @enabled = 0

Using dbms_rowid one can map rowid back to object id, file id so the tablespace name and also using the file_id and block_no one can map it back to object_name.

declare
my_rowid rowid := 'AAADYWAABAAAHPaAAB'; /* or any rowid */
rowid_type number;
object_id number;
relative_fno number;
block_no number;
row_no number;
begin
dbms_rowid.rowid_info(my_rowid, rowid_type, object_id, relative_fno, block_no, row_no);
dbms_output.put_line('ROWID: ' || my_rowid);
dbms_output.put_line('Object#: ' || object_id);
dbms_output.put_line('RelFile#: ' || relative_fno);
dbms_output.put_line('Block#: ' || block_no);
dbms_output.put_line('Row#: ' || row_no);
end;
/

Sample Output:
Object#: 13846
RelFile#: 1
Block#: 29658
Row#: 1

Using the object_id you can can map the row id to object_name
SQL> select object_name from dba_objects where object_id = 13846;

OBJECT_NAME
——————————————————————————–
TEST1

Using the relative_fno you can can map it to file name and tablespace_name
TABLESPACE_NAME
——————————
FILE_NAME
——————————————————————————–
SYSTEM
/u01/oradata/TEST/system_01.dbf

Using the file id and block_no one can map it to object_name too
SQL> column segment_name format a30
SQL> select owner, segment_name from dba_extents where file_id = 1 and block_id<= 29658 and block_id + blocks > 29658

OWNER SEGMENT_NAME
—————————— ——————————
SCOTT TEST1

DBA_TAB_MODIFICATIONS view contains tables that were modified since last time statistics was gathered on the table. It contains the number of inserts, deletes and updates have occurred on the table. In 10g this is turned on automatically when the statistics_level is typical/all.

By calling DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO one can flush monitoring information from memory into dictionary. When DBMS_STATS.GATHER_*_STATS is called it calls DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO as one of the step to flush modification information.

In the example below it shows when dbms_stats.gather_schema_stats is called it flushes modification information in the dictionary.

SQL> select timestamp from dba_tab_modifications where table_owner = ‘SCOTT’;

TABLE_NAME TIMESTAMP
—————————— ——————–
TEST1 09-FEB-2010 22:17:46
TABLE2 09-FEB-2010 22:19:06

SQL> create table scott.table3( x number );

SQL> insert into scott.table3 values ( 10 );

1 row created.

– insert few rows in table3
SQL> /

..

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_schema_stats(‘SYS’);

PL/SQL procedure successfully completed.

SQL> select table_name, timestamp from dba_tab_modifications where table_owner = ‘SCOTT’;
TABLE_NAME TIMESTAMP
—————————— ——————–
TABLE3 09-FEB-2010 22:23:10
TEST1 09-FEB-2010 22:17:46
TABLE2 09-FEB-2010 22:19:06

Information on DBA_TAB_MODIFICATIONS
SQL> desc dba_tab_modifications;
Name Null? Type
—————————————– ——– —————————-
TABLE_OWNER VARCHAR2(30) — table owner
TABLE_NAME VARCHAR2(30) — table name
PARTITION_NAME VARCHAR2(30) — partition name
SUBPARTITION_NAME VARCHAR2(30) — sub partition name
INSERTS NUMBER — number of inserts in the table
UPDATES NUMBER — number of updates in the table
DELETES NUMBER — number of deletes in table
TIMESTAMP DATE — timestamp when the table was
TRUNCATED VARCHAR2(3) — if table was truncated
DROP_SEGMENTS NUMBER — if segments were dropped

If you need to capture SQL statements to replay in another instance you can turn trace on and capture in the trace files located in the udump folder.

– turn timing on.
alter session set timed_statistics=true;

– set to unlimited otherwise you may get an incomplete trace file when dump file size limit is reached.

alter session set max_dump_file_size=unlimited;

alter session set events ’10046 trace name context forever, level 12′;

– ******* run all of your processing here *******

alter session set events ’10046 trace name context off’;

SET STATISTICS TIME [ON|OFF] – Tells how much CPU time and time taken to parse and compile and also the CPU time and elapsed time taken for execution of the SQL statement.

Sample Output:
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

In SQLServer TRUNCATE TABLE can be rolled back when within a transaction. TRUNCATE still doesn’t log each row when it’s deleting each row and it doesn’t call DELETE trigger during truncate.

Example:
BEGIN TRANSACTION
SELECT count(*) FROM test
TRUNCATE TABLE test
SELECT count(*) FROM test
ROLLBACK TRAN
SELECT count(*) FROM test

Output:
———–
2

(1 row(s) affected)

———–
0

(1 row(s) affected)

———–
2

(1 row(s) affected)