Jijo K Jose

Personal Website

Archive for August, 2013

August 9th, 2013

Database, Oracle

No Comments »

Find table names and column names inside a database using oracle

Display all table names in a database

select * from tab;

Display number of tables in a database

select count(*) from tab;

Find table exits in a database

select * from tab where tname like '%STUDENT%';

Display all column names in a database

select * from USER_TAB_COLUMNS;

Display number of columns in a database

select count(*) from USER_TAB_COLUMNS;

Find column name exits in a database

select * from USER_TAB_COLUMNS where COLUMN_NAME like '%STUDENT_ID%';

Find column name exits on a table in a database

select * from USER_TAB_COLUMNS where TABLE_NAME like '%STUDENT%' and COLUMN_NAME like 'STUDENT_ID';

Create and grant privileges to user in MySQL

Create user in MySQL

CREATE USER 'jijokjose'@'localhost' IDENTIFIED BY 'jijokjose';

Grant all privileges to user in MySQL

GRANT ALL PRIVILEGES ON *.* TO 'jijokjose'@'localhost' WITH GRANT OPTION;

Create database in MySQL

CREATE DATABASE db_vc;

Login using CMD

mysql –user=jijokjose –password=jijokjose db_name

Import database file using CMD

H:\>mysql -u jijokjose -p db_vc < H:\\db_vc.sql
Enter password: *********

Oracle SQL*Loader 4 – Load data using SQLLDR command

Now we have two files and one table named STUDENT_DATA

The Data file : std_data.dat
The Control file : std_control.ctl

If you didn't have the two files then read this post –> Oracle SQL*Loader 2 – Data and Control File

Place the data and control file inside the directory "D:\A5518\Zzz\SQL Loader". Open command prompt and navigate to "D:\A5518\Zzz\SQL Loader" directory then enter the following command.

The command used to initiate this load needs to invoke SQL*Loader and point it to the control file describing the data. In this case, the input file name is not provided in the control file, that name needs to be passed in on the command line as well. The following sqlldr command will do the job:

sqlldr <username>/<password> CONTROL=<control_file_name> DATA=<data_file_name> LOG=<log_file_name> DISCARD=<discard_file_name> BAD=<bad_file_name>

or

sqlldr jijo/kjose CONTROL=std_control DATA=std_data LOG=std_log DISCARD=std_discard BAD=std_bad

Example

D:\A5518\Zzz\SQL Loader>sqlldr jijo/kjose CONTROL=std_control DATA=std_data LOG=std_log DISCARD=std_discard BAD=std_bad

SQL*Loader: Release 11.2.0.2.0 – Production on Mon Aug 5 17:05:14 2013

Commit point reached – logical record count 9
Commit point reached – logical record count 10

D:\A5518\Zzz\SQL Loader>

Last updated by Jijo K Jose at August 9, 2013.