Oracle Common Commands

1. Connect the óracle:

Sqlplus Username/[email protected] [as SYSDBA]

Sql>startup Force [Nomount/mount/open/force/restrict]

Sql>shutdown [Normal/transactional/immediate/abort]

2. Start the console:

Mysqld-nt–install (compressed installation into service)

Mysqld-nt–remove (Uninstall Service)

net start (stop) MySQL

De>mysql-v or Mysql-u root-pde>

Set oracle_sid=**
Emctl Start Dbconsole
Http://hnjk-oh:1158/em
Isqlpuls start
Http://hnjk-oh:5560/isqlplus
Lsnrctl Start (Stop) (start TNS)
DBCA (Start configuration Management)
Dbua (Upgrade management)
NETCA (Network configuration Management)

3. Create table spaces and users:

Sql>create tablespace wy863college datafile ' F:\oracle\product\10.2.0\oradata\hnjkorg\wy863college.dbf ' size 500M Autoextend on NEXT 5M MAXSIZE UNLIMITED;

Sql>drop tablespace tablespacename including contents and datafiles;

Sql>create user Wy863college identified by college default Tablespace Wy863college;

Sql>grant dba to Wy863college;

4. Change the character set:

Log in to Oracle:

Sqlplus sys/**** as SYSDBA * * * is the password of sys

To view the character set:

Sql>select userenv (' language ') from dual;

To stop the service:

Sql>shutdown Immediate

To open a service in restricted mode:

Sql>startup Restrict

To change the character set:

Sql>alter database Character Set Internal_use ZHS16GBK

To restart Oracle:

Sql>shutdown Immediate

Sql>startup

5. Oracle Archive

To see if the Oracle database is in archive mode
Sql> select Name,log_mode from V$database;

NAME Log_mode
------------------ ------------------------
TEST Noarchivelog

Sql> Archive Log List
Database log mode No Archive mode
Automatic Archival Disabled
Archive Destination Use_db_recovery_file_dest
Oldest online log sequence 80157
Current log sequence 80163

Edit the archive mode for this section of the configuration database
1. Change non-archive mode to archive mode:
1) sql> Conn/as sysdba (database connected as DBA)
2) sql> shutdown immediate; (Close database now)
3) Sql> startup mount (launches the instance and loads the database but does not open)
4) sql> ALTER DATABASE archivelog; (Change database to archive mode)
5) sql> ALTER DATABASE open; (Open database)
6) sql> alter system archive log start; (Enable automatic archiving)
7) Sql> exit (exit)

Do a full backup because the backup log generated in non-archived log mode is not available for the archive mode. This step is not very important!
2. Change the archive mode to non-archive mode:
1) Sql>shutdown normal/immediate;
2) Sql>startup MOUNT;
3) Sql>alter DATABASE Noarchivelog;
4) Sql>alter DATABASE OPEN;
3. Archive related commands
Archive log stop;
Archive log start;
Archive log list;

Show parameters;
Show Parameters Log_archive_start;
Show Parameters log_archive_max_process; #归档进程数
alter system set LOG_ARCHIVE_MAX_PROCESS=5; #将归档进程数改为5
SELECT * from V$bgprocess; #检察后台进程

6. Number of process changes:

Sqlplus/nolog

Sql>conn/as Sysdba;

Sql>show parameter pfile;

Sql>show parameter process;

Sql>alter system set processes=500 scope=spfile;

Sql>startup Force;

7. Create a catalogue:

Sqlplus Username/[email protected] [as SYSDBA]

I. Create:

Sql>create or replace directory Dump_dir as ' E:\test\ ';(Windows-based systems)

Sql>create or replace directory Dump_dir as '/home/oracle/bfiletest ';(based on Linux systems)

sql>commit;

II. View:

Sql>select * from Dba_directories;

III. Delete:

Sql>drop directory Dump_dir;

8. Guide data:

Doc mode:

I. EXPDP \ "Sys/hnjk as Sysdba\" schemas= (Developer,jforum,courseforum,
Virtualcourtroom) directory=dump_dir dumpfile= "Db.dmp"

Exp User/password file=exp.dmp compress=y grants=y indexes=y tables= (table1,table2);

(exp can only be used compress=y)

II. IMPDP \ "Sys/hnjk as sysdba\" Directory=dump_dir dumpfile= "$dump _file"

III. IMPDP system/<password> schemas=sh remap_schema=sh:sh2 \

DUMPFILE=DATADIR1:SCHEMA1%U.DMP,DATADIR2:SCHEMA2%U.DMP \

Exclude=constraint, Ref_constraint, Index,materialized_view \

Table_exists_action=replace Logfile=datadir1:impschema.log

9. View Flash_recovery_area space usage:

1) View the database Redolog situation:

Sql> select * from V$log;

2) Manually switch logs

sql> alter system switch logfile;

3) View Flash_recovery_area space usage:

[Email protected] oracle]# du-a-K Flash_recovery_area
...............

2353148 Flash_recovery_area Description: Flash_recovery_area space uses 2.35GB

4) View the usage of each part of the Flash_recovery_area space:

Sql> select * from V$recovery_file_dest;

Sql> select * from V$flash_recovery_area_usage;

10. Oracle deletes users and table spaces

Oracle has been in use for a long time, adding many user and tablespace. Need to clean up.

For a single user and tablespace, you can do this using the following command.

Step One: Delete user

Drop Userxxcascade

Note: Delete the user, just delete the schema objects under the user, will not delete the corresponding tablespace.

Step Two: Delete tablespace

DROP tablespace tablespace_name including CONTENTS and datafiles;

However, because it is a DB for the development environment to use, there are many user and table spaces that need to be cleaned up.

Ideas:

Export all the user and tablespace in db, filter out the system and useful tablespace, load the useful information into a table.

Then write the routine loop and delete the tablespace that are not in the useful table.

1). Select Username,default_tablespace from Dba_users;

2).

CREATE TABLE Mtusefulspace
(
ID Number (4) Not NULL PRIMARY KEY,
USERNAME VARCHAR2 (30),
Tablespacename VARCHAR2 (60),
OwnerName VARCHAR2 (30)
);

3).

Declare icount number (2);
Tempspace VARCHAR2 (60);
Begin
For curtable in (select Username as allusr,default_tablespace as alltblspace from Dba_users)
Loop
Tempspace: =curtable.alltblspace;
Dbms_output.put_line (Tempspace);
Select COUNT (tablespacename) into icount from mtusefulspace where tablespacename = Tempspace;
If Icount=0 Then
DROP tablespace tempspace including CONTENTS and datafiles;
End If;
Commit
End Loop;
End

The following error will be reported after execution

ORA-06550: Line 10th, column 5th:
PLS-00103: the symbol "DROP" appears when you need one of the following:
Begin Case declare exit
For goto if loop mod null pragma raise return select update
While with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
Close current Delete fetch lock insert open rollback
SavePoint set SQL Execute commit forall merge pipe
06550.00000-"line%s, column%s:\n%s"
*cause:usually a PL/SQL compilation error.
*action:

It seems to be locked up.

No way, the routine can not write, you can only group out the statement execution.

Export the user, tablespace, to Excel that needs to be deleted. Use concatenate to group out SQL.

Paste to sqldevelop batch execution.

The entire deletion will be time consuming, with more than 100 user. It took about 12 hours.

If you want to find the exact location of datafile, you can use

Select T1.name,t2.name from V$tablespace T1, v$datafile t2 where t1.ts# = t2.ts#;

SQL Code
--Delete empty tablespace, but no physical file
Drop Tablespace tablespace_name;
--Delete non-empty table spaces, but not physical files
Drop tablespace tablespace_name including contents;
--Delete empty table space, including physical files
Drop tablespace tablespace_name including datafiles;
--Delete a non-empty tablespace containing physical files
Drop tablespace tablespace_name including contents and datafiles;
--Add Cascade CONSTRAINTS if the table in the other tablespace is associated with a field of a table in this table space with a constraint such as a foreign key.
Drop tablespace tablespace_name including contents and datafiles CASCADE CONSTRAINTS;

Oracle Common Commands