Oracle DBA Daily Experiences

Run the following SQL to find permissions granted on users on the database

select
case when p.protecttype = 205 then 'GRANT'
when p.protecttype = 206 then 'DENY'
else 'unknown'
end + ' ' +
case when p.action = 193 then 'SELECT'
when p.action = 197 then 'UPDATE'
when p.action = 195 then 'INSERT'
when p.action = 196 then 'DELETE'
when p.action = 224 then 'EXECUTE'
else 'unknown'
end + ' ON ' + objectowner.name + '.' + o.NAME + ' TO ' + u.name
from sysusers u
inner join sysprotects p on u.uid = p.uid
inner join sysobjects o on o.id = p.id
inner join sysusers objectowner on o.uid = objectowner.uid
WHERE u.name = 'username'
order by o.name, u.name

From the following KB article it list couple of store procedures which can be used to send email through SQLServer. One of the feature it has the “From/Sender” can be passed in a parameter where as in case xp_sendmail it can’t be.
http://support.microsoft.com/kb/312839

Depending on tool one can grant access to users if they need access to look at code for store procedure, function and package without giving the user execute access.

For AquaStudio for example one can set up user with following access which would allow to look at the code, the access needs to be granted by sys. In this setup you are replace the view all_objects with dba_objects and all_source with dba_source.

grant select on sys.dba_source to ;
create synonym .all_source for sys.dba_source;

grant select on sys.dba_objects to ;
create synonym .all_objects for sys.dba_objects;

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

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)

— SQL server version
SELECT @@version

Example output:
Microsoft SQL Server 2005 – 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

– Current logged in username
SELECT suser_sname()

Example output:
sa

– Client hostname
SELECT host_name()

Example:
myhostname

– Get Current date time on the server
SELECT getdate()
Output: 2010-01-31 13:20:11.113

– Row count of rows selected/updated/deleted/inserted
@@ROWCOUNT – Row count of rows selected, updated, deleted, inserted