Menu

Category: Databases

How to create a user and grant permissions in Oracle
01/22/2024
| No Comments
| Oracle, PL/SQL

As always, begin by connecting to your server where Oracle is hosted, then connect to Oracle itself as the SYSTEM account. The SYSTEM account is one of a handful of predefined administrative accounts generated automatically when Oracle is installed. SYSTEM is capable of most administrative tasks, but the task we’re particularly interested in is account management. Creating a user Once connected as SYSTEM, simply issue the CREATE USER command to generate a new account. Here we’re simply creating a books_admin account that is IDENTIFIED or authenticated by the specified password. The Grant statement…

Read More

Posted in Oracle, PL/SQL Tagged Comments Off on How to create a user and grant permissions in Oracle
Add IDENTITY or Unique number to a table – T/SQL
10/03/2023
| No Comments
| Microsoft SQL, SQL, T-SQL Code

I want to update my table records. However, my existing table does not have any unique columns. So, I need to append Identity column and update all the records based on that Identity column. For example, If my temp table has 1000 records without any unique column values. I need to number all these 1000 records and update the values. There is no need to do an UPDATE. The identity column is going to be populated when it is created….

Read More

MySQL my.ini Performance and Optimization Tips
02/10/2023
| No Comments
| MySQL

The major advantage of identifying the performance-driving factor for a database is that it allows you to avoid over-provisioning and reduce costs by right-sizing your servers. It also gives you insights into whether moving data storage or adding server capacity will improve performance and, if so, how much it will be. The tuning database for MySQL query performance optimization doesn’t come with pale challenges. However, once tuned properly, the database gives worthwhile performance results with great functionalities. It lowers…

Read More

Posted in MySQL Tagged , Comments Off on MySQL my.ini Performance and Optimization Tips
MSSQL – Query to Find Column From All Tables of Database
07/14/2022
| No Comments
| Microsoft SQL

How many tables in the database AdventureWorks have column names like ‘EmployeeID’? It was quite an interesting question and I thought if there are scripts that can do this would be great. I quickly wrote down the following script which will go return all the tables containing specific columns along with their schema name.

Read More

SQL Time Zone Conversion Functions
07/13/2022
| No Comments
| Microsoft SQL

I did a lot of research on Microsoft’s website, and the only time zone conversion functions that I could find were relative to analysis services, and would not provide me with a solution to my problem. So, my options were to pay someone to modify our application, or create a process in the database environment to perform the conversions as data is inserted into the common database environment.

Read More

How can I speed up queries on a large table?
07/13/2022
| No Comments
| Microsoft SQL

Any process or action that requires querying the database and getting results can be optimized by adding database indexes. An index is used to speed up the performance of queries. It does this by reducing the number of database data pages that must be visited/scanned. In SQL Server, a clustered index determines the physical order of data in a table. There can be only one clustered index per table (the clustered index IS the table) What is an index?So,…

Read More

How to Restore a MySQL Database with Command Line
07/12/2022
| No Comments
| MySQL, SQL

In MySQL, you can use the mysql command to restore the database from a dump file. mysqldump is a command-line utility used to generate a MySQL logical database backup as a single .sql file with a set of SQL statements. The utility helps you dump MySQL tables, multiple databases, or their objects. Keep in mind that it is not possible to back up MySQL databases or data to separate .sql files with the mysqldump utility Step 1: Create the database In the command prompt,…

Read More

Posted in MySQL, SQL Tagged , Comments Off on How to Restore a MySQL Database with Command Line
Illegal mix of collations (utf8_general_ci,IMPLICIT) – MySQL
04/05/2021
| No Comments
| MySQL

— Preparations SET SESSION group_concat_max_len=4294967295; SET SESSION foreign_key_checks=0; — Set default character sets and collations ALTER DATABASE dwdata CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER DATABASE dwnotification CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER DATABASE dwsystem CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER DATABASE dwworkflowengine CHARACTER SET utf8 COLLATE utf8_general_ci; — Create statement that adjusts collations for tables in dwdata SELECT GROUP_CONCAT(CONCAT(‘ALTER TABLE ‘, TABLE_SCHEMA, ‘.’, TABLE_NAME, ‘ CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;’) SEPARATOR ‘\n’) AS “Prepared statement” FROM INFORMATION_SCHEMA.TABLES…

Read More

Posted in MySQL Tagged , , Comments Off on Illegal mix of collations (utf8_general_ci,IMPLICIT) – MySQL
© 2019 www.myw0.com . All Rights Reserved
Design www.tenemaza.com