trigger:
SELECT? TRIGGER_NAME, TRIGGER_TYPE, TABLE_OWNER, TABLE_NAME, STATUS? from? user_triggers;
Snapshot:
SELECT? OWNER, NAME, MASTER, TABLE_NAME, LAST_REFRESH, NEXT? from? user_snapshots? Order? by? Owner, Next;
Synonym:
SELECT? *? from? syn;
sequence:
SEQ;
Database link:
SELECT? *? from? user_db_links;
Constraint limit:
SELECT? TABLE_NAME, ConsTRAINT_NAME, SEARCH_CONDITION, STATUS? from? user_constraints;
This user reads the permissions of other user objects:
SELECT? *? from? user_tab_privs;
System authority owned by this user:
SELECT? *? from? User_sys_privs;
user:
SELECT? *? from? all_users? Order? BY? user_id;
The remaining free space of the table space:
SELECT? TABLESPACE_NAME, SUM (BYTES)? Total byte, max (bytes), count (*)? from? DBA_FREE_SPACE? Group? By? tablespace_name;
Data Dictionary:
SELECT? TABLE_NAME? From? DICT? Order? BY? table_name;
Lock and resource information:
SELECT? *? from? V $ lock; excluding DDL lock
Database character set:
SELECT? Name, Value $? from? props $? where? name = 'nls_characterset';
ININ.ORA parameters:
SELECT? NAME, VALUE? From? V $ parameter? Order? by? name;
SQL shared pool:
SELECT? SQL_TEXT? From? V $ sqlarea;
database:
SELECT? *? from? V $ database
Control file:
SELECT? *? from? V $ controlfile;
Heavy log file information:
SELECT? *? from? V $ logfile;
Log file information from the control file:
SELECT? *? from? V $ log;
Data file information from the control file:
SELECT? *? from? V $ datafile;
NLS parameter current value:
SELECT? *? from? V $ nls_parameters;
Oracle version information:
SELECT? *? from? v $ version;
Describe the background process:
SELECT? *? from? v $ bgprocess;
View version information:
SELECT? *? from? product_component_version;
7. How to read and write files in PL / SQL? ?
Software Environment:?
1, server side: windows? NT4.0 ORACLE? 8.0.4
2, the Oracle installation path is: C: / ORANT?
Implementation:?
In the version of PL / SQL? 3.3 or more, the UTL_FILE package allows the user to read the operating system file via the PL / SQL. as follows:?
Declare
FILE_HANDLE? UTL_FILE.FILE_TYPE;
Begin
FILE_HANDLE?: =? UTL_FILE.FOPEN ('/ TMP', "file name ',?' w '); UTL_FILE.PUTF (File_Handle," Writing information / n');
UTL_FILE.FCLOSE (File_Handle);
EXCEPTION
When? UTL_FILE.INVALID_PATH? THEN
RAISE_APPLICATION_ERROR (-20000,? 'Error:? invalid? path? for? file? init.ora.');
End ;?
The PUTF () process is used to write text into a file in the specified format.
The PUT_LINE () process writes a specified string into the file and starts a new line in the file.
8. How to calculate the number of records in the table? ?
System environment:?
1, operating system: Windows? 2000
2, Database: Oracle? 8i? R2? (8.1.6)? For? NT? Enterprise Edition
3, installation path: c: / oracle
Implementation:?
Check out what objects under the current user (table, view, synonym, snapshot)
SQL>? SELECT? *? From? Tab;
View the table structure
SQL>? DESC? Table name
View the number of records in the table
SQL>? SELECT? Count (*)? From? Table name;
SQL>? SELECT? Count (rowid)? From? Table name;
9. How to view the structure of the table? ?
System environment:?
1, operating system: Windows? 2000
2, Database: Oracle? 8i? R2? (8.1.6)? For? NT? Enterprise Edition
3, installation path: c: / oracle
Implementation:?
Check out what objects under the current user (table, view, synonym, snapshot)
SQL>? SELECT? *? From? Tab;
View the table structure
SQL>? Describe? Table name
Shorthand or above
SQL>? DESC? Table name
10. How to generate SQL batch files with SQL? ?
Software Environment:?
1, Windows? NT4.0 ORACLE? 8.0.4
2, the Oracle installation path is: C: / ORANT
Questions raised:?
1. Users need to perform one of the same SQL operations for each table under the database user. At this time, it is very troublesome to type the SQL statement over again.
Implementation:?
SQL>? Set? Heading? OFF? - Prohibition of output column headings
SQL>? Set? Feedback? OFF? - Disable the counting feedback information for the last line
List the definition of all synonyms under the current user, can be used to test the true existence of synonyms
SELECT? 'DESC?' || TNAME? From? Tab? Where? tabtype = 'synonym';
Query the number of records for all tables under users
SELECT? 'SELECT?' '' || TNAME || '', count (*)? from? '|| tab? where? Tabtype =' TABLE ';
Give all the eligible tables for Select permissions to public
SELECT? 'GRANT? SELECT? ON?' || Table_name || '? to? public;'? from? user_tables? Where? "Condition";
Delete users under various objects
SELECT? 'Drop?' || TabType || '?' || TNAME? From? Tab;
Delete eligible users
SELECT? 'DROP? User?' || username || '? cascade;'? from? all_users? where? user_id> 25; quickly compile all views?
---- When you pour the database into the new server (database reconstruction), you need to recompile the view again.
---- Because the table space view is problematic, it can use the PL / SQL language characteristics, quickly compile. ?
SQL>? Spool? ON.SQL
SQL>? Select'ALTER? View? '|| TNAME ||'? Compile; '? From? Tab;
SQL>? Spool? OFF
Then perform ON.SQL.
SQL>? @ On.sql
Of course, authorization and creating symbols can also be made quickly, such as:
SQL>? SELECT? 'GRANT? SELECT? ON?' || TNAME || '? TO? User name;'? From? Tab;
SQL>? SELECT? '|| TNAME ||'? For? User name. '|| TNAME ||'; 'from? Tab;
