Oracle Common Commands

--Login:
Sys/[email protected] as Sysdba

1, establish table space, grant permissions

/* divided into four steps */
/* Step 1th: Create a temporary tablespace */
Create temporary tablespace user_temp
Tempfile ' D:\oracle\oradata\Oracle9i\user_temp.dbf '
Size 50m
Autoextend on
Next 50m maxsize 20480m
Extent management Local;

/* Step 2nd: Create a data table space */
Create Tablespace User_data
Logging
DataFile ' D:\oracle\oradata\Oracle9i\user_data.dbf '
Size 50m
Autoextend on
Next 50m maxsize 20480m
Extent management Local;

/* 3rd step: Create user and specify Tablespace */
Create user username identified by password
Default Tablespace user_data
Temporary tablespace user_temp;

/* Step 4th: Grant permissions to users */
Grant CONNECT,RESOURCE,DBA to username

-------------------------------------------------------------------------------------------------------------

2. Tree Association

Start with
Connect by Prior
Description
A, prior: is a single operator, placed in front of the column name, the equal sign can be, put in the parent ID is to look for the ancestor node, put in its own ID is to find child nodes;
B, Connect_by_root: is a single operator, returns the topmost node of the current layer;
C, Connect_by_isleaf: Is pseudo-column, to determine whether the current layer is a leaf node, 1 is the representative, 0 means no;
D, Level: is a pseudo-column, showing the current node layer in the number of layers;
E, Sys_connect_by_path: is a function that displays the detailed path of the current layer.

-------------------------------------------------------------------------------------------------------------

3. query table name and table comment
SELECT tb.table_name,cl.comments
From User_tab_comments cl,user_tables TB
WHERE 1=1
and tb.table_name like ' f_sta_prjcost_% '
and cl.table_name = Tb.table_name

-------------------------------------------------------------------------------------------------------------

4. Tables owned by the current user

Select table_name from User_tables; Tables owned by the current user
Select table_name from All_tables; All users ' tables
Select table_name from Dba_tables; including system tables
Select table_name from dba_tables where owner= ' user name

-------------------------------------------------------------------------------------------------------------

5. Fields, notes
SELECT t.table_name,c.column_name,cc.comments
From User_tables t,user_tab_columns c,user_col_comments cc
WHERE t.table_name like ' f_prjcost_% '
and t.table_name = C.table_name
and c.column_name = Cc.column_name
and cc.comments like ' Construction unit% '
;

-------------------------------------------------------------------------------------------------------------

6, modify column name
SELECT
t.table_name,c.comments,
' ALTER TABLE ' | | t.table_name | | ' RENAME COLUMN projpart_id to project_level_id; '
from User_tables t,user_tab_comments C
WHERE t.table_name like ' f_prjcost_% '
and t.table_name = c.table_name< br>;
ALTER TABLE table_name rename column col_old to col_new
Add columns name
ALTER TABLE table_name
ADD column_name Datat YPE
Delete column name
ALTER TABLE TABLE_NAME
DROP column column_name;
Modify column length:
ALTER TABLE "Bidwstaging_usr". F_sta_prjcost_summary "MODIFY (" project_name "varchar2);

SELECT Cl.comments,tb.table_name,
REPLACE (tb.table_name, ' f_prjcost ', ' f_prjct '),
' CREATE TABLE ' | | REPLACE (tb.table_name, ' f_prjcost ', ' f_prjct ') | | ' As SELECT * from ' | | tb.table_name| | '; ',--build table
' DROP TABLE ' | | REPLACE (tb.table_name, ' f_prjcost ', ' f_prjct ') | | '; ',--Delete table
' COMMENT on COLUMN ' | | tb.table_name| | '. PROJECT_LEVEL_ID is ' project Structure ID '; ' --Modify Comments
From User_tab_comments cl,user_tables TB
WHERE 1=1
and tb.table_name like ' f_prjct_% '
and cl.table_name = Tb.table_name;
Add a table comment, column comment:
COMMENT on table D_org_n is ' organization dimension table ';
COMMENT on COLUMN f_prjcost_adj_price_settle. PROJECT_LEVEL_ID is ' project Structure ID ';

-------------------------------------------------------------------------------------------------------------

7. Authorization
SELECT Tb.table_name,cl.comments,
' GRANT SELECT, insert,delete,update,alter on ' | | Tb.table_name | | ' to Bidwstaging_usr; '
From User_tab_comments cl,user_tables TB
WHERE 1=1
and tb.table_name like ' f_prjcost_% '
and cl.table_name = Tb.table_name
;

-------------------------------------------------------------------------------------------------------------
8. Synonyms
SELECT Tb.table_name,cl.comments,
' CREATE OR REPLACE synonym ' | | Tb.table_name | | ' For bidw_usr. ' | | Tb.table_name | | '; '
From User_tab_comments cl,user_tables TB
WHERE 1=1
and tb.table_name like ' f_prjcost_% '
and cl.table_name = Tb.table_name

-------------------------------------------------------------------------------------------------------------

9, the method of locking the User:
sql> ALTER user test account lock;
--oracle How to unlock the user:
sql> alter user test account unlock;
-------------------------------------------------------------------------------------------------------------
10. Truncate permissions for different users
Grant drop any table to bidwstaging_usr;
Grant Select any dictionary to bidwstaging_usr;

-------------------------------------------------------------------------------------------------------------

11. Create a new partition

CREATE TABLE CUSTOMER
(
customer_id number not NULL PRIMARY KEY,
First_Name VARCHAR2 (+) not NULL,
Last_Name VARCHAR2 (+) not NULL,
PHONE VARCHAR2 () not NULL,
EMAIL VARCHAR2 (80),
STATUS CHAR (1)
)
PARTITION by RANGE (customer_id)
(
PARTITION cus_part1 VALUES less THAN (100000) tablespace cus_ts01,
PARTITION cus_part2 VALUES less THAN (200000) tablespace CUS_TS02
)

Oracle Common Commands