--
export ORACLE_SID=XYYZ
sqlplus / as sysdba
connect / as sysddba

-- Sessions
select SID, SERIAL#, STATUS,PROGRAM, USERNAME, LOGON_TIME, to_char(LOGON_TIME,'yyyy-mm-dd hh:mi:ss') 
from v$session  
order by LOGON_TIME desc ;

-- expired passwd
alter user myuser1 password expire;
select username, account_status, EXPIRY_DATE from dba_users;
ALTER USER scott IDENTIFIED BY password;

-- run file
SQLPLUS HR/your_password @SALES

-- SHOW TABLE SPACES
select tablespace_name from dba_data_files 
sqlplus UUUUSERRRR/password@DB_ID 

select column_name from all_tab_cols where table_name = 'xxxx';

### DATABASE INFO DATABASE INFO
— GET THE DB BLOCK SIZE AND CHARACTER SET
SELECT VALUE FROM V$PARAMETER WHERE NAME = ‘DB_BLOCK_SIZE’;
SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER=’NLS_NCHAR_CHARACTERSET’
SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER=’NLS_CHARACTERSET’

— GET THE DB BLOCK SIZE AND CHARACTER SET
SELECT VALUE FROM V$PARAMETER WHERE NAME = ‘DB_BLOCK_SIZE’;
SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER=’NLS_NCHAR_CHARACTERSET’
SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER=’NLS_CHARACTERSET’

### TABLES NAMES #########
— TO HAVE A LIST OF TABLES YOU OWN
SELECT TABLE_NAME FROM USER_TABLES;

— TO HAVE A LIST OF TABLES YOU CAN SELECT (YOURS + YOU ARE GRANTED TO SELECT)
SELECT TABLE_NAME FROM ALL_TABLES;

— TO HAVE A LIST OF ALL TABLES IN THE DB (IF YOU’RE GRANTED TO SEE THEM)
SELECT TABLE_NAME FROM DBA_TABLES;

### OTHERS #########
— GET THE CREATE SCRIPT
SELECT DBMS_METADATA.GET_DDL (‘TABLE’,'<TABLE_NAME>’)||’;’ FROM DUAL;

–SELECT CONSTRAINT
SELECT CONSTRAINT_NAME, TABLE_NAME FROM USER_CONSTRAINTS WHERE CONSTRAINT_NAME='<CONST NAME>’;

–SELECT VIEW
SELECT * FROM SYS.DBA_VIEWS WHERE VIEW_NAME LIKE ‘<VIEW_NAME>’

— create index
CREATE INDEX NATIONAL_SEARCH_HITS_IDX ON NATIONAL_SEARCH_HITS (VCMID);
select index_name from dba_indexes where table_name=’taBLE’;

Select COLUMN_NAME from user_tab_columns where table_name=’NEO_TC_APPLICATION’;

### COPY AND BACKUPS #########
–COPY/BACKUP TABLE
CREATE TABLE <TABLE_BAKCKUP> AS SELECT * FROM <TABLE>;

— CREATE A VIEW
CREATE OR REPLACE VIEW V_IINSIGHT_LASTUPDATED AS
SELECT …

### CREATE A USER (DBA) #########
DROP USER <USERNAME>;

CREATE USER RTB IDENTIFIED BY <USERNAME>
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP

SELECT TABLESPACE_NAME,
INITIAL_EXTENT,
NEXT_EXTENT,
MIN_EXTENTS,
MAX_EXTENTS,
PCT_INCREASE,
STATUS,
CONTENTS
FROM DBA_TABLESPACES
ORDER BY TABLESPACE_NAME

GRANT DBA TO <USERNAME>;

### CREATE AUTO-NUMBER FOR A TABLE #########
-- ORACLE + AUTO NUMBER

CREATE TABLE <TABLE>
(ID NUMBER PRIMARY KEY,
NAME VARCHAR2(30));

SELECT * FROM <TABLE>;

CREATE SEQUENCE <TABLE_SEQUENCE>
START WITH 1
INCREMENT BY 1;

CREATE OR REPLACE TRIGGER <TRIGGER>
BEFORE INSERT
ON <TABLE>
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT <TABLE_SEQUENCE>.NEXTVAL INTO :NEW.ID FROM DUAL;
END;

INSERT INTO <TABLE> (NAME) VALUES (‘TEST’);

## DATES #########
to_date(LASTUPDATED,’YYYYMMDDHH24MISS’)
TO_CHAR( MODIFIEDON,’YYYYMMDDHH24MISS’)

### Admin #########
-- List the log file size
select trunc(completion_time), sum(BLOCKS*BLOCK_SIZE/1024/<wbr/>1024) from  v$archived_log group by  trunc(completion_time) order by 1;

-- List events
select event,count(*) from gv$session where wait_class <> 'Idle' group by event

-- List locks
select * from DBA_2PC_PENDING;

Exports

https://stackoverflow.com/questions/26785645/how-to-create-copy-of-full-schema-on-same-database-in-oracle

You don't need a fancy network link or anything just the expdp/impdp commands. First export the schema that you want:

expdp fromUser/fromPword schemas=sourceSchema directory=DUMPDIR dumpfile=dump.dmp logfile=explog.txt

Tip: make sure that that user has the ability to write to DUMPDIR or whatever it is you call it

Then reimport the schema using the remap_schema parameter:

impdp toUser/toPword directory=DUMPDIR dumpfile=dump.dmp logfile=implog.txt remap_schema=fromUser:toUser

If you really want to use the network_link parameter (so you don't create a dumpfile for some reason) then I recommend reading this or this.

Tablespaces

How can I tell what tablespace a particular Oracle table or index resides in?

Resolution:

If the table or index is in the library's $data_root/file_list, that should show you the tablespace. If it is not, you should be able to see it in the util a/17/10/2 Merged file list (which gets its values from the file_list.xxx template in the $aleph_root/tab/ directory -- in addition to the $data_root/file_list).

Also, you can do the following SQL. (Note: you can be connected to any library in doing this.)

To get the tablespace for a particular Oracle table:

SQL> select tablespace_name from all_tables where owner = 'USR00' and table_name = 'Z303';