ORACLE常见语法(https://blog.csdn.net/qq_33547950/article/details/79257882)

https://blog.csdn.net/qq_33547950/article/details/79257882

1、查询所有表空间名

select tablespace_name from sys.dba_tablespaces;

3、查询所有的schema及所属的所有表

select s.username "schema",t.table_name "table_name" from sys.dba_users s left join sys.dba_tables t on s.username=t.owner order by s.username asc

1、通过指定表空间查询所属的表名

Select Table_Name, Tablespace_Name
From Dba_Tables
Where Tablespace_Name = 'USERS';

1、查询所有schema

//一个用户（user）对应一个方案（schema）
select username from sys.dba_users

2、查询schema下的所有表

select table_name from sys.dba_tables where owner='schema名';

3、查询数据库的实例名

SELECT INSTANCE_NAME FROM V$INSTANCE;

 

---------------------------建表、用户、表空间、授权----------------------------

CREATE TABLESPACE zjb_tablespace
DATAFILE '/u01/app/oracle/oradata/XE/zjb.dbf'
SIZE 100M
AUTOEXTEND ON
NEXT 10M
MAXSIZE UNLIMITED;

CREATE TABLE example_table (
id NUMBER(10) PRIMARY KEY,
name VARCHAR2(50),
age NUMBER(3),
email VARCHAR2(100)
) tablespace ZJB_TABLESPACE;

INSERT INTO example_table (id, name, age, email)
VALUES (1, 'John Smith', 30, 'john.smith@example.com');

INSERT INTO example_table (id, name, age, email)
VALUES (2, 'Jane Doe', 25, 'jane.doe@example.com');

INSERT INTO example_table (id, name, age, email)
VALUES (3, 'Bob Johnson', 40, 'bob.johnson@example.com');

select * from example_table

建用户&授权：

create user roma_logminer identified by Admin123;

grant create session to admin;

grant create view to admin;

grant select any dictionary to admin;

grant unlimited tablespace to admin;

grant create synonym to admin;

grant select any teble to roma_logminer;

GRANT CREATE TABLESPACE TO roma_logminer;

GRANT CREATE ANY TABLE TO roma_logminer;

GRANT ALTER ANY TABLE TO roma_logminer;

GRANT DROP ANY TABLE TO roma_logminer;