Instructions and explanations for SQL beginners
(Basic process of user creation(making a schema), database access etc.)
Hi guys! I am blogger AllInformation.
Today, I will post how to use Oracle and SQL.
It is very hard for beginners to use these programs, because the mechanism is very hard.
Therefore, I will explain the mechanisms with simple explanation and some examples.
Just keep reading! then it would be much easier for you to use these programs! Trust me!
So, Let's go for it!
(Before starting reading this one, please understand that the language setting in the programs are Korean language. I think it will not disturb you to do something that you want!)
STEP 1. Understanding tablespaces
In analyzing data using Oracle,
The first thing you need to understand is a "table space".
Table space means a space to store data to be processed like the shelves in the above picture.
It literally means TABLESPACE, that is, space to store data.
+ Size comparison of data: table space> segment> extent> data block
You don't need to understand the difficult words above.
it's easy to think of the tablespace as the biggest one, that is, the container that holds and stores everything.
Therefore, the first thing to do to process the data is "creating a tablespace."
(If you don't have any space, then there is no place to store data)
STEP 2. Let's create a table space!
Press Window + r buttons to open the window program for running, and then submit "cmd" into the blank, and press enter to open the window command program.
In the command program, you need to input sqlplus by using your keyboard,
and then press enter to enter the sql plus program. (It is to make a table space! keep remember)
(Login is required here, ID is system, password is the password entered when you installed Oracle)
"system" that I wrote in the id form is the owner ID of the database. Think of it as an operator.)
After logging in, you can now use oracle's database.
---------------
- In-depth understanding
* There is an important concept here.
The sqlplus we use is called client, and the oracle database is called the server that stores the database.
client is literally a guest.
For example, a customer came and said, "I wanna order a cup of coffee, but please make it weak" (request)
Therefore, "client" is a program that transmits commands or orders to the server (Oracle database).
Then, the server here is a coffee shop. Remember the command that you ordered "Please make coffee weak", select the materials (data) that the coffee shop(Oracle) have in their storage(database) based on this command.
After processing the result(weak coffee) is returned to the customer (It is called as "response").
Therefore, the client sends a command to the server. The server receives the request, processes it, and returns the result to the client.
--------------
Anyway, before processing the data in the database Let's keep creating a TABLESPACE.
CREATE TABLESPACE tablespace_filename DATAFILE 'C:\app\~~~\oradata\myoracle\파일이름.dbf' SIZE 100M AUTOEXTEND ON NEXT 5M;
To understand the above coding, I'll explain one by one.
the code above is a command to create a new tablespace!
'C:\~~~' (storage location) : you need to select the location where ORACLE is installed.
For me, I wrote 'C:\oraclexe\app\oracle\oradata\XE' as a location. maybe there is a location that you installed oracle, then you need to select the "XE" folder in "oradata" in "oracle" in "app" in "oraclexe".
Simple explanation : Create a tablespace, and create a datafile named yooo.dbf in the location of 'C:\oraclexe\app\oracle\oradata\XE'. and the size is 100 megabytes, and it can be automatically expanded when the data exceeds this capacity. and the automatically increased capacity is 5 megabyte increments.
So right now, we made the table space to store the data.
the next step is making a user.
STEP3. Create a user.
you would wonder that why we make a user? What is it?
When I studied Oracle at the first time, I also had a same thought with you.
I will explain why we need user based on the Oracle system.
---------------------------
- explanation about the user in Oracle (Easy example for understanding)
Assuming that you are a chairman in a large corporation, and there are a lot of precious data in your company such as the cost of production, the ids and passwords of your customers, their personal information etc.
If you can manage all of this information then it would be really great. but you know that it can be managed by one person.
So, you need to allocate the information to other managers based on their roles.
So, the chairman id in the oracle is "System". he can do anything that he want, and he can see any information.
And in this case, the other manages who are allocated some information from the chairman would be "User".
So, to distribute the authorization of information, Users are needed in the Oracle system.
Therefore, this step, we will make a user by using "System" id (Chairman).
you know, we already get into the sqlplus program using "System" id. it means that we got into the program with the chairman's id.
---------------------------
Since sql is case-insensitive, it doesn't matter whether you use lowercase or uppercase. However, Commands are usually written in uppercase.
CREATE USER UserID IDENTIFIED BY UserPassword
DEFAULT TABLESPACE Tablespace_where_your_data_in_your_work_will_be_stored
TEMPORARY TABLESPACE Temporal_storing_place;
Write the above command.
- the explanation about the above command
Create a username of security_team, but make sure the password is identified as 1234. If nothing is specified, data is set to be stored in the tablespace YOOO. Temporarily, it is set to be temporarily stored in tablespace temp.
--------- (more information) ----------
- How to change user password and delete user
Command for changing the password of a user
ALTER user UserID IDENTIFIED BY new_password_that_you_want_to_change;
Command for deleting a user
DROP USER UserID;
STEP4. Granting authority
GRANT CONNECT, RESOURCE, DBA TO security_team;
- Explanation of the above code
I want to give the authorities of function 1(connect), function 2(resource), and function 3(DBA) to a user (security_team)
Type of authority: connect, resource, dba, etc... Give ora_user the authority to manage connect, resource and database.
STEP5. Now access the DB with the created account.
CONNECT User_ID/password;
log in by entering the above command.
if you want to check whether your log-in is successfully operated, then write the below code
SELECT USER FROM DUAL;
USER
---------
User ID
If the message is displayed, you have successfully logged in.
STEP6. Now pass the data to be processed with this user's ID
If I want the security_team to process the data of expcust.dmp, you need to move the data into the security_team user's tablespace. After that process, the data can be accessed by the security team.
First of all, you need to exit the current sqlplus program to come back to window UI,
type exit to exit sqlplus and return to the command window
--------
If you have already closed the window, you can press window+r, press cmd, and then press Enter.
--------
First, get into the CMD window. you need to move the file containing the data file (dmp: dump file) that I want to process into another location. This can be done with the cd (change directory) command and dir (directory) command.
-------------
- codes for window command
dir : Shows the files in the current folder.
cd .. : move to the previous file
cd 'c:\app' : change the current location into the filepath ('c:\app')
-------------
using cd and dir eventually found "expcust.dmp" file.
The security team has found a data dump file that needs to be processed. But... the above file cannot be imported due to a partition problem (my fault...), Let's try to import the "expall.dmp" file instead.
in this location,
imp UserID/User_password file=filename.dmp log=log_file_name.log ignore=y grants=y rows=y indexes=y full=y
Write the above code, and press "Enter"
- Explanation of the above code
The meaning of the above coding is to load a dump file named expall.dmp in the schema (data processing space) of the security_team ID, In the process, to check whether an error has occurred or whether it has entered properly, a log file is left as empall.log and recorded. And I'm not sure about ignore, so let the user modify grants, that is, all rows (rows), indexes (indexes), and full (the whole file) are loaded.
You can see that the data passed well to this user.
(Your current window and my window may be different. I created a user called ora_user and practiced this file before, so the appearance may be different.)
STEP7. Make sure that the imported data is coming in
The steps we did up to the previous step was using the command language interface (CLI), which was executed in the command window.
Now it is time to come back to the GUI (Graphic User Interface)! Manipulation with commands in CLI is visually too difficult, so it is usually used in a GUI way. So, the reason for using software such as sql developer, Mysql, etc. is to make the tasks that were difficult in CLI a little more visually convenient.
So please turn on sql developer or other GUI sql tool,
If there is a button to connect to a new database, press it.
For sql developer, click the '+' button there.
And for the name, enter the user ID we created earlier in the command window,
Enter the username and password you created in the command window you created earlier in the username and password.
Press the test button. When it says "Success", press the Connect button. If a failure occurs, the user ID or password is incorrectly entered, or It must be the case that the registration was not done properly in the command window.
When you press connect, it prompts you to enter the password again.
Just enter the password as it was before.
We will look into creating tables and manipulating tables in the next post!
댓글