[Problem and background]1. delete a table? A library?
Drop table table_name;
Drop table database_name;2. Delete all data of a user?
It is best to stop the database first and shutdown immediate; & startup to ensure no user connection
Or select Sid, serial # from V $ session where username = // 'username // '; query the connection
Sid serial #
--------------------
24 25341
End all sessions of this user
SQL> alter system kill session // '2017 41 ///';
System altered.
Delete: Drop User Username cascade; 3. Create and authorize users (in this example, connect, modify, and administrator)
Create User Username identified by passwd;
Grant connect, resource, DBA to username;
Commit; 4. How to Set character sets?
View the client Character Set environment:
Select * From nls_instance_parameters; its source is V $ parameter.
Setting Process
Svrmgr> shutdown immediate;
Svrmgr> startup Mount;
Svrmgr> alter system enable restricted session;
Svrmgr> alter system set job_queue_processes = 0;
Svrmgr> alter system set aq_tm_processes = 0;
Svrmgr> alter database open;
Svrmgr> alter database character set zhs16gbk;
Svrmgr> shutdown immediate;
Svrmgr> startup;
If the following error is reported:
SQL> alter database character set zhs16gbk;
Alter database character set zhs16gbk
*
Error at line 1:
ORA-12712: New character set must be a superset of old Character Set
Solution:
SQL> alter database character set internal_use zhs16gbk; # Use internal_use to skip the superset check. Alter database character set internal_use 5. query instances? All Database names? Table Name?
Select * from V $ database; show parameter dB; // view all current databases:
Show parameter optimizer; // display the setting information
Select table_name from user_tables; // The table of the current user
Select table_name from all_tables; // tables of all users
Select table_name from dba_tables; // includes the system table
Field Names and data types:
Desc talbename;
View the primary key:
Select * From user_constraints where constraint_type = // 'P // 'and table_name = upper (// 'trd _ User ///')
View SQL statements of a tablespace
Col tablespace_name format A10;
Select F. tablespace_name,. total, U. used, F. free, round (U. used/. total) * 100) // "% used ///",
Round (f. Free/a. Total) * 100) // "% free ///"
From
(Select tablespace_name, sum (Bytes/(1024*1024) Total
From dba_data_files group by tablespace_name),
(Select tablespace_name, round (sum (Bytes/(1024*1024) used
From dba_extents group by tablespace_name) U,
(Select tablespace_name, round (sum (Bytes/(1024*1024) free
From dba_free_space group by tablespace_name) f
Where a. tablespace_name = f. tablespace_name
And a. tablespace_name = U. tablespace_name; 6. Add a tablespace
Add data files to the tablespace:
Alter tablespace users add datafile // '/u01/Oracle/oradata/trds/system01.dbf // 'size 1000 m;
Increase the size of the original data file in the tablespace:
Alter database datafile // '/u01/Oracle/oradata/trds/system01.dbf // 'resize 1000 m; VII. Loss of data files in temporary tablespace of the database
A ORA-01157 error may also occur when the data file of the temporary tablespace in the database is lost. Because the database does not check the data files in the temporary tablespace, the database can be opened at this time. In this case, the solution is to logically Delete the data file of the temporary tablespace and add a new data file of the temporary tablespace.
For example:
Select * From dba_objects order by object_name;
Select * From dba_objects order by object_name;
* Error at line 1:
ORA-01157: cannot identify/lock Data File 5-see dbwr trace file
ORA-01110: Data File 5: // '/Oracle/oradata/temp01.dbf ///' Alter database tempfile // '/Oracle/oradata/temp01.dbf // 'drop;
Select tablespace_name, file_name from dba_temp_files;
Alter tablespace temp add tempfile '/Oracle/oradata/temp01.dbf 'size 100 m; 8. view the number of Oracle user connections
Select count (*) from V $ session # Number of connections
Select count (*) from V $ session where status = // 'Active // '# number of concurrent connections
View the Oracle database status
Select status from V $ instance;
Select open_mode from V $ database; 9. PLS-213: Package standard not accessible error Solution
Sqlplus/nolog
SQL> connect/As sysdba
SQL> $ ORACLE_HOME/rdbms/admin/CATALOG. SQL
SQL> $ ORACLE_HOME/rdbms/admin/catproc. SQL
SQL> $ ORACLE_HOME/rdbms/admin/catexp. SQL 10. Run LSNRCTL start
Tnslsnr for Linux: Version 8.1.7.0.0-Production
System parameter file is/Oracle/product/8.1.7/Network/admin/listener. ora
Log messages written to/Oracle/product/8.1.7/Network/log/listener. Log
Listening On: (description = (address = (Protocol = IPC) (Key = EXTPROC )))
Listening On: (description = (address = (Protocol = TCP) (host = linserver) (Port = 1521 )))
Listening On: (description = (address = (Protocol = TCP) (host = linserver) (Port = 2481) (protocol_stack = (Presentation = GIOP) (session = raw )))
TNS-01201: Listener cannot find executable/Oracle/product/8.1.7/bin/EXTPROC for Sid plsextproc
Use extproco in binl as a link called EXTPROC. 11. oemapp commands:
Oemapp dbastudio
Oemapp Console
Oemapp txtm 12. How to create an instance?
Create Database trds
Controlfile Reuse
Logfile // '/data/Oracle/trds/redo01.log // 'size 1 m reuse,
/// '/Data/Oracle/trds/redo02.log // 'size 1 m reuse,
/// '/Data/Oracle/trds/redo03.log // 'size 1 m reuse,
/// '/Data/Oracle/trds/redo04.log // 'size 1 m Reuse
Datafile // '/data/Oracle/trds/system01.dbf // 'size 10 m Reuse
Autoextend on
Next 10 m maxsize 200 m
Character Set we8iso8859p1;
Create rollback segment rb_temp storage (initial 100 K next 250 K );
Alter rollback segment rb_temp online;
Create tablespace RBS
Datafile // '/data/Oracle/trds/rbs01.dbf // 'size 5 m reuse autoextend on
Next 5 m maxsize 150 m;
Create tablespace users
Datafile // '/data/Oracle/trds/users01.dbf // 'size 3 m reuse autoextend on
Next 5 m maxsize 150 m;
Create tablespace temp
Datafile // '/data/Oracle/trds/temp01.dbf // 'size 2 m reuse autoextend on
Next 5 m maxsize 150 m;
Create rollback segment rbstorage (initial 50 K next 250 K)
Tablespace RBS;
Create rollback segment Rb2 storage (initial 50 K next 250 K)
Tablespace RBS;
Create rollback segment Rb3 storage (initial 50 K next 250 K)
Tablespace RBS;
Create rollback segment rb4 storage (initial 50 K next 250 K)
Tablespace RBS;
Alter rollback segment rb1;
Alter rollback segment Rb2 online;
Alter rollback segment Rb3 online;
Alter rollback segment rb4 online;
Alter rollback segment rb_temp offline;
Drop rollback segment rb_temp; |
