In Oracle, PostgreSQL, DB2 and SQLite database system, there is a nice command feature called Create Table As which allows easy duplicating of a table with data from another or a few other tables. The SQL command can also be used to ‘copy and paste’ a table exactly or backup a table data before performing data manipulation query on the original table, just in case the script has error and the backup date can be used to restore to original state. Create Table As will creates a new table built from the content of dataset or result set retrieved by a Select SQL query from a table that already exists within the database.

Continue reading Easily Duplicate, Copy or Backup Tables in Oracle, PostgreSQL, DB2 and SQLite with Create Table As SQL » » »

If you enable slow queries log of MySQL database server, you will notice that from the slow query log file contains only SQL statements and queries that took more than query_time of 10 seconds to execute or run. This is the default value or query time threshold for log_slow_queries function, which is 10 seconds. 10 seconds is way too long for a SQL query to complete, so the value may be too high to capture any meaningful data. You can alter or change the slow query time to lower value or number of seconds.

Continue reading Change or Set MySQL Long Query Time Value for log-slow-queries » » »

One of the main requirements for a fast web server is to has efficient and effective SQL statements or queries that are optimized. Any non-optimal SQL (Structured Query Language) commands or statements that take too long or lengthy time to execute will use up a lot of system resources, causing MySQL database to run slower, and then more and more queries backlogs queuing up, and when connection limit is reached, visitors are been denied or refused connection. In worst case scenario, your web server will go down as well, or continuously underperform. The case is especially true when you are using MyISAM table type which uses table-level locking instead of row-level locking in a high traffic website.

Continue reading Enable Logging of Slow Queries (Slow Query Log) in MySQL Database » » »

Oracle Package is an object in Oracle database that allows developers and designers to group procedures and functions into common groups, typically based upon their functionality. Package can be treated as the container for functions and procedures. A package consists of two sections which is the package specification (export script file will be in .pks extension) which must be declared first and contains a definition of any objects that can be referenced from outside of the package. The other section is a package body (export SQL script file will be in .pkb extension) that contains the implementation of the objects or stored procedures within the package. With package, a procedure can be hidden inside the package by not declaring them in the package specification.

Continue reading Delete, Remove or Drop Oracle Stored Packages with Drop Package » » »

Sometime when you compile an object or package or view in Oracle, or execute some PL/SQL statements on Oracle table, or when running an Oracle program or application, the following error my occurs:

ORA-00942: table or view does not exist

Continue reading ORA-00942 Table or View Does Not Exist Oracle Error » » »

When inserting records into Oracle database by SQL statements, creating new tables, importing backup dump into Oracle database or when manipulating tables or data in the Oracle database, the following error may occurs:

ORA-01658: unable to create INITIAL extent for segment in tablespace tablespace_name

Continue reading Oracle ORA-01658 Unable to Create INITIAL Extent for Segment in Tablespace Error » » »

Oracle table partitioning is important for optimum performance of the database, as it allows Oracle to process the specific and independat partition or part that is smaller in size, instead of entire full table or database. However, when you want to create a new partition to a table by adding or splitting partition, administrator may encounter errors such as ORA-14074. Other than that, Oracle error ORA-14080 can also happen, if you’re not careful enough with the following symptom:

ORA-14080: partition cannot be split along the specified high bound

Continue reading Create, Add or Split Oracle Database Partition Fails with ORA-14080 Error » » »

Oracle database partition is a division or decomposition of a logical database and/or its constituting elements (indexes or transaction logs) into distinct, smaller, more manageable and independent parts or pieces. Database partitioning provides solution to problems faced by supporting very large tables and indexes, especially in data warehouse applications, which store and analyze large amounts of historical data. With partitioning or table splitting, SQL statements can access and manipulate the partitions rather than entire tables or indexes, and thus increase manageability, performance or availability of the Oracle database.

Continue reading Oracle ORA-14074 Create or Add New Partition Fails Error » » »