Important DBA Commands (SQL Server + Oracle)
SQL Server Commands
> CREATE LOGIN test_user WITH PASSWORD = 'P@ssw0rd';
Creates a server-level login in SQL Server.
> USE TestDB;
Switches context to the database TestDB.
> CREATE USER test_user FOR LOGIN test_user;
Creates a database user mapped to a server login.
> ALTER ROLE db_datareader ADD MEMBER test_user;
Adds user to read-only role in the database.
> ALTER ROLE db_datawriter ADD MEMBER test_user;
Adds user to write-only role in the database.
> CREATE DATABASE TestDB;
Creates a new database named TestDB.
> BACKUP DATABASE TestDB TO DISK = 'D:\Backups\TestDB.bak';
Performs a full database backup.
> RESTORE DATABASE TestDB FROM DISK = 'D:\Backups\TestDB.bak' WITH REPLACE;
Restores the database from backup.
> SELECT * FROM sys.dm_exec_requests;
Displays currently running requests.
> EXEC xp_readerrorlog;
Views SQL Server error log.
> EXEC sp_MSforeachdb 'USE [?]; EXEC sp_spaceused';
Displays size of each database.
> SELECT * FROM sys.dm_db_missing_index_details;
Shows missing indexes.
> UPDATE STATISTICS TableName;
Updates statistics for query optimization.
> EXEC sp_helpdb tempdb;
Shows info about tempdb.
> SELECT @@VERSION;
Displays SQL Server version.
Oracle Commands
> CREATE USER test_user IDENTIFIED BY password;
Creates a new Oracle user.
> GRANT CONNECT, RESOURCE TO test_user;
Grants basic privileges to user.
> GRANT CREATE SESSION TO test_user;
Allows user to connect to the database.
> ALTER USER test_user IDENTIFIED BY new_password;
Changes the password of a user.
> CREATE TABLESPACE userspace DATAFILE 'userspace01.dbf' SIZE 100M AUTOEXTEND ON;
Creates a new tablespace.
> ALTER TABLESPACE userspace ADD DATAFILE 'userspace02.dbf' SIZE 100M;
Adds a datafile to a tablespace.
> ALTER DATABASE DATAFILE 'userspace01.dbf' RESIZE 500M;
Resizes a datafile.
> rman target /
Connects to RMAN utility.
> BACKUP DATABASE;
Takes a full database backup using RMAN.
> BACKUP DATABASE PLUS ARCHIVELOG;
Backs up database and archived logs.
> SELECT SID, SERIAL#, USERNAME, STATUS FROM V$SESSION;
Shows all active sessions.
> SELECT * FROM V$SESSION_LONGOPS;
Displays long-running operations.
> SELECT TABLESPACE_NAME, USED_SPACE, TABLESPACE_SIZE FROM
DBA_TABLESPACE_USAGE_METRICS;
Checks tablespace usage.
> SELECT * FROM V$VERSION;
Displays Oracle version.
> SELECT FILE_NAME, TABLESPACE_NAME FROM DBA_DATA_FILES;
Lists datafiles and their tablespaces.
> SELECT USERNAME FROM DBA_USERS;
Lists all users in the database.
