Oracle Database Learning Notes

To insert data while creating a table:
CREATE TABLE Zhang3 as SELECT * from Zhang1;
CREATE TABLE Zhang3 (id,name) as SELECT * from Zhang1;



Insert the query data into a table:
INSERT INTO Zhang3 select * from Zhang1;
Insert into Zhang3 (id,name) select * from Zhang1;



Two views about integrity constraints, table names, column names:
User_constraints, User_cons_columns



Create a user and authorize the user (on the command line):
Sqlplus/as SYSDBA
Create user ZFS identified by ZFS;
Grant DBA to ZFS;



The Oracle database is customized for installation, and there is no database after the installation succeeds, you need to create the database manually (using DB Configuration Assistant).
You need to specify a password during the creation of the database (the password for the specified SYS, SYSTEM, DBSNMP, Sysman users). The actual use of found to enter the password can be logged sysdba this user, perhaps this is a special user, is logged in with the identity of the system. But you can also log in to SYS as a user with any password.

The database has not been completed since it was created, and there is no LISTENER to create a good LISTENER using Net configuration Assistant.
Now we also need to have tnsnames.ora this file to describe the database we just created. If LISTENER was created before the database was created, the Tnsnames.ora file should be generated automatically.
Because the database was created before LISTENER, you now need to manually configure Tnsnames.ora. Still using NET Configuration assistant, select the Local net service name configuration, enter the name of the database created, and Tnsnames.ora will be generated automatically after the configuration is complete.



About Oracle database management for users:
Users of different databases should not be generic, such as creating a user in a database that cannot log on to another database.
The user of the database is in a table named user$ that is saved in the database. This table is not visible to users of the DBA role,



Information described by Tnsnames.ora:
Each entry in the file should describe the information required to connect to a database (IP address, port number, service name of the database to which the listener is mapped to the corresponding database), and the user name and password required to connect to the database are not placed in the Tnsnames.ora In this file, but to enter it at login.
So each time the login session corresponds to a database, if you want to switch to another database, you want to use another entry in Tnsnames.ora.



Because Oracle's ODBC driver is used in the Informatica PowerCenter, the Oracle ODBC Driver should be selected in the process of installing the Oracle database if it is installed in a customized manner.



Querying tables in the database:
SELECT * from tab;
SELECT * from User_tables;
SELECT * from All_tables;
SELECT * from Dba_tables;



The difference between SYS and system:
SYS is the most privileged user and is the highest administrator within the database cluster. System is the highest administrator for a single Oracle instance.



Compares a value to the value of a list or returned by a query. Must is preceded by =,!=,>,<,<=,>=. Can is followed by any expression or subquery this returns on or more values. An any of the examples can be some
eg. SELECT * FROM Employees
WHERE salary = Any
(SELECT Salary
From Employees
WHERE department_id = 30)
ORDER by employee_id;



Regular Expressions about Oracle:
In regular expressions, you can use an operator similar to \s to match whitespace characters, but Oracle's regular expressions do not support \ r \ n \ t \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \



Oracle Database interprets the empty string as null.



Examples of output statements for Plsql:
Dbms_output. Put_Line (' surname= ' | | surname);



The%TYPE attribute is particularly useful when declaring variables to hold database
Values. The syntax for declaring a variable of the same type as a column is:
Variable_name Table_name.column_name%type;



Assigning Values to Variables with the SELECT into Statement
A simple form of the the SELECT to statement is:
SELECT Select_item [, Select_item] ...
Into variable_name [, variable_name] ...
from table_name;


Between and in Oracle are closed intervals.

Oracle Database Learning notes