Useful Information related to MySQL and Oracle database:

Useful Information related to MySQL and Oracle database:

This post covers the information related to mysql/oracle database.

How to drop all tables in database:

There are many alternatives were already existing to delete all the tables of schema in oracle as well as mysql. This post is to demonstrate how to achieve this with sql command.

Using Oracle: 

• Connect or login into your respective schema with credentials and execute below query to list out all the tables exists in current schema.

                SELECT 'DROP TABLE "' || TABLE_NAME || '" CASCADE CONSTRAINTS;' FROM user_tables;

• Above query will result list of sql drop down statements, Copy all those and execute to delete all the tables permanently.

Using Mysql:

• Connect or login into your respective schema with credentials and execute below query to list out all the tables exists in current schema.

      SELECT CONCAT("DROP TABLE `",table_name,"`;") FROM information_schema.tables WHERE table_schema = 'camelone';

• Above query will result list of sql dropdown statements, Copy all those and execute to delete all the tables permanently before that make sure you set the foreign key checks to 0 or disable using below query.

            SET FOREIGN_KEY_CHECKS = 0;

How to check table space list in Oracle:

We can use the below query to list out all the table space of database in oracle.

   SELECT TABLESPACE_NAME "TABLESPACE",
   INITIAL_EXTENT "INITIAL_EXT",
   NEXT_EXTENT "NEXT_EXT",
   MIN_EXTENTS "MIN_EXT",
   MAX_EXTENTS "MAX_EXT",
   PCT_INCREASE
   FROM DBA_TABLESPACES;

To drop table space in Oracle:

If we want to delete table space, Use the below query

   DROP TABLESPACE CAMELONE_CORE_TS 

    INCLUDING CONTENTS 

    CASCADE CONSTRAINTS;

To restore dump to MySQL database using command prompt:

If you want to restore the dump file using  using command line use the below query.
  mysql -u username -p database_name < filename.sql

To restore dump in Oracle using any oracle support tool

@your local path pointing to the dump file
For ex: @D:\my-dump.sql

To Rename table from lower case to Upper Case:

SELECT distinct CONCAT('RENAME TABLE ', TABLE_NAME, ' TO ' , UPPER(TABLE_NAME), ';') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'camelone';

To Set SQL Safe Update:

SET SQL_SAFE_UPDATES = 0;

To Check the table reference:

Select table_name
from information_schema.KEY_COLUMN_USAGE
where table_schema = 'camelone'
and referenced_table_name = 'T_CORE_URI';

To Change the Port of Oracle XE:

Exec DBMS_XDB.SETHTTPPORT(3010);

 To Set Max Allowed Packet in MySql:

show variables like 'max_allowed_packet';
SET GLOBAL max_allowed_packet=1024*1024*1024; 
SET SESSION max_allowed_packet=1024*1024*1024; 

Data Source Configuration in Web Server - Apache tomcat:

While working with Web application and Apache tomcat as server, To connect and point to the database like mysql or oracle, Data source configuration in context file of application server is one of the best option.

You can find the context.file under the config folder of apache tomcat and place the below code between the context elements.

For MySQL:

<Resource acquireRetryAttempts="30" auth="Container" checkoutTimeout="180000" driverClass="com.mysql.jdbc.Driver" factory="org.apache.naming.factory.BeanFactory" idleConnectionTestPeriod="300" initialPoolSize="8" jdbcUrl="jdbc:mysql://localhost:3306/SchemaName" maxIdleTime="0" maxPoolSize="10" name="jdbc/SchemaName" password="password" preferredTestQuery="select 1" type="com.mchange.v2.c3p0.ComboPooledDataSource" user="root"/>

Note: Replace SchemaName with your schema name or instance name along with user as well as password.

For Oracle:

<Resource accessToUnderlyingConnectionAllowed="true" auth="Container" connectionProperties="SetBigStringTryClob=true" driverClassName="oracle.jdbc.OracleDriver" logAbandoned="true" maxActive="2" maxIdle="1" maxTotal="3" maxWait="1" maxWaitMillis="10000" name="jdbc/schema-name" password="password" removeAbandoned="true" removeAbandonedTimeout="90" type="javax.sql.DataSource" url="jdbc:oracle:thin:@localhost:1521/DataBaseName" username="SchemaName"/>

Note: Replace SchemaName with your schema name or instance name, DataBaseName to your installed data base name.
                        

To drop and create users in Oracle using command prompt:

alter session set "_ORACLE_SCRIPT"=true;  

drop user camelone cascade;

create tablespace CAMELONE_CORE_TS DATAFILE 'temp_core.dat' SIZE 100M REUSE AUTOEXTEND ON;

create user camelone identified by password default tablespace CAMELONE_CORE_TS;

grant dba,connect,resource to camelone;