DBA - Tips and Queries

 To check a user status in Oracle Database run below command:  SELECT USERNAME, ACCOUNT_STATUS from dba_users where username like 'User_Name'; Example query and its output: 

To Create a user in Oracle run below command: " CREATE USER User_Name IDENTIFIED BY MyPassword; " To allow a user to connect to a Database run below command: (Connect is a Role)  " GRANT CONNECT TO User_Name; " To allow a user to connect to a Database run below command: (Create Session is a privilege ) " GRANT CREATE SESSION TO   User_Name; " To grant Select on a Table or all tables simply run below SQL Statement:  " GRANT SELECT ON TableName TO User_Name; " " GRANT SELECT ON ANY TABLE To User_Name; " To grant Table Privileges a user simply run below SQL Statement:  " GRANT SELECT,INSERT,UPDATE,DELETE ON Schema.Table TO User_Name; " To grant DBA role to a user simply run below SQL Statement:  " GRANT DBA to User_Name "

To Extract Data of a Table or query into excel, text or CSV in MySQL: If you Don't have access or availability of  MySQL Workbench, phpMyAdmin, AQUA Data Studio or any other tool or if you want to extract data from shell you can simply use below query:  mysql -h IP/Hostname -P Port  -u Username -p DatabaseName -e"SELECT * from Table1"> /Path/Result1.csv ***Note: This script is for Linux OS, for Windows OS use appropriate path (C:\Folder\File.csv)

To Enable or Disable a User in MS SQL Server: There are two ways to Enable / Disable a user in MS SQL Server 1. Using TSQL To Enable a User:  use master go ALTER LOGIN [UserName] ENABLE go To Disable a User:  use master go ALTER LOGIN [UserName] DISABLE go 2. Using SQL Server Management Studio GUI: Open SQL Server Management Studio, go to object explorer and expend logins folder as in the below image:  Double click on desired user / login or right click and go to properties as in the below image: Click on Status and Enable / Disable user / login as in the below image:

To Find MS SQL Server Database files (mdf, ndf, ldf) path use following command: In this example Database Name is [Admin]  use [Admin] go select * from sysfiles go Output of the above Query is: 

There are different Ways to Import / Export Database, Tablespace, Table, Schema in Oracle Database:   To Import Table from one Database to another follow the following steps:  1. First check Data Pump directory:  select Directory_Name, Directory_Path from dba_directories; 2. If directory is not created or you would like to change the location of directory run this:  CREATE OR REPLACE DIRECTORY DUMP_DIR AS '/disk1/folder1'; " 3. Additionally you can grant read/ write access to the user.  GRANT READ,WRITE ON DIRECTORY DUMP_DIR TO User; " 4. Export Table command (Run this command on Shell not on SQL Console) expdp SYSTEM TABLES=Schema.Table DIRECTORY=DUMP_DIR DUMPFILE=TableDumpfileName.dmp LOGFILE=Table_dumplog.log 5. To Import the dump command (Run this command on Shell not on SQL Console). This command will import the Data in same Table. Table definition on destination must be same as it is in source:  ...