Dynamic sql
If the dynamic statement is a SELECT statement, you can save the query results to variables after INTO. If a parameter exists in a dynamic statement, USING passes a value for the parameter in the statement.
The parameter format in dynamic SQL is: [: parameter name], and the parameter needs to use USING to pass values at runtime.
EXECUTE IMMEDIATE Dynamic statement string [INTO Variable list] [USING parameter list]
dblink
oracle can implement cross database access by creating dblink.
Oracle database links can be divided into the following three categories:
(1) private: a user level dblink is created. Only the user who created the dblink can use the dblink to access the remote database, and only the user can delete the dblink.
(2) public: creates a database level dblink that can be used by all users with database access rights in the local database or pl/sql programs.
(3) global: creates a network level dblink for oracle network.
select * from user_sys_privs where privilege like upper('%DATABASE LINK%') AND USERNAME='WANGYONG';
If the query returns a row, it means that you have the permission to create a database link. Otherwise, you need to use sys login orcl to give the user the permission to create: grant create public database link to user name
Authorize a table to users:
grant xxx authority on Table name to user
Step 2: create dblink
There are two types: 1) create graphically through pl/sql developer, and 2) create through SQL statements in sqlplus
perhaps
create public database link TESTLINK2 connect to user_name identified by "password" USING 'Connected databases'
Using dblink:
-- Simplest usage SELECT * FROM table_name@database_link; -- Don't want anyone to know database link When using a name, you can use synonyms to wrap it CREATE SYNONYM table_name for table_name@database_link; SELECT * FROM table_name; -- You can also create a view to encapsulate CREATE VIEW table_name_v AS SELECT * FROM table_name@database_link;
Delete dblink:
-- delete public Type dblink DROP PUBLIC DATABASE LINK dblink_name; -- delete private Type dblink，Only the creator can delete DROP DATABASE LINK dblink_name;
Synonyms
Synonym means that the current database user creates an alias for another user's object, and then queries and operates the alias, which is equivalent to directly operating the database object. Synonyms can also be set for a long string of characters with high frequency of use, which will be easier to query
Syntax: CREATE [OR REPLACE] synonym Synonyms for (Table name......)
Check whether the current user has permission to create synonyms:
select * from session_privs
If not, grant permission first
--among USER_B Is the user who needs to create synonyms GRANT CREATE SYNONYM TO USER_B; --At this time, you also need a permission USER_B user select USER_A user table Permissions for, --At this time USER_A Assigned by user grant select on tableA to user_temp;
Delete synonyms:
DROP [PUBLIC] SYNONYM [user.]sysnonym_name;
view
A view is a preprocessed query statement that can filter data from several tables.
Both view and with as query data first, and then are called by other operations.
The difference between View and with as is that the View is a virtual table after it is created. With as is equivalent to a temporary data table. The View is always in existence after it is created and not deleted. The View does not exist after it is executed with as
CREATE [OR REPLACE] [{FORCE|NOFORCE}] VIEW view_name
AS
SELECT query
[WITH READ ONLY CONSTRAINT]
FORCE: the view can be created even if the base table does not exist, but the view cannot be used normally
The view cannot be used normally until it is created successfully.
NOFORCE: if the base table does not exist, the view cannot be created. This is the default option
drop view View name #Delete view
index
The function of index is equivalent to the catalogue of books. You can quickly find the required content according to the page numbers in the catalogue. Can improve query speed
Single index: create index name on table name (column name);
Combined index: create index name on table name (column name 1, column name 2);
Delete index: drop index index name;
View all indexes of this table:
select * from all_indexes where table_name = 'Table name';
Tablespace
Once tables and indexes are created, tablespaces cannot be modified.
Create tablespace:
CREATE TABLESPACE Tablespace name DATAFILE 'Data file path' SIZE size [AUTOEXTEND ON] [NEXT size] [MAXSIZE size];
Description:
[] the contents are optional. If the directory is included in the data file path, you need to create it first
SIZE is the initial table space SIZE, in K or M
Whether AUTOEXTEND is automatically extended. The value is ON or OFF
NEXT is the extended size when the file is full
MAXSIZE is the maximum file size, and the value is numeric or unlimited (indicating unlimited size)
Query tablespace:
--Administrator role viewing tablespaces SELECT file_name, tablespace_name, bytes, autoextensible FROM dba_data_files WHERE tablespace_name = 'Tablespace name';
Rename tablespace:
alter tablespace Original tablespace name rename to New tablespace name
Expand tablespace:
ALTER TABLESPACE Tablespace name ADD DATAFILE 'File path' SIZE size [AUTOEXTEND ON] [NEXT size] [MAXSIZE size];
Count the free size of all tablespaces:
select a.tablespace_name,nvl(sum(b.bytes),0) bytes from dba_data_files a,dba_free_space b where a.tablespace_name=b.tablespace_name(+) and a.file_id=b.file_id(+) group by a.tablespace_name
Statistics on tablespace usage:
select c.tablespace_name "Tablespace",round(a.bytes/1024/1024,2)"Tablespace size", round((a.bytes-b.bytes)/1048576,2)"Used space"， round(b.bytes/1048576,2)"Remaining space", round(b.bytes/a.bytes * 100,2)||'%'"Remaining percentage" from (select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a, (select a.tablespace_name ,nvl(sum(b.bytes),0) bytes from dba_data_files a,dba_free_space b where a.tablespace_name=b.tablespace_name(+) and a.file_id=b.file_id(+) group by a.tablespace_name) b, DBA_tablespaces c where a.tablespace_name=b.tablespace_name(+) and a.tablespace_name=c.tablespace_name order by round(b.bytes/1024/1024,2)
Delete tablespace:
--Delete tablespace only DROP TABLESPACE Tablespace name; --Delete tablespaces and data files DROP TABLESPACE Tablespace name INCLUDING CONTENTS AND DATAFILES;