Common oracle Summary 2

-- Create tablespace test_tablespace logging datafile 'e: \ ORACLETABLESPACE \ test_tablespace.dbf' SIZE 32 m autoextend on next 32 m maxsize 2048 m extent management local;

-- Create temporary tablespace test_temptablespace tempfile e: \ ORACLETABLESPACE \ TESTTEMP_TABLESPACE.DBF 'size 32 m autoextend on next 32 m maxsize 2048 m extent management local;

-- Create user viva1 identified by viva1 default tablespace TEST_TABLESPACE temporary tablespace TEST_TEMPTABLESPACE profile DEFAULT; -- authorize the user to grant create session, create any table, create any view, create any index, create any procedure, alter any table, alter any procedure, drop any table, drop any view, drop any index, drop any procedure, select any table, insert any table, update any table, delete any table to viva1;

-- View the user SELECT * FROM DBA_USERS; SELECT * FROM ALL_USERS; SELECT * FROM USER_USERS;

-- View User Permissions SELECT * FROM DBA_SYS_PRIVS; SELECT * FROM USER_SYS_PRIVS;

-- View the user's permission SELECT * FROM DBA_TAB_PRIVS; SELECT * FROM ALL_TAB_PRIVS; SELECT * FROM USER_TAB_PRIVS;

-- View all roles SELECT * FROM DBA_ROLES;

-- View the SELECT * FROM DBA_ROLE_PRIVS; SELECT * FROM USER_ROLE_PRIVS;

-- No privileges on tablespace 'tablespace' alter user userquota 10 M [unlimited] on tablespace;

-- View the usage of all tablespaces select B. file_id File ID, B. tablespace_name tablespace name, B. bytes/1024/1024 | 'M', (B. bytes-sum (nvl (. bytes, 0)/1024/1024 | 'M' is used, sum (nvl (. bytes, 0)/1024/1024 | 'M' remaining space, 100-sum (nvl (. bytes, 0)/(B. bytes) * 100 percentage occupied from dba_free_space a, dba_data_files B where. file_id = B. file_id group by B. tablespace_name, B. file_id, B. bytes order by B. file_id;

-- View the user's default tablespace select username, default_tablespace from dba_users;

-- View the data file path and name used by the tablespace to be extended select * from dba_data_files where tablespace_name like 'users % ';

-- ADD the TABLESPACE data file alter tablespace test_tablespace add datafile 'e: \ ORACLETABLESPACE \ test_tablespace2.dbf' SIZE 500 m autoextend on next 32 m maxsize 2048 M;

-- Increase the size of the tablespace data file alter database datafile 'e: \ ORACLETABLESPACE \ test_tablespace.dbf' RESIZE 256 M;

-- Or use file_id alter database datafile 10 RESIZE 50000 M;

-- Alter database datafile 'e: \ ORACLETABLESPACE \ test_tablespace.dbf' autoextend on maxsize 4096 M;

-- Find the deadlock select * from dba_jobs_running -- first find the session_id: select xidusn, object_id, session_id, locked_mode from v $ locked_object; -- 131 -- then locate the serial # select username, sid, serial # from v $ session where sid = 131; -- 372 -- finally kill the process: alter system kill session '123 ';

-- Expand sub-partitions

ALTER TABLE  vav_stb_sec ADD      PARTITION "P_DEFAULT" VALUES LESS THAN (MAXVALUE) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOLOGGING STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "MDMS_TS_ZJSM_SEC" NOCOMPRESSALTER TABLE  vav_stb_sec ADD      PARTITION "MDMS_ZJSM_SEC_P_20121102" VALUES LESS THAN (TO_DATE(' 2012-11-03 02:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOLOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "MDMS_TS_ZJSM_SEC" NOCOMPRESS ALTER TABLE  vav_stb_sec ADD      PARTITION "MDMS_ZJSM_SEC_P_20121103" VALUES LESS THAN (TO_DATE(' 2012-11-04 02:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOLOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "MDMS_TS_ZJSM_SEC" NOCOMPRESS ALTER TABLE  vav_stb_sec ADD      PARTITION "MDMS_ZJSM_SEC_P_20121104" VALUES LESS THAN (TO_DATE(' 2012-11-05 02:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOLOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "MDMS_TS_ZJSM_SEC" NOCOMPRESS 

Drop tablespace "MDMS_TS_HNHL_SOURCE_201212" INCLUDING CONTENTS AND DATAFILES

 

MERGE INTO test1 p1using (SELECT ROWNUM + (select to_number(nvl(max(cid), 4700)) from test1) AS CAREAID,              CREGION_NAME         FROM (SELECT CREGION_NAME                 FROM VAV_VIEW_STB_FULL_INFO_R                GROUP BY CREGION_NAME)) p2on (p1.cname = p2.cregion_name)when not matched then  insert values (p2.careaid, p2.cregion_name)