Oracle Basic Operations

Databases and instances

=============================================
Jdbc:oracle:thin:@127.0.0.1:1521:orcl
Listening:
Start monitoring: Lsnrctl start
Stop listening: Lsnrctl stop ORCL
View listening status: Lsnrctl status
NET Start Oracleservicename
NET Stop Oracleservicename

View DB Instance
Show parameter service_name;
Select name from V$database;
Select instance_name from V$instance;
prompt annotation Output

Spool Xx.log
--the statement to be intercepted
Spool End

View notes for a table
Select Comments from user_col_comments where table_name= ' Y_JGQ_YGXXB ';

Instance is a set of background processes/threads in a database and a shared memory area
In general, it is a single-instance database, where only one instance of the database operates.
But real application clusters (real application Clusters,rac)
You can have multiple instances mount and open a database at the same time (on a shared set of physical disks).

Management of Tables
=============================================
Data type
char (10) fixed length, fast query, wasted space Max 2000 characters
VARCHAR2 (20) variable length, slow query, space saving maximum 4000
Number
Number (5,2) five-digit, 2-bit active decimal
Number (5) Five-bit integer
Date Time type (day-month-year) (January-May-05)
Timestamp time type is more accurate than date
Blob binary type picture sound

Build table
CREATE TABLE Student (
XH Number (4), XM varchar (2), sex char (2), Bitthday date,sal number (7,2)
);

Delete a table
drop table name;

Modify Table Name
Rename table name to new table name

Add a field
ALTER TABLE name Add (column name);

Delete a field
ALTER TABLE name drop column name;

Modify field length
ALTER TABLE name modify (column name);

Modify the field type or name (cannot have data)
ALTER TABLE name modify (column name);

Add data
Insert into table name (column name ...) VALUES (column name ...);
Insert into table name values (column name ...);

Delete data, you can retrieve it from the log file in the save point
Delete from table name; (Can be deleted by rollback recovery, before the rollback point is set back to the save point rollback;)

Roll back the rollback;

Quickly delete data, do not write logs, can not be retrieved;
TRUNCATE TABLE name;

Delete the structure and data of a table
drop table name;

Tabular
View the structure of a table
DESC Table Name

Tabular
SELECT * from table name;
Select Column name ... from table name;
Select Column name ' column name to display ' from table name;

Cancel Duplicate rows
Select DISTINCT column name from table name;

Single-Table Query
SELECT * FROM table name where (column name >500 or column name = ' xxx ') and column name = ' xxx ';
SELECT * FROM table name order by column name; (ASC default from low to high)
SELECT * FROM table name order BY column name 1 ASC column Name 2 desc; (High to low)
Select Max (table name), min (table name) from table name;
Select Column Name 1, column name 2 from table name where column name 1= (select MAX (column name 1) from table name);
Select Column Name 1, column name 2 from table name grop by column name 2;

Multi-Table union query (Cartesian product) (the query condition cannot be less than the number of tables-1)
Table Association Query

Update
Update Y_JGQ_YGXXB set rlzyh=1001 where yhh= ' P03890 '
Select Y_jgq_ygxxb.*, Z_JSQ_JGB. FHMC, Y_jgq_ygxxb. SSJG, Z_JSQ_JGB. XNJG from Y_jgq_ygxxb, Z_JSQ_JGB where Y_JGQ_YGXXB. SSJG = Z_JSQ_JGB. XNJG;
Select mar1. Column name 1,mark2. Column Name 2 from table name 1 mar1, table name 2 MARK2 where mar1. Column name 1=mark2. Column Name 2;
Sub-query = inline view
Assigning an alias to a subquery table Biega as

Page total of three kinds
1.rownum (like a hidden field.) Number of rows recorded)
SELECT * FROM (select a1.* rownum rn from (SELECT * from MyList) A1 where rownum<=10) where rn>6;

Create a table with query results
CREATE table name 1 () as SELECT * from table Name 2;
Merge Query Union (and set de-weight) union ALL (and set not to weight) intersect (intersection) minus (difference set)
SELECT * FROM table name 1 Union SELECT * from table Name 2;

(Import and export tables to be performed under the bin under Oracle)
Exporting tables
Exp userid= User name/password @ instance tables= (table name) File=path\xx.dmp;

Export table Structure
Exp userid= User name/password @ instance tables= (table name) File=path\xx.dmp rows=n;

Export Database (incremental backup)
Exp userid= system/Password @ instance full=y inctype=complete file=path\xx.dmp;

Import Table
Imp userid= user name/password @ instance tables= (table name) File=path\xx.dmp;

Copy a table
CREATE table new table as SELECT * from original table;

Export Database
Copy data from one table to another table
Insert into target table from * original table;

User
=============================================
User: A database similar to another database, where a project corresponds to a user
When creating a user, create a corresponding scenario with data objects (tables, triggers, views ...)
SYS system administrator rights (SYSDBA) data dictionary base table and dynamic view in SYS
System Database Administrator rights (DBA) sub-level internal data management data

Create a user and give permissions
Create user username identified by password
Grant Connect to User name
Grant SELECT on EMP to username with GRANT OPTION (this permission can continue to be passed first)

Delete User (deleting a user name deletes this user-built table and must be cascade deleted)
Drop USER username Cascade

Switch users
Conn User name/password

Disconnect Connection
Disc User Name

Change Password
PASSW Enter
Old Password
New password

Intercepting the interface contents into the file:
Spool path;
SQL statements;
Spool off;

View current Login Role
Show user;

Run a script under a specific directory
Start Path\xx.sql;

Limit logon Hours
Create profile restricted name limit failed_login_attempts 3 Password_lock_time 2;
Alter user username profile restriction name;

Unlock
Alter user username account unlock;

Change Password regularly
Create profile restricted name limit password_life_time Password_grace_time 2;
Drop Profile Password.history Cascade

Log in with the OS user without a password
Sqlplus/as SYSDBA

Permissions and Roles
=============================================
Permissions are divided into: System permissions and Object permissions (permission to access other objects)
Roles are divided into: custom roles and pre-defined roles
A role is a collection of permissions

View Roles
SELECT * from User_role_privs;

The Operation permissions of the table you built are assigned to other users
Grant Select on table name to other user name
SELECT * from other user names. Table name

Revoke permissions
Revoke select on table name from other user names

DBA Database administrator
SYSDBA system Administrator
Sysoper System Operator
Connect Login Role

SYSDBA User: Can change character set, create delete database, log in after user is SYS (shutdown, startup)
Sysoper: User cannot change character set, cannot create, delete database, login after user is public (shutdown, startup)
DBA User: You can perform various administrative tasks only after you start the database.
Sysdba> sysoper> a normal DBA

Data dictionary static database system information (belongs to SYS is a collection of only base tables and views)
User_xxx
All_xxx
Dba_xxx
User_tables All tables owned by the current user
All_table All tables that the current user can access
Dba_tables database tables owned by all scenarios
Dba_users All Users
Dba_sys_privs User has system permissions
Dba_tab_privs The object permissions that the user has
Dba_col_privs display the user's column permissions
Dba_role_privs the role that the user has

Query all system permissions (140 total)
SELECT * from System_privilege_map order by name;

Querying all roles
SELECT * from Dba_roles;

Querying all object permissions (total of 16)
Select distinct privilege from Dba_tab_privs;

Querying the database for all table spaces
Select Tablespace_name from Dba_tablespaces;

Querying system permissions owned by a role
SELECT * from Dba_sys_privs where grantee= ' role name ';
SELECT * from Role_sys_privs where role= ' role name ';

Querying permissions for objects owned by a role
SELECT * from Dba_tab_privs where grantee= ' role name ';
SELECT * from Role_tab_privs where role= ' role name ';

The role that the user has
SELECT * from Dba_role_privs where grantee= ' username ';

Displays the current database full name
SELECT * from Global_name;

Shows that the current user has access to all data dictionary views
SELECT * from dict where comments like '%grant% ';

Dynamic performance View records activity information for the current routine
V_$ begins its synonym with v$ opening v_$datafile

Table Space
=============================================
Tablespace: Managing Logical folders for tables
Table Space-"section-" District-"block
Different tablespace can build the same table, but the user who needs to build the table is different, the difference means the user differs from the table space,
A table space is the logical structure of a table. Table naming constraints are user-dependent, regardless of table space
(Index to place table space, trigger to put table space, improve efficiency, reduce IO)

The role of Table spaces
1. Increase disk usage
2. Reduce IO reads while facilitating backup and recovery

Create tablespace (single file cannot exceed 500m)
Create tablespace tablespace name datafile ' path\xxx.dbf '
Size 500m uniform size 2m;

Working with Table spaces
CREATE table table name () tablespace table space name;

Take a table space offline
Alter tablespace table space name offline;

Bring table spaces Online
Alter tablespace table space name online;

Read-Only table space
Alter tablespace table space name read only;

Read and write table space
Alter tablespace table space name read write;

Show all tables for table spaces
SELECT * from all_tables where tablespace_name= ' table space name '

Know the table, check the table space.
Select Tablespace_name,table_name from user_tables where table_name= ' table name ';

Delete Table space
Drop tablespace table space including contents and datafiles;

Extending table Space
1. Add Data files
Alter tablespace table space add datafile ' path\xx.dbf ' size 20m;
2. Increase the size of the file
Alter tablespace table space ' path\xx.dbf ' rsize 20m;
3. Set file Auto-growth
Alter tablespace table space ' path\xx.dbf '
Autoextend on next 10m maxsize 500m;

Migrating table Spaces
1 Determine the table space where the data file resides
Select Tablespace_name from Dba_data_files where file_name= ' path\xx.dbf ';

2. Take the table space offline
Alter tablespace table space offline;

3. Move the table space to the specified location
Host move original path \xxx.dbf new path \xxx.dbf;
4. Execute the ALTER tablespace command
Alter tablespace table Space rename datafile ' original path \xxx.dbf ' to ' New path \xxx.dbf ';

5. Bring the table space online
Alter tablespace table space online;

Show tablespace Information
Select Tablespace_name from Dba_tablespaces;

Display the data files contained in the table space
Select File_name,bytes from from Dba_data_files where tablespace_name= ' table space ';
The table space name that the default Tablespace authorized user accesses temporary tablespace tem account unlock;

Transaction
=============================================
Transaction commit (delete savepoint, release lock)
Commit

Set Save Point
savepoint preservation of names;

Canceling partial transactions
Rollback to save the roll call;

Cancel All transactions
Rollback

Set read-Only transactions
Set TRANSACTION Read Only

SQL functions
=============================================
Lower (char) converts a string to a lowercase format

Upper (char) converts a string to uppercase format

Length (char) return lengths

substr (char m,n) intercept string

Replace (column name, ' A ' ' B ') Replacement string

Language language

Db_name Current database name

Host: the hostname where the database resides

Session_user the user who is currently operating

Select Sys_context (' Userenv ', ' db_ so ') from dual;

Data integrity
=============================================
Data compliance logic and business rules can be implemented by constraints, triggers, applications (procedures, functions)

1. Constraints
Not Null,unique (can be null), primary key,foreign Key,key,check,
Sex char (2) Default ' man ' Check (Sex (' Man ', ' woman '))
References (the table name (field) to be associated);

Add constraint
ALTER TABLE name modify column name not NULL;
ALTER TABLE name ADD CONSTRAINT constraint name unique (column name);
ALTER TABLE name ADD constraint constraint name check (addr in (");

Delete Constraint
ALTER TABLE name DROP constraint constraint name; (remove primary key to take Cascade)

Display user constraint information
Select constraint_name.constraint_type,status,validated
From user_constraints where table_name= ' table name ';
Select Column_name,position from User_cons_columns
where constraint_name= ' constraint name ';

Column-Level definition constraints
Table-level definition constraints create TABLE xxx () references

Index
=============================================
(Build index wasted space and insert efficiency, improve query efficiency)

Single-column index
Create index index name on table name (column name)

Composite Index
Create index name on table name (column name 1, column name 2);(SQL statement scan from back to front, try to exclude multiple columns in the back)

Query index
Select Index_name,index_type from user_indexes where table_name= ' table name ';
There are indexes on unique.

Stored Procedures
=============================================
Block-"Package-" function
Block-"Package-" process

Create procedure stored procedure name is
Begin
--Execute partial statements
End
/
Show Error

Call a stored procedure
exec Procedure name (parameter:)
Declear definition
Gigin execution
Exception error

Create procedure stored procedure name is
Begin
--This is a package in PL/SQL programming
Dbms_output.put_line (' Hello ');
End
/

Set serveroutput on open output option
Select column name into v_ column name from table name where column name [email protected];

Java calls stored procedures
CallableStatement Cs=connection.preparecall ("{Call stored procedure (?,?)}");

Function
Create funtion function name (name varchar) return number name number (7,1);
Begin
--Executive Section
End
/
VAR ABC number;
Call function () into:abc;

Package
Create Package name is
function ();
Process ();
End
/

Trigger: A stored procedure that is implicitly executed. The time and action of the trigger must be defined.
Create Trigger
Cursor-ref cuisor
Declare
Defining a cursor Type
The type cursor name is ref cuisor;
Defining a Cursor variable
Cursor name 1 cursor name;
V_ column name Vaechar;
Begin
Open cursor name 1 for select column name from table name where column name =&xx;
Loop
Fetch cursor name 1 into v_ column name;
Exit when cursor name 1 NotFound;
Dbms.output.putline (' v_ column name ');
End Loop
End

Create a View
Create or Replace view MyView as SELECT * from table name (with Read only)
Drop View MyView;

Other scattered
=============================================
Oracle version =i represents internet,g for grid
For the launch of e-commerce version -9i, G--grid for the grid launched, there are 10g, 11g, the current latest version of 11g
Steps to perform a SELECT query statement
1. Compiling (parse)
2. Execute (EXECUTE)
3. Extracting data (FETCH)
</Other Scattered >

Oracle Services
=============================================
1.Oracle 11g Seven Windows service features introduction
-------------------------------------------
①oracleserviceorcl:oracle Core Services This service is the basis for database startup and only if the service is started, the Oracle database can start normally. (Default boot on, must start)

②oracleoradb11g_home1tnslistener:orace monitoring services, services are required only when the database requires remote access, such as PL/SQL Developer. (Default boot start, not must start)

③ORACLEJOBSCHEDULERORCL: Job scheduling (timer) service, (default is disabled, if need to turn on, need to change to manual, not must start)

④oracledbconsoleorcl:oracle Database Console service, Access address: http://localhost:1158/em. (Default boot start, not must start)

⑤oraclevsswriterorcl:oracle ORCL VSS Writer service,oracle volume map copy write service, VSS (Volume Shadow Copy service) enables storage of basic devices such as disks, Array, etc.) when creating a high fidelity
Point image, which is a mapped copy (shadow copy). It can create a mapped copy on multiple volumes or on a single volume without affecting the system's system performance. (must not be started)

⑥oraclemtsrecoveryservice: Service-side control. The service allows the database to act as a resource manager for transactions in a Microsoft Transaction Server MTS, com/com+ object, and distributed environment. (must not be started)

⑦oracleoradb11g_home1clragent:oracle part of the database. Net Extension Service. (default manual, not must start)

2.oracle Start Batch Processing
-------------------------------------------
@echo off

is echo determined to start the Oracle 11g service?
Pause
Echo Starting Oracleservice, please wait ...

REM 1, Oracle Core Services This service is the basis for database startup, and only if the service is started, the Oracle database can start normally. (Default boot on, must start)
NET START ORACLESERVICEORCL

REM 2, Orace monitoring services, services are only required when the database requires remote access, such as PL/SQL Developer. (Default boot start, not must start)
NET START Oracleoradb11g_home1tnslistener

REM 3, Job scheduling (timer) service, (default is disabled, if need to turn on, need to change manually, not must start)
rem Net START ORACLEJOBSCHEDULERORCL

REM 4, Oracle Database Console service, Access address: http://localhost:1158/em. (Default boot start, not must start)
rem Net START ORACLEDBCONSOLEORCL

REM 5, Volume-mapped copy-write service, VSS (Volume Shadow Copy service) enables the creation of high-fidelity point-in-time images of storage infrastructure (such as disks, arrays, etc.) (must not be started)
rem Net START ORACLEVSSWRITERORCL

REM 6, service-side control. The service allows the database to act as a resource manager for transactions in a Microsoft Transaction Server MTS, com/com+ object, and distributed environment. (Default boot start, not must start)
rem Net START oraclemtsrecoveryservice

REM 7, part of the Oracle database. NET Extension Service. (default manual, not must start)
rem Net START oracleoradb11g_home1clragent

Pause

3.oracle Stop Batch Processing
-------------------------------------------
@echo off

is echo sure you want to stop the Oracle 11g service?
Pause
echo Stopping oracleservice, please wait ...

REM 1, Oracle Core Services This service is the basis for database startup, and only if the service is started, the Oracle database can start normally. (Default boot on, must start)
net stop ORACLESERVICEORCL

REM 2, Orace monitoring services, services are only required when the database requires remote access, such as PL/SQL Developer. (Default boot start, not must start)
net stop Oracleoradb11g_home1tnslistener

REM 3, Job scheduling (timer) service, (default is disabled, if need to turn on, need to change manually, not must start)
net stop ORACLEJOBSCHEDULERORCL

REM 4, Oracle Database Console service, Access address: http://localhost:1158/em. (Default boot start, not must start)
net stop ORACLEDBCONSOLEORCL

REM 5, Volume-mapped copy-write service, VSS (Volume Shadow Copy service) enables the creation of high-fidelity point-in-time images of storage infrastructure (such as disks, arrays, etc.) (must not be started)
net stop ORACLEVSSWRITERORCL

REM 6, service-side control. The service allows the database to act as a resource manager for transactions in a Microsoft Transaction Server MTS, com/com+ object, and distributed environment. (Default boot start, not must start)
net stop Oraclemtsrecoveryservice

REM 7, part of the Oracle database. NET Extension Service. (default manual, not must start)
net stop Oracleoradb11g_home1clragent

Pause

Services that need to be started in 4.Oracle basic development
-------------------------------------------
For beginners, if you only use Oracle's own sql*plus, just start ORACLESERVICEORCL, and if you use a third-party tool such as PL/SQL developer, Oracleoradb11g_ Home1tnslistener service is also to be opened.
ORACLEDBCONSOLEORCL is a web-based EM must be turned on, and the rest of the service is seldom used.

Note: ORCL is the database instance name, the default database is ORCL, you can create other, that is, the oracleservice+ database name.

Oracle remote Connection not on
==============================================================
D:\app\lb\product\11.2.0\dbhome_1\NETWORK\ADMIN
Tnsnames.ora
Orcl=
(DESCRIPTION =
(Address_list =
(ADDRESS = (PROTOCOL = TCP) (HOST = 127.0.0.1) (PORT = 1521))
)
(Connect_data =
(SID = ORCL)
(SERVER = dedicated)
)
)

Start-run-enter "CMD" to determine
In the pop-up window, enter Sqlplus/as SYSDBA return
Then, modify with the command:
Alter user username identified by "password";
Enter ~ Note the colon ~

Oracle Basic Operations