Common Oracle scripts

1. view all objects currently

SQL> select * From tab;

2. Create an empty table with the same structure as Table

SQL> Create Table B as select * from a where 1 = 2;

SQL> Create Table B (B1, B2, B3) as select A1, A2, A3 from a where 1 = 2;

3. Check the database size and space usage

SQL> Col tablespace format A20
SQL> select B. file_id File ID,
B. tablespace_name tablespace,
B. file_name physical file name,
B. Total Bytes bytes,
(B. bytes-sum (nvl (A. bytes, 0) already in use,
Sum (nvl (A. bytes, 0) remaining,
Sum (nvl (A. bytes, 0)/(B. bytes) * 100 percentage remaining
From dba_free_space A, dba_data_files B
Where a. file_id = B. file_id
Group by B. tablespace_name, B. file_name, B. file_id, B. bytes
Order by B. tablespace_name
/
Dba_free_space -- the remaining space in the tablespace
Dba_data_files -- data file space usage

4. view existing rollback segments and their statuses

SQL> Col segment format A30
SQL> select segment_name, owner, tablespace_name, segment_id, file_id, status from dba_rollback_segs;

5. view the data file placement path

SQL> Col file_name format A50
SQL> select tablespace_name, file_id, Bytes/1024/1024, file_name from dba_data_files order by file_id;

6. display the current connected user

SQL> show user

7. Use SQL * Plus as a calculator

SQL> select 100*20 from dual;

8. connection string

SQL> Select column 1 | Column 2 from table 1;
SQL> select Concat (column 1, column 2) from table 1;

9. query the current date

SQL> select to_char (sysdate, 'yyyy-mm-dd, hh24: MI: ss') from dual;

10. Data replication between users

SQL> copy from user1 to user2 create Table2 using select * From Table1;

11. Order by cannot be used in views, but it can be replaced by group by for sorting purposes.

SQL> Create View A as select B1, B2 from B group by B1, B2;

12. Create a user through authorization

SQL> grant connect, resource to test identified by test;

SQL> conn test/test