Install the mysql workbench 8.0 and follow the below steps after connecting to your database Step1 : Below is the screen which you will get after connecting to your database instance. Highlighted is the schema which needs to be exported into a file (.sql file) Step2 : Click on left tab “Administration” and then select the “Data Export” Step3 : Select the database and files which you would like to export. Now select the file name and location and click Start Export
Category: Database
ACID Properties – DBMS Transaction
ACID Properties are considered to be the desirable properties of any transactions.When in a database system more than one transaction are being executed simultaneously and in parallel then system maintains the below properties in order to ensure the data accuracy and integrity. A-Atomicity if something unexpected happens in the middle of the transaction, then it should be completely undone so that the system will go back to it previous known good state. If the transaction is successful, then it must be committed , so that the system will go to the new state. C-Consistency Ensures the system is in a consistent stage,even though the transaction is committed or rolled back completely. I-Isolation Isolation makes sure that the data that are being manipulated are isolated, so that no other transactions / processes can manipulate them while it is modified by another transaction. D-Durability Durability indicates that, once the transaction is committed,… Read more“ACID Properties – DBMS Transaction”
Using DUAL table in Oracle
DUAL is a special table found in some of the databases e.g Oracle, Informix and DB2 etc. MySQL, Microsoft SQL (MS SQL) does not have this table moreover MySQL allows DUAL to be specified as a table in queries that do not need data from any tables.In oracle, this DUAL table is having one row and one column by default. The owner of DUAL is sys, can be accessed by all user. The table has a single VARCHAR2(1) column called DUMMY that has a value of ‘X’.This table was created by Charles Weiss of Oracle corporation to provide a table for joining in internal views. We mostly uses this to find the calculations or checking system variables etc. Examples 1. The following query displays the string value from the DUAL table SELECT ‘Test String’ FROM DUAL; Output: Test String ———– Test String 2. The following query displays the numeric value… Read more“Using DUAL table in Oracle”
How to copy one schema into another using SQL
Some times we don’t have the DBA access and need to copy data from one schema to another. You can perform that by executing migration SQL script in Oracle XE. Step 1. Generate migration script. SELECT table_name, (‘Select ”’||TABLE_NAME||”’ as tbl from dual;’ ||CHR(10)||’delete from <TARGET_SCHEMA>.’||TABLE_NAME||’;’||CHR(10)|| ‘insert into <TARGET_SCHEMA>.’||TABLE_NAME|| ‘ select * from <SOURCE_SCHEMA>.’||TABLE_NAME||’@<XE_CONNECTION>;’) as GENERATED_SQL FROM all_tables where Owner='<SOURCE_SCHEMA>’ Order By TABLE_NAME; Step 2. Run Below script by updating Source and Target connection details WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK connect system/password@xe — Where system is Oracle XE root user ID drop PUBLIC DATABASE LINK <XE_CONNECTION>; CREATE PUBLIC DATABASE LINK <XE_CONNECTION> CONNECT TO <SOURCE_USER_NAME> IDENTIFIED BY “PASSWORD” USING <SOURCE_CONNECTION_NAME>; set sqlprompt ”; — ———– Echo ON / autocommit OFF for the logged in session set autocommit off; set echo off; connect <TARGET_SCHEMA>/password@<XE_CONNECTION> set echo on; — Add SQL generated from Step 1 — Step 3. Execute the script. and commit… Read more“How to copy one schema into another using SQL”
How to find index or constraint in Oracle?
I am using liferay and many times I see the error stating index name or constraint names SYS_C00381400 or IX_C7057FF7. But don’t know how to find the actual table impacted so that I can fix it.
Changing the forgotten password of System
Use the below SQL query by logging with sysdba account, alter user system identified by “newpassword”;
SQL query to search in CLOB field
You can use below SQL query to search a string in CLOB object stored in database, select * from tablename where dbms_lob.instr(clob column name , ‘search string’ )>0;
Recover oracle database from error ORA-00333
To restore the oracle from ORA-00333: redo log error , you need to follow the below steps, SQL> connect system Enter password: ERROR: ORA-01033: ORACLE initializatio Process ID: 0 Session ID: 0 Serial number: 0 ORA-00333: redo log read error block 767 count 7428 SQL>connect sys as sysdba password SQL> startup SQL>select l.status, member from v$logfile inner join v$log l using (group#); SQL>recover database using backup controlfile; Enter and provide the current file path – /oracle/fast_recovery_area/redo01.log SQL>alter database open resetlogs;