How to create read only users in SQL Server 2012

Database Version: SQL Server 2012
Step 1.
Connect to your database server.
Expand Security > Logins > New login.

Step 2.
Put the login name and  select sql server authentication  and provide password.
On this window you may/ may not select the option enfornce password policy.

Step 3.
From User Mapping option select the database you want to give access.
Tick the boxes for role membership next to public and db_datareader.
Confirm by clicking OK.

Done. The read only user is created!

Oracle Database 11g: SQL Fundamentals I (1ZO-051) Dump

Title:-  Oracle Database 11g: SQL Fundamentals I Dump

Exam Code:-1Z0-051

Link:- https://onedrive.live.com/redir?resid=B9CA6221A841D988!252&authkey=!ADZ6bA9QTG3Adls&ithint=file%2cpdf

P.S. Please keep in mind the dump is used only for practice purpose, one should not totally rely on it to clear a certification. 

How to Reset SA Password in Sql Server 2012

I had a scenario where i forgot SA password or windows authentication was not working.
So i used the below method to reset the SA password to login into SQL Server.

Step 1:-
Change SQL SA password from a command prompt

Go to the command prompt of the server and type in command prompt osql –L
C:\Users\Administrator>osql -L

Servers:
localhost

Step 2:-
Copy full name of SQL Server and type: OSQL -S <insert_servername_here> -E
C:\Users\Administrator>osql -S localhost -E

Step 3:-
Execute the following query: sp_password NULL, '<insert_new_password_here>', 'sa'
1> sp_password NULL, 'soumya@123#','sa'
2> GO

Done, the sa password has been reset.

Oracle Database 11g: New Features for Administrators (1ZO-050) Dump

Title:-  Oracle Database 11g: New Features for Administrators

Exam Code:-1Z0-050

Link:- http://1drv.ms/1nvfvzo

P.S. Please keep in mind the dump is used only for practice purpose, one should not totally rely on it to clear a certification. 

Last Login Time for nonsys user in oracle 12c

Sqlplus shows Last Login Time for non system users in oracle12c.

In oracle 12c a new security feature has been added which allows us to check when did  a non system user logged in.

[oracle@server3 ~]$ sqlplus soumya/soumya

SQL*Plus: Release 12.1.0.2.0 Production on Mon Feb 15 08:35:28 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Mon Feb 15 2016 08:35:14 +05:30

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options


The feature can be disabled by doing the following:-
[oracle@server3 ~]$ sqlplus -nologintime soumya/soumya

SQL*Plus: Release 12.1.0.2.0 Production on Mon Feb 15 08:45:09 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options


The actual information is basically stored in column SPARE6 of SYS.USER$ and can be queried in the column LAST_LOGIN of DBA_USERS.

[oracle@server3 ~]$ sqlplus / as sysdba
SYS@ORA12C> col username FOR a15
SYS@ORA12C> col last_login FOR a25
SYS@ORA12C> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='DD.MM.YYYY HH24:MI:SS';

Session altered.

SYS@ORA12C> SELECT username,last_login FROM dba_users WHERE username='SOUMYA';

USERNAME        LAST_LOGIN
--------------- -------------------------
SOUMYA          15.02.2016 08:45:09

SYS@ORA12C> col name FOR a15
SYS@ORA12C> ALTER SESSION SET NLS_DATE_FORMAT='DD.MM.YYYY HH24:MI:SS';
col spare6 for a40
Session altered.

SYS@ORA12C> SELECT name,spare6 FROM USER$ WHERE name='SOUMYA';

NAME            SPARE6
--------------- -------------------
SOUMYA          15.02.2016 03:15:09


oracle@server3 ~]$ sqlplus soumya/soumya

SQL*Plus: Release 12.1.0.2.0 Production on Mon Feb 15 08:52:32 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Mon Feb 15 2016 08:45:09 +05:30

Oracle Database SQL Expert Dump (1Z0-047)

Title:-  Oracle Database SQL Expert Dump

Exam Code:-1Z0-047

Link:- https://onedrive.live.com/redir?resid=B9CA6221A841D988!246&authkey=!AHCNUFXPsb1Umd8&ithint=file%2cpdf

P.S. Please keep in mind the dump is used only for practice purpose, one should not totally rely on it to clear a certification. 

Expdp Backup script with retention for Windows

Oracle Version:- 11g

Make sure all the directories mentioned in the script is present.Just copy the following lines mentioned below and save it as .bat file. Schedule it on task scheduler
as per your requirement.Also make sure you have 7zip installed on your machine for the zip purpose.

@ECHO OFF
For /f "tokens=2-4 delims=/ " %%a in ('date /t') do (set mydate=%%c-%%b-%%a)
For /f "tokens=1-2 delims=/:" %%a in ("%TIME%") do (set mytime=%%a%%b)
set mytime=%mytime: =0%

set ORACLE_SID=orcl
set ORACLE_HOME=C:\app\Oracle11G\product\11.2.0\dbhome_1
set ORACLE_BASE=C:\app\Oracle11G\
set PATH=%ORACLE_HOME%\bin
set BACKUPPATH=C:\app\Oracle11G\Datapump
forfiles /p "C:\app\Oracle11G\Datapump" /m * /d -7 /c "cmd /c rd /s /q @path"
cd %BACKUPPATH%
mkdir "C:\app\Oracle11G\Datapump\%mydate%_%mytime%"


%ORACLE_HOME%\BIN\expdp system/system directory=BACKUP Full=Y dumpfile=backup_%mydate%_%mytime%.dmp logfile=log_%mydate%_%mytime%.log exclude=statistics
cd %BACKUPPATH%
"C:\Program Files\7-Zip\7z.exe" a -mx9 C:\app\Oracle11G\Datapump\Backup_%mydate%_%mytime%.7z C:\app\Oracle11G\Datapump\backup_%mydate%_%mytime%.dmp
move C:\app\Oracle11G\Datapump\Backup_%mydate%_%mytime%.7z C:\app\Oracle11G\Datapump\%mydate%_%mytime%\
move C:\app\Oracle11G\Datapump\log_%mydate%_%mytime%.log C:\app\Oracle11G\Datapump\%mydate%_%mytime%\
del backup_%mydate%_%mytime%.dmp