Thursday, May 29, 2025

Process to import SAKILA Schema in Oracle database

 #####Create a directory for sakila files

mkdir /u01/app/oracle/sakila

cd /u01/app/oracle/sakila

------------------------------------------------------------------------------------------------------------------

#####Download the schema file

wget https://raw.githubusercontent.com/DataGrip/dumps/master/oracle-sakila-db/oracle-sakila-schema.sql


#####Download the data file

wget https://raw.githubusercontent.com/DataGrip/dumps/master/oracle-sakila-db/oracle-sakila-insert-data.sql


#####verify downloaded files

ls -la /home/oracle/sakila/


-------------------------------


OR,

download the file named (oracle-sakila-schema.sql and oracle-sakila-insert-data.sql) from the link below:

https://github.com/DataGrip/dumps/tree/master/oracle-sakila-db


and move the file to location /u01/app/oracle/sakila


---------------------------------------------------------------------------------------------------------------------


#####Connect to Oracle as SYS


sqlplus sys as sysdba 


#####Create Sakila User and Tablespace


sql-- Create tablespace for Sakila (using ASM)


CREATE TABLESPACE sakila_tbs

DATAFILE '+DATA' SIZE 200M

AUTOEXTEND ON NEXT 20M MAXSIZE 2G;


#####Find default temporary tablespace

SELECT property_value 

FROM database_properties 

WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';


-- Create the sakila user

CREATE USER sakila IDENTIFIED BY sakila

DEFAULT TABLESPACE sakila_data

TEMPORARY TABLESPACE temp_new;


-- Grant necessary privileges

GRANT CONNECT, RESOURCE TO sakila;

GRANT CREATE VIEW TO sakila;

GRANT UNLIMITED TABLESPACE TO sakila;


-- Exit from SYS

EXIT;

------------------------------------------------------------------------------------------------------------------------------------

#####Connect as Sakila User


sqlplus sakila/sakila


#####Run the Schema Creation Script


IN SQL PROMPT-- Set the directory path where your files are located


@/u01/app/oracle/sakila/oracle-sakila-schema.sql


Wait for this to complete - you'll see tables, indexes, and constraints being created.


#####Run the Data Insertion Script


IN SQL PROMPT-- Insert the sample data


@/u01/app/oracle/sakila/oracle-sakila-insert-data.sql


This will take a few minutes - you'll see INSERT statements executing.


--------------------------------------------------------------------------------------------------------------------------------------

#####Verify the Installation


-- Check if all tables were created

SELECT table_name FROM user_tables ORDER BY table_name;


-- Check row counts for major tables

SELECT 'ACTOR' as table_name, COUNT(*) as row_count FROM actor

UNION ALL

SELECT 'FILM', COUNT(*) FROM film

UNION ALL

SELECT 'CUSTOMER', COUNT(*) FROM customer

UNION ALL

SELECT 'RENTAL', COUNT(*) FROM rental;


------------------------------------------------------------------------------------------------------------------------------------------


# Export the sakila schema to dump file

expdp schemas=SAKILA DIRECTORY=DUMPDIR DUMPFILE=sakila_20250529_%U.dmp LOGFILE=sakila_20250529.log compression=ALL;


# Import dump file of sakila schema

impdp schemas=SAKILA remap_schema=SAKILA:VIEWERS remap_tablespace=SAKILA_TBS:TBS_VIEWERS directory=DUMPDIR dumpfile=sakila_20250529_01.dmp logfile=SAKILA_import_20250527.log;



0 comments:

Post a Comment