FANDOM


Database General

Database Comparison

Schema Object Support Comparison

Table View Sequence Function Procedure Package Datalink
Oracle
SQL Server
MySQL
PostgreSQL

Constraint and Index Support Comparison

Primary Key Foreign Key Alternative Key Not Null Constraint Default Constraint Check Constraint Unique Index Bitmap Index
Oracle Y Y Y Y Y Y Y Y
SQL Server Y Y Y Y Y Y Y Y
MySQL Y Y N Y Y N Y N
PostgreSQL

Identifiers Length Comparison

Database Table View Sequence Synonym Index Constraint Trigger Procedure, Function Database Link Column Alias
Oracle 8 30 30 30 30 30 30 30 30 128 - -
MySQL 5.0 64 64 64 - 64 64 - 64 64 - 64 255
PostgreSQL 8.x 63 63 63 63 - 63 63 63 63 63 63 63

Datatype

String Data

MySQL

  • The CHAR and VARCHAR Types
    • Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 65,535. The effective maximum length of a VARCHAR is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.
  • The BLOB and TEXT Types

Temporal Data

MySQL

Constraints

Check Constrain

Transaction

SQL

Articles

Tips and Tricks

Get simplest timestamp string in 'yyyyMMddHHmmss' format

  • SQL Sever
    • SELECT REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(19),getdate(),120),'-',''),':',''),' ','')
      SELECT format(getdate(),'yyyyMMddHHmmss') -- SQL Server 2012 or higher
    • CAST and CONVERT function
    • FORMAT function
  • Hive
    • SELECT from_unixtime(unix_timestamp(), 'yyyyMMddHHmmss') AS now

Oracle

Readings

References

Articles

Administration
Application Development
  • SQL*Net - Oracle FAQ
    • Oracle's networking software that allows remote data-access between programs and the Oracle Database, or among multiple Oracle Databases.
Performance Tuning
misc

Tips and Tricks

Listing all schemas or all objects in a schema

In the case that you've logged in a database, but you have entirely no idea what the database is like, you may need the following query first.

SELECT username FROM dba_users

And then, you may need the next query on the schema you want to know more.

SELECT DISTINCT owner, object_type, object_name FROM dba_objects WHERE owner = 'HR'

If you don't have DBA privilege, just use all_object table.

SELECT DISTINCT owner, object_type, object_name FROM all_objects WHERE owner = 'HR'

Finding roles and privileges of a user or current session.

With more emphasis on securities, default privileges for a regular(non-DBA) user is very restrictive. As of Oracle database 10.2, CONNECT and RESOURCE which are typical predefined roles for a regular user don't include create view and create synonym privileges.

So often, you should have to check what roles and privileges are given for a user. You can do that using the following statements.

 -- finding all roles for the current session
 SELECT * FROM SESSION_ROLES;
 
 -- finding all privileges for the current session
 SELECT * FROM SESSION_PRIVS;

Required roles to access dynamic performance (V$) views.

To access the dynamic performance views such as V$SESSION, V$PARAMETER and V$LOCK as well as static dictionary views of DBA such as DBA_TABLES, DBA_SEQUENCES and DBA_SYNONYMS, the user need SELECT_CATALOG_ROLE

DBA user can grant the role to the user using the following statement.

 GRANT select_catalog_role TO :username;

Finding the values of parameters currently in effect.

Checking the context of current operation is really one of the most basic thing you can do in any circumstances. With Oracle, that is parameters. You can identify the current value of SGA max size, max sessions, optimizer mode or other settings throughout the parameter.

 SELECT * FROM V$PARAMETER ORDER BY name;

V$PARAMETER is one of public synonyms in default installation.

Identifying the character-set of the Oracle instance

Use the following statement.

 SELECT * FROM nls_database_parameters
 WHERE parameter = 'NLS_CHARACTERSET';

Granting minimum privileges to a user

The default roles or privileges granted to a user right after the create varies with versions of Oracle database. As an increasing emphasis of security, the default roles or privileges seems to reduced at the more newer versions.

For a owner of a specific schema, the minimum privileges required for the user to build schema objects and process statements are the followings

CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SEQUENCE,
CREATE SYNONYM, CREATE PUBLIC SYNONYM, CREATE PROCEDURE, CREATE TRIGGER,
UNLIMITED TABLESPACE

To grant those privileges to a user, use the following statement

GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, -
   CREATE SYNONYM, CREATE PUBLIC SYNONYM, CREATE PROCEDURE, CREATE TRIGGER, UNLIMITED TABLESPACE TO :USER;

Changing the password of the user

Use the following statement.

 ALTER USER :username IDENTIFIED BY :password;

Be cautious not to mark single quotation mark around values of :username and :password. The following is simple example.

 ALTER USER hr IDENTIFIED BY hr88rh;

When you are connected to Oracle using SQL*Plus, you can use password command to change your password.
The following is sample command line when changing password of hr user

 SQL>password
 Changing password FOR hr
 OLD password :
 NEW password :
 Retype NEW password :
 Password changed

Viewing information about users

About current user, access the following dictionary views.

View Description
USER_USERS Describes only the current user.
ALL_USERS Lists users visible to the current user, but does not describe them.
USER_TS_QUOTAS Describes tablespace quotas for current user.
USER_RESOURCE_LIMITS Displays the resource limits for the current user.
USER_PASSWORD_LIMITS Describes the password profile parameters that are assigned to the user.

More privileged users can use the following views to access the information of all users.

View Description
DBA_USERS Describes all users of the database.
DBA_TS_QUOTAS Describes tablespace quotas for users.
DBA_PROFILES Displays all profiles and their limits.
V$SESSION Lists session information for each current session, includes user name.

Use connect command of SQL*Plus without tnsnames.ora

Identifying the sessions currently logged-on

You need select_catalog_role and execute the following query

SELECT sid, serial#, username, STATUS, schemaname, machine, terminal, program, TYPE, logon_time
FROM V$Session
WHERE TYPE <> 'BACKGROUND'
ORDER BY logon_time DESC;

Identifying execution plan of query

Using DBMS_XPLAN.DISPLAY_CURSOR function

The execution plan of last executed query in plan table can be printed out using DBMS_XPLAN.DISPLAY_CURSOR function.

DBMS_XPLAN package was introduced at Oracle 9i, but DISPLAY_CURSOR function was added with Oracle 10g.
DBMS_XPLAN.DISPLAY_CURSOR function accesses V$SESSION, so the user need to have SELECT_CATALOG_ROLE role to correctly execute select * from table(DBMS_XPLAN.DISPLAY_CURSOR)

SELECT employee_id, first_name || ' ' || last_name AS name, department_id, job_id, manager_id
FROM hr.employees
WHERE job_id = 'SA_REP'
AND manager_id IN (146, 147);
 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

If you want to see just execution plan and not to actually execute the query. You can use EXPLAIN PLAN statement.

EXPLAIN plan FOR
SELECT employee_id, first_name || ' ' || last_name AS name, department_id, job_id, manager_id
FROM hr.employees
WHERE job_id = 'SA_REP'
AND manager_id IN (146, 147);
 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

Using autotrace with SQL*Plus

When using SQL*Plus, you can turn on autotrace feature for the current session using SET AUTOTRACE ON command.

After the autotrace is enabled, execution of query would display normal result data and the execution plan right after it.

To use this feature, you must create a PLAN_TABLE table in your schema and then have the PLUSTRACE role granted to you.

SQL>SET AUTOTRACE ON
SQL>SELECT employee_id, first_name || ' ' || last_name AS name, department_id, job_id, manager_id
2 FROM hr.employees
3 WHERE job_id = 'SA_REP'
4 AND manager_id IN (146, 147);

If you want to see just execution plan and not to actually execute the query, you can use SET AUTOTRACE TRACEONLY command.

SQL>SET AUTOTRACE TRACEONLY
SQL>SELECT employee_id, first_name || ' ' || last_name AS name, department_id, job_id, manager_id
2 FROM hr.employees
3 WHERE job_id = 'SA_REP'
4 AND manager_id IN (146, 147);

Readings

MySQL

Readings

References

Server Command Options
Option Description Remarks
--defaults-file=file_name Read only the given option file. If the file does not exist or is otherwise inaccessible, an error occurs.
SQL Statement Syntax
MySQL 5.6 SQL Syntax
Statement Description Remarks
SHOW provide information about databases, tables, columns, or status information about the server
SET
GRANT grants privileges to MySQL user accounts
MySQL 5.0 SQL Syntax

Readings

Tips and Tricks

For developers

After log-in on unfamiliar database

To identify the server address, port and current user, use status command.

mysql>STATUS

To identify the databases available, use SHOW DATABASES statement.

mysql>SHOW DATABASES;

To identify the privileges granted to the current user, use SHOW GRANTS statement.

mysql>SHOW GRANTS FOR CURRENT_USER;

To change the password for the current user, use SET PASSWORD statement and password function.

mysql>SET PASSWORD = PASSWORD('cleartext password');

To identify all the tables defined in current schema, use 'SHOW TABLES statement. If no schema is assigned yet to current session, execute USE statement before.

mysql>USE myschema;
mysql>SHOW TABLES;
mysql>SHOW COLUMNS IN mytable;

To list all the users of current MySQL instance

mysql> pager less -SFX
mysql> SELECT * FROM mysql.USER;

For administrators

Adding an account
mysql>CREATE USER 'sample'@'%' IDENTIFIED BY 'password';
mysql>GRANT ALL ON sample.* TO 'sample'@'%';

GRANT ALL would exclude GRANT OPTION privilege. For more, refer Adding User Accounts section of MySQL manual.

Installation

Typical sample of my.cnf
[mysqld]
port            = 3306
#socket         = /tmp/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

datadir=D:\home\mysql\5.0\data/
character-set-server=utf8
character-set-filesystem=utf8
collation-server=utf8_general_ci
default-storage-engine=innodb

innodb_data_file_path = ibdata1:30M:autoextend
MySQL as a Service on Windows
  • Register MySQL as a Windows service
C:\>"C:\servers\mysql-5.0.96\bin\mysqld" --install-manual MySQL --defaults-file=D:\home\mysql\5.0\my.cnf
  • Start service
C:\>sc start MySQL
  • Stop service
C:\>sc stop MySQL

MariaDB TX

References

mysqld Options

Option Description Remarks
--bootstrap Used by mysql installation scripts, such as mysql_install_db to execute SQL scripts before any privilege or system tables exist Do no use while an existing MariaDB instance is running

Readings

Galera Cluster

  • http://galeracluster.com/
  • Desc. : a synchronous multi-master database cluster, based on synchronous replication and Oracle’s MySQL/InnoDB
  • License :
  • Sources :

Readings

Microsoft SQL Server

Readings

References

SQL Server 2012
View/Function Description
sys.databases Contains one row per database in the instance of Microsoft SQL Server
sys.schemas Contains a row for each database schema
sys.tables Returns a row for each table object
sys.columns Returns a row for each column of an object that has columns, such as views or tables
sys.indexes Contains a row per index or heap of a tabular object, such as a table, view, or table-valued function
sys.configurations Contains a row for each server-wide configuration option value in the system
View/Function Description
sys.dm_exec_sql_text Returns the text of the SQL batch that is identified by the specified sql_handle.
sys.dm_exec_query_plan Returns the Showplan in XML format for the batch specified by the plan handle.
sys.dm_exec_cached_plans Returns a row for each query plan that is cached by SQL Server for faster query execution.
sys.dm_exec_query_stats Returns aggregate performance statistics for cached query plans in SQL Server.
sys.dm_exec_sessions Returns one row per authenticated session on SQL Server.
sys.dm_exec_connections Returns information about the connections established to this instance of SQL Server and the details of each connection.

Readings

Configuration
SQL Tuning
Partitioning
Editions

Tips and Tricks

For developers

Changing the locale of current session
set language N'English';
Identifying the databases available
select * from master.dbo.sysdatabases;
Listing all the server configuration options
select * from sys.configurations;
Listing all the database-level options
select * from sys.databases;

For administrators

Listing all configuration
SELECT "name", minimum, maximum, "value", value_in_use, "description", is_dynamic, is_advanced
FROM sys.configurations ORDER BY name;
Listing all seesions
select * from sys.dm_exec_sessions
where is_user_process = 1
order by host_name, program_name, login_name
Checking total sizes and available spaces of database files
select file_id as [File ID]
 , name as [File Name]
 , physical_name as [Physical Name]
 , size/128.0 as [Total Size(MB)]
 , size/128.0 - cast(fileproperty(name, 'spaceused') as int)/128.0 as [Available Space(MB)]
from sys.database_files
with (nolock)
option (recompile);
Checking the usage of transaction log file
DBCC SQLPERF(LOGSPACE);

SQL tuning

Identifying the execution plan of dynamic query
set statistics profile on; -- or set statistics xml on

exec sp_executesql
 @statement = N'select * from visitor where (valid_from < @from or valid_to > @to)',
 @parameters = N'@from varchar(14), @to varchar(14)',
 @from = '201602010000',
 @to = '201601010000';

set statistics profile off; -- or set statistics xml off

More concisely

set statistics profile on; -- or set statistics xml on

exec sp_executesql
 N'select * from visitor where (valid_from < @from or valid_to > @to)',
 N'@from varchar(14), @to varchar(14)',
 @from = '201602010000',
 @to = '201601010000';

set statistics profile off; -- or set statistics xml off
Querying cached execution plans of the queries
select cp.refcounts
, cp.usecounts
, cp.objtype
, cp.cacheobjtype
, st.dbid
, st.objectid
, st."text"
, qp.query_plan
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text (cp.plan_handle) st
cross apply sys.dm_exec_query_plan (cp.plan_handle) qp
where st.dbid in (select dbid from master.dbo.sysdatabases where name in ('master', 'MYDB'))
and cp.objtype = 'prepared'
order by cp.usecounts desc;
Finding CPU consuming statements
SELECT (
  SELECT "name" FROM master.dbo.sysdatabases WHERE dbid = st.dbid) AS "Database"
  , st."text" AS "Query"
  , qs.creation_time AS "Createion time"
  , qs.execution_count AS "Executions"
  , datediff(HOUR, qs.creation_time, getdate()) AS "Hours"
  , "Executions/Hour" = CASE
      WHEN datediff(HOUR, qs.creation_time, getdate()) = 0 THEN qs.execution_count
      ELSE qs.execution_count/datediff(HOUR, qs.creation_time, getdate())
    END
  , FLOOR(qs.total_worker_time/1000) AS "Total Worker Time(ms)"
  , FLOOR(qs.total_worker_time/qs.execution_count/1000) AS "Avg. Worker Time(ms)"
  , "Worker Time(ms)/Hour" = CASE
      WHEN datediff(HOUR, qs.creation_time, getdate()) = 0 THEN FLOOR(qs.total_worker_time/1000)
      ELSE FLOOR(qs.total_worker_time/datediff(HOUR, qs.creation_time, getdate())/1000)
    END
  , FLOOR(qs.total_elapsed_time/1000) AS "Total Elapsed Time(ms)"
  , FLOOR(qs.total_elapsed_time/qs.execution_count/1000) AS "Avg. Elapsed Time(ms)"
  , FLOOR((qs.total_elapsed_time - qs.total_worker_time)/qs.execution_count/1000) AS "Avg. Time Delta(ms)"
  , FLOOR(qs.total_rows/qs.execution_count) AS "Avg. Rows"
  , st.encrypted AS "Encrypted"
  , qp.query_plan AS "Query Plan"
  FROM (SELECT top 50 qs.plan_handle, qs.total_worker_time, qs.execution_count, qs.creation_time, qs.total_elapsed_time, qs.total_rows
    FROM sys.dm_exec_query_stats AS qs
    ORDER BY qs.total_worker_time DESC) AS qs
  CROSS apply sys.dm_exec_sql_text(qs.plan_handle) AS st
  CROSS apply sys.dm_exec_query_plan (qs.plan_handle) AS qp
  WHERE st.dbid IN (SELECT dbid FROM master.dbo.sysdatabases WHERE name IN ('master', 'MYDB')
  AND qs.execution_count > 0
  )
ORDER BY "Worker Time(ms)/Hour" DESC;
Finding worker/elapsed time of a specific query
SELECT (SELECT "name" FROM master.dbo.sysdatabases WHERE dbid = st.dbid) AS "Database"
   , st."text" AS "Query"
   , qs.creation_time AS "Createion time"
   , qs.execution_count AS "Executions"
   , datediff(HOUR, qs.creation_time, getdate()) AS "Hours"
   , "Executions/Hour" = CASE
       WHEN datediff(HOUR, qs.creation_time, getdate()) = 0 THEN qs.execution_count
       ELSE qs.execution_count/datediff(HOUR, qs.creation_time, getdate())
     END
   , FLOOR(qs.total_worker_time/1000) AS "Total Worker Time(ms)"
   , FLOOR(qs.total_worker_time/qs.execution_count/1000) AS "Avg. Worker Time(ms)"
   , FLOOR(qs.last_worker_time/1000) AS "Last Worker Time(ms)"
   , "Worker Time(ms)/Hour" = CASE
       WHEN datediff(HOUR, qs.creation_time, getdate()) = 0 THEN FLOOR(qs.total_worker_time/1000)
       ELSE FLOOR(qs.total_worker_time/datediff(HOUR, qs.creation_time, getdate())/1000)
     END
   , FLOOR(qs.total_elapsed_time/1000) AS "Total Elapsed Time(ms)"
   , FLOOR(qs.total_elapsed_time/qs.execution_count/1000) AS "Avg. Elapsed Time(ms)"
   , FLOOR(qs.last_elapsed_time/1000) AS "Last Elapsed Time(ms)"
   , FLOOR((qs.total_elapsed_time - qs.total_worker_time)/qs.execution_count/1000) AS "Avg. Time Delta(ms)"
   , FLOOR(qs.total_rows/qs.execution_count) AS "Avg. Rows"
   , st.encrypted AS "Encrypted"
   , qp.query_plan AS "Query Plan"
   FROM sys.dm_exec_query_stats AS qs
   CROSS APPLY (
      SELECT * FROM sys.dm_exec_sql_text (qs.plan_handle) AS st
      WHERE st.text LIKE '%string_in_qeury%') AS st
   CROSS APPLY sys.dm_exec_query_plan (qs.plan_handle) AS qp
   WHERE st.dbid IN (SELECT dbid FROM master.dbo.sysdatabases WHERE name IN ('master', 'MYDB')
   AND qs.execution_count > 0)
ORDER BY "Worker Time(ms)/Hour" DESC;
Finding IO consuming statements
select (select "name" from master.dbo.sysdatabases where dbid = st.dbid) as "Database"
, st."text" as "Query"
, qs.creation_time as "Createion Time"
, qs.execution_count as "Executions"
, datediff(hour, qs.creation_time, getdate()) as "Hours"
, "Executions/Hour" = case
    when datediff(hour, qs.creation_time, getdate()) = 0 then qs.execution_count
    else qs.execution_count/datediff(hour, qs.creation_time, getdate())
  end
, (qs.total_logical_reads/qs.execution_count) as "Avg. Logical Reads"
, (qs.total_logical_writes/qs.execution_count) as "Avg. Logical Writes"
, (qs.total_physical_reads/qs.execution_count) as "Avg. Physical Reads"
, "Logical IOs/Hour" = case
    when datediff(hour, qs.creation_time, getdate()) = 0 then (qs.total_logical_reads + qs.total_logical_writes)
    else floor((qs.total_logical_reads + qs.total_logical_writes)/datediff(hour, qs.creation_time, getdate()))
  end
, st.encrypted as "Encrypted"
, qp.query_plan as "Query Plan"
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(qs.plan_handle) as st
cross apply sys.dm_exec_query_plan(qs.plan_handle) as qp
where st.dbid in (select dbid from master.dbo.sysdatabases where name in ('master', 'MYDB'))
order by "Logical IOs/Hour" desc;
Finding plans with missing indexes
SELECT cp.usecounts
, cp.refcounts
, cp.objtype
, cp.cacheobjtype
, st.dbid
, st.text
, qp.query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
WHERE st.dbid = (SELECT dbid FROM master.dbo.sysdatabases WHERE name='MYDB')
AND qp.query_plan.exist(N'/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup') <> 0
ORDER BY cp.usecounts DESC;
Finding plans with implicit conversion warning
WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT qs.query_hash
, qs.query_plan_hash
, ConvertIssue = operators.value('@ConvertIssue', 'nvarchar(250)')
, Expression = operators.value('@Expression', 'nvarchar(250)')
, qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
CROSS APPLY query_plan.nodes('//Warnings/PlanAffectingConvert') rel(operators);

Sybase

HSQLDB

  • Concepts
    • Catalog = Information Schema, Schema*
    • Schema = Object*
  • "In HyperSQL, there is only one catalog per database. The name of the catalog is PUBLIC."

SQLite

References

Command Syntax Description Remarks
.open .open ?OPTIONS? ?FILE? Close existing database and reopen the specified FILE.
.tables .tables ?TABLE? List names of tables.
.schema .schema ?PATTERN? ?--indent? Show the CREATE statements matching PATTERN.

Sample SQL Statements

Query

sqlite> .OPEN ledger.db
 
sqlite> .TABLES
 
sqlite> .schema ledgers --indent
 
sqlite> SELECT COUNT(*) FROM ledgers;
 
sqlite> SELECT ledgerhash, ledgerseq FROM ledgers LIMIT 10;
 
sqlite> .OPEN transactions.db
 
sqlite> .TABLES
 
sqlite> .schema transactions --indent
 
sqlite> SELECT COUNT(*) FROM transactions;
 
sqlite> SELECT transid, transtype, fromseq, ledgerseq FROM transactions ORDER BY ledgerseq ASC, fromseq ASC LIMIT 200 offset 300;

Microsoft Access

misc

Sample Schemas and Data