Showing posts from October, 2022
Check the invalid objects and generate script for compiling SELECT owner, 'alter ' || REPLACE( object_type, ' BODY', '') || ' ' || own…
Recompiling All Invalid Objects in Oracle Check the invalid objects and generate script for compiling SELECT owner, 'alter ' || REPLACE( object_type, ' BODY'…
Exception handling in MySQL Stored Procedures -- Replace the table name,columns names according to your requirement -- drop procedure sp_med_master_del DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_med_master_del`…
Delete Duplicate rows in a table in Oracle -- replace table name and column names according to your requirement DECLARE CURSOR C1 IS select phone_no,service_type,count(1) from nz_wkglines_ftth group by phone_no,service_t…
Database size of MySQL SELECT s.schema_name, CONCAT(IFNULL(ROUND((SUM(t.data_length) + SUM(t.index_length)) / 1024 / 1024, 2), 0.00), 'Mb') total_size, CONCAT(IFNULL(ROUN…
How to store Latitude and Longitude in MySQL CREATE TABLE `test_lat_long` ( `lat_long` POINT NOT NULL, SPATIAL INDEX `SPATIAL` (`lat_long`) ) ; INSERT INTO `test_lat_long` (`lat_long`) VALUES (POINT(40.71727401,-74.00898606)); SELECT * FROM te…
how to Manage sequences in MySQL use <db>; CREATE TABLE sequence (id INT NOT NULL); INSERT INTO sequence VALUES (0); select * from sequence; set sql_safe_updates=0; UPDATE sequence SET id=LAST_INSERT_ID(id+1); SELECT LAST_INSERT_ID();
Rename a database in MySQL 1 : take backup of the database to be renamed mysqldump -u root -p -R school_dev > school_dev.sql 2 : in the backup file Replace <UserName> and <Password> with the actual credentials for the database, and…
Batch file Script for taking MySQL backup of a database 1. Open notepad copy and paste the below code in blue color and save the file as "backup.bat" Change the path of MYSQL location according to your system where MySQL is installed …
