Common SQL commands for Oracle

Switch to Oracle User: Su-oracle, and execute under Oracle command line:

Launch into Oracle:

Connection database: Sqlplus/as sysdba

Windows Client Remote connection Oralce:

Sqlplus Itmuser/[email PROTECTED]//192.168.14.10:1521/ORCL

Test with tnsping:

Tnsping localhost (the host address where Oracle resides)

Tnsping ORCL (db instance name)

Monitoring start, view, and stop

Lsnrctl Start/status/stop

To export a table:

exp Itmuser/[email protected] file=/tmp/win_disk.dmp tables= ' \ ' disk\ ' rows=y

WHERE exp is the command to export data,ITMUSER/CFLDCN is the user name and password, respectively

Netmon is the path and file name of the exported file followed by the instance database file where the table resides

Tables followed by the table Disk to guide ; pay special attention to this symbol that references this table ' \ "Disk\" '

Import Table:

Imp itmuser/[email protected] file=/tmp/lin_disk.dmp log=/home/oracle/imp.log full=y

Enter the following SQL command line:

Startup database: Startup

Close database: Shutdown or shutdown immediate

View Oracle Version: select version from V$instance;

View current instance Name: Select instance_name from V$instance;

Select name from V$database;

To query the host name where Oracle resides:

Select Host_name from V$instance;

Create User: Itmuser identified by password;

Show current users: Show user;

Switch User: conn Itmuser/password;

Query Oracle All users: Select username from dba_users;

To view the file path for a user tablespace:

Select name from V$datafile;

To view the name and size of the user table space:

Select Tablespace_name,sum (bytes)/1024/1024 from Dba_segments GROUP by Tablespace_name

To view all user tablespace:

SELECT * from User_tablespaces;

Query all tables under the current library:

SELECT * from tab;

To query the size of all tables:

Select Segment_name,sum (bytes)/1024/1024 from user_extents Group by Segment_name;

To view the size of the specified table:

Select sum (bytes)/1024/1024 MBytes from user_segments where segment_type= "TABLE"

and segment_name= "DISK";

To view the actual usage size of the table:

Select Num_rows * avg_row_len/1024/1024 from User_tables where table_name= "Disk"

Delete data from table: Truncate TABLE "Disk_copy1";

Modify table name: Rename disk_copy2 to Disk_copy

ALTER TABLE old_table_name RENAME to New_table_name;

View the number of data bars in a table: SELECT COUNT (*) as Totalitem from "Disk";

View system date: Select Sysdate

Common SQL commands for Oracle